VBA Macro for Upload in EPM addin

Question

I am working on automating upload of data through EPM- demand planning add-in.

I have 33 sheets; each sheet has a set of data to be uploaded and amounts to more than 1000 data points. Currently I have to individually go to each sheet and click on upload button, which is highly manual and time consuming.

I have written a macro to upload the whole workbook but, in-between the upload, Excel is crashing. So I'm looking for a way to upload each sheet one by one.

Macro code:-

Sub Upload()
Dim ws As Worksheet, flg As Boolean
For Each ws In Sheets
ws.Select
EPMexample.SaveAndRefreshWorksheetData
Next
End Sub

But the challenge is I have to click yes for every sheet in the workbook once upload starts.

Is there a way to automatically click ok every time or to upload the entire workbook at once.

Answer 1

Try this:

Sub Upload()
    EPMexample.SaveWorkbookData
    EPMexample.RefreshActiveWorkBook
End Sub

All the input forms in your workbook will be submitted, with just one confirmation message.