Forum Discussion
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 a vba code from access to open and Update the query.
If I refresh the query manually it works fine.
Do anyone have experience with this?
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)
3 Replies
- Your Welcome
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)
Hi Nikolino
Tks. for your help.
I have already a VBA update data source, and after that I have a refresh.all.
I have made it work with help of a delay.
I added a delay on 10 sec. between the update and the refresh, and this is working, Maybe not
the best solution, but it is working.
Thank you for your time
