0

I'm having a frustrating issue in my attempt to automate the manual update of 8 different excel dashboards. These dashboards are built with Power Query (16 different queries) and Power Pivot and populated with a ton of CubeValue formulas like so:

=IFERROR(CUBEVALUE("ThisWorkbookDataModel","[Measures].["&$B$1&"]","[Data].[Geography].&["&$B9&"]","[Data].[Period].&["&$B$3&"]","[Data].[Product].&["&C$3&"]"),"-")

Normally the process is to manually open an old file, click 'RefreshAll' and then SaveAs with a new name.

The macro runs seemingly fine, but when you open a macro refreshed file, all the cells are zeroed out (or rather contains the "-" from the error statement).

If I comment out the close line in the code below, the Workbook will refresh properly, so it seems the macro is closing before it can fully refresh.

I've tried the answer from the question below but to no avail.

Wait until ActiveWorkbook.RefreshAll finishes - VBA

Set thiswb = Workbooks("AMPS_Update_Template.xlsm")

sourcepath = "..."
workpath = "..."
savepath = "..."

'*** Define File Name Strings ***
mon = thiswb.Worksheets("Sheet1").Range("D8").Value
we = thiswb.Worksheets("Sheet1").Range("D6").Value

endstring = "_" & mon & "." & we
amps = "AMPS_"
ext = ".xlsb"


'*** BU Array ***
bus = Array("CRMRS", "WHTNRS", "RTD", "PBB", "PD", "SS", "FRZ", "YOG")

'*** Check and Refresh Loop ***
For i = 0 To 7

    If thiswb.Worksheets("Sheet1").CheckBoxes(bus(i)).Value <> 1 Then

        GoTo Line1

    ElseIf thiswb.Worksheets("Sheet1").CheckBoxes(bus(i)).Value = 1 Then

        Application.DisplayAlerts = False

        Workbooks.Open Filename:=savepath & amps & bus(i) & endstring & ext

        DoEvents

        Set wb = Workbooks(amps & bus(i) & endstring & ext)

        wb.RefreshAll

        DoEvents

        wb.SaveAs Filename:=savepath & amps & bus(i) & endstring & ext

        wb.Save

        wb.Close

        Application.DisplayAlerts = True


    End If

Line1:
    Next i

End Sub
DanCo89
  • 11
  • 1
  • 4
  • Add `Application.CalculateUntilAsyncQueriesDone` after RefreshAll (in a new line) – Ricardo Diaz Jan 30 '20 at 20:35
  • That seems to have made excel nonresponsive and it just crashed when I tried to close out the refreshing worksheet. – DanCo89 Jan 30 '20 at 21:04
  • Which answer did you try from the link? – Ricardo Diaz Jan 30 '20 at 21:14
  • I tried pretty much everything that pertains to an ODBC connection. – DanCo89 Jan 30 '20 at 21:44
  • Try these two: 1) Instead of RefreshAll try to refresh only the Model with `ThisWorkbook.Model.Refresh` or 2) try to loop through the tables with `ThisWorkbook.Model.ModelTables(TableName).Refresh` – Ricardo Diaz Jan 30 '20 at 21:46
  • wb.Model.Refresh returns the same result as my wb.RefreshAll code. Looping through the tables with `For Each mtable In wb.Model.ModelTables _ mtable.refresh _ next mtable` does the same. – DanCo89 Jan 30 '20 at 22:41
  • In my setup I use `Application.CalculateUntilAsyncQueries` with the model refresh and it works. Sorry it didn't for you. – Ricardo Diaz Jan 30 '20 at 23:07

0 Answers0