web query in excel
 

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?

web query in excel
 

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?

web query in excel
 

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?

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?