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