Dynamic Web Queries

[Matt Day]

Hello,

I am attempting to use the below code to run some web queries via my macro.
I am often times getting an Application defined or Object defined error on
the .Refresh BackgroundQuery part. I have about 5 of these queries in my
macro and the error can happen in any of them and there does not appear to
be a pattern. I dont know if the program is having problems connecting to
the online information or what might be causing this, but I have had
multiple web queries run through a macro and have never received this error.
I tried tossing some Application.wait code in there but that did not seem to
help. Does anyone have any suggestions on this?

Also, as I run this macro over and over (I need to run it each day to gather
data) "ExternalData_x" items are being created with respect to these web
queries. To explain this further you can see this under the Insert - Name -
Define menu. I can delete these manually but would prefer that they arent
saved at all in the spreadsheet if this is at all detrimental. I have the
macro deleting the QueryTables with Selection.QueryTable.Delete, but these
ExternalData_x. Any suggestions on this?

Thanks

Matt

With Sheets("RawData").QueryTables.Add(Connection:="URL;" & url, _
Destination:=Sheets("RawData").Range("b2"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = False
End With

 

[Tushar Mehta]

If you analyze your code, you will realize that each time it is
executed you are adding a new query ...QueryTables.Add(...

Instead, turn on the macro recorder (Tools | Macro > Record new
macro...), refresh an existing query, and turn off the recorder. XL
will give you the necessary code to reuse an existing query. It should
be something like:

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

which can be modified to

Range("B2").QueryTable.Refresh BackgroundQuery:=False


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Matt Day]

Thank you for looking at my problem. I understand what you are saying, but I
require the ability to change the query through the code, therefore I often
times need to be able to add a new query. But when I do this, I have no need
for the old query although the old queries seem to remain in the
spreadsheet.

Now, I have modified my code so that it no longer requires 5 queries, rather
just 1. But, even with your suggestion below and simplifying the code, I am
often times getting an Application defined error when the code attempts to
refresh the query. It does not happen every time. I just dont understand why
the refresh is not working all the time as I have used the refresh command
in other spreadsheets. Could it have to do with multiple queries existing
for the same cell due to the QuerTables.Add function or something along
those lines? Or could the web query be timing out and not getting all of the
data?

Basically the code seems to be running great except for this Application
defined or Object defined error with respect to refreshing the query. Any
further assistance that anyone can provide would be wonderful.

Thanks again for all the help Tushar!

Matt Day

 

[Peter Benson]

I have EXACTLY the same probleml. I have been using VBA to get quotes from
Yahoo for over a year. Never a problem. Recently (last week or two) I have
started getting the Application defined or Object defined error on the
..refresh statement. It happens intermittently: five queries, each one or
two seconds apart, might work fine then suddenly (with the same stock codes)
I'll get this error. NO CODE HAS CHANGED for over a year. It's NOT that
the VBA code doesn't work -- it has been working fine for over a year. Now
(on all four of my PCs) I'm getting this problem. I cannot work around it as
it's completely random.

It's as if Yahoo is sending back some sort of invalid response -- I can't
think what else it could be.

It happens under 2000 and 2003. (I haven't tried other versions).

Something (not my code) has changed recently to cause this intermittent
problem.