Log In
Register
Home
Active Member
March 11, 2021
Offline
Hi,
I refresh workbooks that contain queris in power query.
Sometims there is a problem with the refresh and the data are not updated but I don't know it untill I enter the quries themself and check the queris with the yellow triangle ( with the question mark inside)
How can I know that there was a problem without entering the queries?
Thanks
July 16, 2010
Offline
Hi and welcome to our forum!
You can also see if there are errors in the Queries and Connections pane. Go to the Data tab on the Excel ribbon > Queries & Connections. This will open a pane on the right-hand side that shows the queries and any with errors will have a caution sign.
Mynda
Active Member
March 11, 2021
Offline
Thank you Treacy!
I am refreshing a lot of workbooks using a Macro that opens the file then refresh the queris in it and then save and close the file and going on to the next file.
I need an indication in an excel cell or in any another way that I can access this indication that File A was refreshed correctly and file B did not refresh the quries.
this way I will be able to know in which files the queries didn't refresh.
Thanks you!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi,
You're using wb.RefreshAll or you refresh each query one by one?
Can you post the code you're using to refresh?
Active Member
March 11, 2021
Offline
I am using this code to refresh the workbook and save and close:
ActiveWorkbook.RefreshAll: ActiveWindow.Close SaveChanges:=True
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
RefreshAll works different in VBA than from user interface, in VBA will not raise error message when a query fails.
Loop through connections and refresh them one by one:
If Connection.Type = xlConnectionTypeOLEDB Then
Connection.OLEDBConnection.BackgroundQuery = False
On Error GoTo 1
Connection.Refresh
End If
2:
Next Connection
Exit Sub
1:
If Err.Number <> 0 Then
'write to a cell: ActiveWorkbook.Name & " failed to refresh!"
End If
Resume 2
1 Guest(s)
