web query in excel
Thanks for the feedback.
Regarding the case sensitivity, you could probably just use a variation of
this:
If Ucase(Left(rngRef.Name, 16)) = Ucase("Sheet1!MyQuery_") Then
(You probably alread did that, though.)
***********
Regards,
Ron
"JimEl" wrote:
Thanks Ron
It worked fine. The only problem was that the if(left...) statement was
case sensitive.
"Ron Coderre" wrote:
I'm not sure if this is the most correct way to go on this, but this is what
I typically put in my query procedures after the query has been run:
Assume query table is in Sheet1 and query table name is MyQuery:
'***************
'Beginning of Code
'***************
On Error Resume Next
For Each rngRef In Sheet1.Names
If Left(rngRef.Name, 16) = "Sheet1!MyQuery_" Then
rngRef.Delete
End If
Next rngRef
On Error GoTo 0
On Error Resume Next
For Each tblRef In Sheet1.QueryTables
If Left(tblRef.Name, 8) = "MyQuery_" Then
tblRef.Delete
End If
Next tblRef
On Error GoTo 0
'***************
'End of Code
'***************
Note: I edited the code a bit to make it more generic, so you may have to
tweak it.
Does that help?
***********
Regards,
Ron
"JimEl" wrote:
I have some macros that pulls in data from a web site. the macros updates
the url, there is one for everyday with price information. However, every
time i run the macro it creates a range "ExternalData_#" with the number
representing the number of time the website has been queried. Some of the
workbook have over 500 ExternalData_# ranges this slows the whole thing down.
Does anyone know how to stop creation of a new range each time?
|