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

"Tushar Mehta" wrote in message
news:MPG.1a7c288da59dea7b98968e@news-server...
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

In article , fourstar77
@hotmail.com says...
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