Runtime Error 7 Inconsistent

[bshafter]

I'm a new to VB Forums and new to VB code as well so excuse me if there is something obvious I am missing. I am running code that produces a Runtime Error 7 message on the following code

Sheets("DIYGO").Select

Range("D2:D45").Name = "RefreshRange"

Range("RefreshRange").QueryTable.Refresh

And errors out on the refresh. It works on other sheets just not this one and sometimes this one works and others do not. The code works fine by itself but when run with others, it gives me the error. The debugger claims it is the "Range("RefreshRange").QueryTable.Refresh" That has the problem. Once again, sometimes it works sometimes it doesn't. The range refreshed is web data. Thanks for your help.

[si_the_geek]

Welcome to VBForums

Thread moved from the 'Application Deployment' forum (which is for questions about installing/distributing your software) to the 'Office Development/VBA' forum

[kaliman79912]

If you want to refresh the query do not use the range and the table, refresh the connection itself. Something likeL

ActiveWorkbook.Connections("MyQuery").Refresh

Where MyQuery is the name of the query, do not mistake it for the name of the range.

[bshafter]

Thank you for your quick response. I have about 200 separate web queries, would each one need to be refreshed individually as they are all from separate locations on the web or can I refresh an area or range of them? They are separated onto multiple sheets.

[kaliman79912]

you can do something like

Dim myConn As Object
 
For Each myConn In ActiveWorkbook.Connections
   myConn.Refresh
Next myConn

[bshafter]

I still get the same error when I use that, although fortunately, it seems to be working now. I went basic and wrote

Dim x As Integer
x = 4
Dim y As Integer
y = 3

Sheets("sheet1").Select
Cells(y, x).Select

Range(ActiveCell, ActiveCell.Offset(43, 0)).Select
Range("D3:D46").QueryTable.Refresh

And for some reason, that is producing no new errors. It seems inconsistent and much more code then necessary but it works. Thanks for your help and if you have any idea why I might be getting these inconsistent errors, please let me know.

[kaliman79912]

I have no idea. But even in your code. All your lines do not affect the outcome exept for the last one. You can leave that alone and it will refresh the querytable in that particular range.