Move on to next step in VBA code if power query does not refresh or gets hung up

[MGTexas123]

If I run a VBA Macro in Excel that instructs a number of power queries in the workbook to refresh, how do I get the VBA code to move to the next step if a query refresh gets stuck (e.g. does not load)? Here is an example of the VBA code:

Sub New_Requests()


ActiveWorkbook.Connections("Query - New Requests").Refresh
ActiveWorkbook.Connections("Query - New Requests Export File to ACT").Refresh


End Sub




Thanks in advance for any help on this!

Matt

 

[JB2020]

If I run a VBA Macro in Excel that instructs a number of power queries in the workbook to refresh, how do I get the VBA code to move to the next step if a query refresh gets stuck (e.g. does not load)? Here is an example of the VBA code:

Sub New_Requests()


ActiveWorkbook.Connections("Query - New Requests").Refresh
ActiveWorkbook.Connections("Query - New Requests Export File to ACT").Refresh


End Sub




Thanks in advance for any help on this!

Matt

Hi Matt
It depends on how you determine whether your queries are getting stuck. You could enable background refreshing on your queries and then wait for some period of time, but I don't think there is really a good way of cancelling the refresh in VBA. You might be better trying to identify what's causing the problem with the queries themselves in my opinion. Though someone else might have a better suggestion than me of course.

 

[MGTexas123]

Hi Matt
It depends on how you determine whether your queries are getting stuck. You could enable background refreshing on your queries and then wait for some period of time, but I don't think there is really a good way of cancelling the refresh in VBA. You might be better trying to identify what's causing the problem with the queries themselves in my opinion. Though someone else might have a better suggestion than me of course.

Thanks for your help with this and the suggestion. I've tried this in the past, but no luck. The main issue is that when Power Query pulls data from an external source such as Sharepoint folder or Google sheets, about 1 out of 5 times the query simply will not refresh and get stuck. What I can't figure out is what is causing the query to hang up some of the time, but not all of the time. The only workaround I can think of is to add to the VBA code (when we run the queries in a Macro) the ability to skip queries that are getting hung up in the macro. Any additional suggestions on how to either solve the core problem, or what VBA code to include in the macro would be greatly appreciated.

 

[JB2020]

Thanks for your help with this and the suggestion. I've tried this in the past, but no luck. The main issue is that when Power Query pulls data from an external source such as Sharepoint folder or Google sheets, about 1 out of 5 times the query simply will not refresh and get stuck. What I can't figure out is what is causing the query to hang up some of the time, but not all of the time. The only workaround I can think of is to add to the VBA code (when we run the queries in a Macro) the ability to skip queries that are getting hung up in the macro. Any additional suggestions on how to either solve the core problem, or what VBA code to include in the macro would be greatly appreciated.

I would put the power query problem up as a separate post first, as that should speed up any solutions for that.
Have you tried just leaving the query running for as long as possible when it's getting stuck?

 

[MGTexas123]

JB2020,

Thanks for your question. I have let the query run for several minutes and Excel eventually throws a VBA error some of the time. I've tried to break the query down into smaller steps, but no luck. What makes this challenging, is the problem only occurs about 1 out of 5 times when I run the macro.

Matt

 

[JB2020]

JB2020,

Thanks for your question. I have let the query run for several minutes and Excel eventually throws a VBA error some of the time. I've tried to break the query down into smaller steps, but no luck. What makes this challenging, is the problem only occurs about 1 out of 5 times when I run the macro.

Matt

Hi Matt

Have you had any more luck with this? I would suggest trying to run the query outside of VBA and leaving it running when it gets stuck, at least then if it throws out an error you will be able to see what's causing it, or whether it is just taking ages to refresh sometimes for whatever reason.