Forum Discussion
Power Query NOT Refreshing on Refresh All VBA
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.
5 Replies
- wdx223_Daniel
Community Champion
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.
CNENFRNLSounds wierd. It functions well on my end.
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.RefreshAllWith ActiveWorkbook.Connections("Query - Query1")
.Refresh
.OLEDBConnection.BackgroundQuery = bBackground //bBackground or Background only?
End With
ThisWorkbook.Save' Application.Quit
End Sub
//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
v-jingzhangCommunity Support
Hi Anonymous
Did you find a solution finally? Could you share it here if you have got one?
Best Regards,
Community Support Team _ Jing
