Forum Discussion
Solved
Update power query on startup
Hi I have 2 power query in an Excel workbook. Only one of the query refreshes on startup. I have set both query to Update on startup, by settings and with a vba code. I have also use ...
There are certainly others here who are much, much, much more familiar with PQ.
But while reading your request, I did a little internet research and there was something ...
attached is an additional VBA code, maybe it does what you imagined, should be a little food for thought. If not, please just ignore my post.VBA Code freshly fished off the internet, for you 🙂
Public Sub UpdatePowerQueries ()
'VBA to update data sources
Dim lngPowerQuery As Long, objDataSource As WorkbookConnection
Dim objWorksheet As Worksheet
On Error Resume Next
For Each objDataSource In ThisWorkbook.Connections
'Workbook Query = Power Query?
lngPowerQuery = InStr (1, objDataSource.OLEDBConnection.Connection, "Provider = Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
'Power Query? Update data source
'Option 1 - Update all data sources
If lngPowerQuery> 0 Then objDataSource.Refresh
'' Option 2 - selectively update data sources
Select Case objDataSource.Name
Case "Query - List_Functions"
objDataSource.Refresh
Case Else
End Select
'Output workbook query name in debugger
Debug.Print objDataSource.Name
Next objDataSource
End SubHope I was able to help you.
I wish you continued success with Excel (the coolest invention since chocola... uh ... Microsoft! :-)))
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
Your Welcome