Forum

How do I know if th...
 
Notifications
Clear all

How do I know if there was a problem with the refresh of data?

6 Posts
3 Users
0 Reactions
537 Views
(@yossip999)
Posts: 3
Active Member
Topic starter
 

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

 
Posted : 11/03/2021 10:38 am
(@mynda)
Posts: 4761
Member Admin
 

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

 
Posted : 11/03/2021 6:52 pm
(@yossip999)
Posts: 3
Active Member
Topic starter
 

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!

 
Posted : 12/03/2021 3:39 am
(@catalinb)
Posts: 1937
Member Admin
 

Hi,

You're using wb.RefreshAll or you refresh each query one by one?

Can you post the code you're using to refresh?

 
Posted : 12/03/2021 4:21 am
(@yossip999)
Posts: 3
Active Member
Topic starter
 

I am using this code to refresh the workbook and save and close:

 

ActiveWorkbook.RefreshAll:  ActiveWindow.Close SaveChanges:=True

 
Posted : 12/03/2021 5:01 am
(@catalinb)
Posts: 1937
Member Admin
 

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:

For Each Connection In ActiveWorkbook.Connections
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

 
Posted : 12/03/2021 8:10 am
Share: