Runtime Error 7 Inconsistent
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
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.
Re: Runtime Error 7 Inconsistent
Welcome to VBForums :wave:
Thread moved from the 'Application Deployment' forum (which is for questions about installing/distributing your software) to the 'Office Development/VBA' forum
Re: Runtime Error 7 Inconsistent
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.
Re: Runtime Error 7 Inconsistent
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.
Re: Runtime Error 7 Inconsistent
you can do something like
vb Code:
Dim myConn As Object
For Each myConn In ActiveWorkbook.Connections
myConn.Refresh
Next myConn
Re: Runtime Error 7 Inconsistent
I still get the same error when I use that, although fortunately, it seems to be working now. I went basic and wrote
Code:
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.
Re: Runtime Error 7 Inconsistent
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.