What is wrong with the external data query code?

[whr4th]

I am trying to cycle through many worksheets and update the query on each one. I cannot use RefreshAll as I think there must be some sort of time constraint on how long each one takes to update. Some of these queries take 5+ minutes.

Here's my code:
Sub Refresh_All()

Application.ScreenUpdating = False

'Cycle through worksheets
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate

ActiveWorksheets.QueryTable.Refresh (False)

Next ws

Worksheets("Main").Activate

Application.ScreenUpdating = True

End Sub

I keep getting a run-time error 424. I have tried several different methods from this board but keep striking out.

I'm sure it's simple and right in front of my face. Please help!

Thanks in advance.

 

[Oaktree]

Just a thought: can you turn calculation to manual, then refresh all?

 

[whr4th]

Nope, tried that and I get a Run Time Error 1004: General ODBC Error.

I think that from reading prior posts, there is a certain length of time between refreshes and if your is still trying to update, it times out.

Other thoughts? I think I just have the verbiage incorrect and I can't figure it out. I even tried the macro recording and tried using:

Range("D9").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

But that didn't work either as I got a Run-time error '1004': Application-defined or object-defined error.

 

[whr4th]

And just to clarify - the error I get is: Run-time error '424': Object required.

I have tried all of the following:
ActiveWorksheets.QueryTables(1).Refresh BackgroundQuery:=False
ActiveWorksheets.QueryTables.Refresh BackgroundQuery:=False
ActiveWorksheets.QueryTables.Refresh
ActiveWorksheets.QueryTable.Refresh ()
ActiveWorksheets.QueryTable.Refresh (False)

And I am sure several others that I can't recall. As I said, I am sure it is something simple but I just can't see it.

 

[whr4th]

Another follow up.

I found this code doing some board searching:

ActiveSheet.QueryTables(1).Refresh False

I tried it and now get a Run-time error '9': Subscript out of range.

Still searching.....

 

[james_a_boyle]

Updating data from Access during an Excel macro

Did you find any solution? I think I am experiencing exactly the same problem

 

[whr4th]

Yes, I figured out how to make mine work. In short, I had tabs without queries and the Macro would bug out if it tried to update a query that didn't exist. If you use the OnError Resume Next statement, it worked fine.

Here's a link to the solution (and discussion): http://www.mrexcel.com/board2/viewtopic.php?t=129098

 

[james_a_boyle]

Thanks, worked a treat!!