Power Query NOT Refreshing on Refresh All VBA

[Anonymous]

I have built a large report using purely Excel with Power Query. A lot of steps are involved in PQ and later loaded in Excel sheet the final report. I tried to automate it but Refreshing is not working and it's a real headache!

The Excel file is a .xlsm (macro enabled).

-I have disabled "Background refresh" and tried using the following code. I used the following code and did not work:

Public Sub Workbook_Open()

   Application.ScreenUpdating = False

   ActiveWorkbook.RefreshAll

   ThisWorkbook.Save

   Application.Quit

End Sub

Still didn't work, it starts refreshing but in reality it doesn't refresh or crashes. When I do manually Refresh All then it works or by pressing refresh icon next to the PQ loaded table.

Later I added one line of code being modified to the following:

Public Sub Workbook_Open()

   Application.ScreenUpdating = False

   ActiveWorkbook.RefreshAll

    ActiveWorkbook.Connections("Query - Final result").Refresh  { Added this line}

   ThisWorkbook.Save

   Application.Quit

End Sub

It still doesn't work. I don't know what I have to do to solve this! I have requested PowerBI Pro version but I am not sure if there is automatic schedueling to solve this.

Any help is appreciated.

[v-jingzhang]

Hi @Anonymous 

Did you find a solution finally? Could you share it here if you have got one?

Best Regards,
Community Support Team _ Jing

[Anonymous]

//You've got to give the workbook time to refresh. I always add an

Application.OnTime(NOW + "00:05:00", ThisWorkBook.NameOfCloseModule)

//and then make a separate module (NameOfCloseModule) that you can //put your ThisWorkbookSave and ActiveWorkbook.Close procedures.

Now it won't try to save then close for 5 minutes, or however long you want.

--Nate

[CNENFRNL]

Sounds wierd. It functions well on my end.

[Anonymous]

@CNENFRNL I will have a check with your code. Just a quick question, I saw your VBA code, could you clafify what I wrote in purple below?

Public Sub Workbook_Open()
Application.ScreenUpdating = False
' ActiveWorkbook.RefreshAll

With ActiveWorkbook.Connections("Query - Query1")
.Refresh
.OLEDBConnection.BackgroundQuery = bBackground //bBackground or Background only?
End With

ThisWorkbook.Save

' Application.Quit
End Sub

[wdx223_Daniel]

you need save file after the refresh is completed. unfortunately, I have not heard of the method to get the query refreshing status with VBA.