What's the name of the Range where a WebQuery puts data?

[LRL]

When I perform a query to an external website via a Web Query, the data is
inserted into the spreadsheet at the range I specify. When I click on the
names in the formula bar, there is a name, ExternalData_xxx where xxx is a
number. This number is incremented every time I perform the query. The range
that it selects is exactly the amount of data that is returned. This would be
very useful if I could figure it out. Does anybody know how I can find this
name? Or specify it?

Thanks,

LRL

 

[Jim Gordon MVP]

Hi LRL,

When you perform a Web Query the External Data Toolbar should appear. If
it doesn, then use VIEW > TOOLBARS > EXTERNAL DATA to turn it on.

Click the Properties button on the toolbar. Change the NAME field to
something more useful to you.

-Jim

 

[LRL]

Thanks Jim. The information is very useful.

I am doing the query via VBA, something similar to this:

Set apQT = mergeCompWS.QueryTables.Add(Connection:= _
"URL;https://www.mywebsite.com/", Destination:=destinationRng)
With apQT

.BackgroundQuery = False
.PostText = someData
.SaveData = True
.TablesOnlyFromHTML = False

.Refresh BackgroundQuery:=False

End With

The destination range is named but is only one cell. The amount of data
returned varies in amount and size. I'd like to be able to programmically
specify it before the query. Or to find and use the name of the range that
contains the returned data. Then the macro can use that name itself.


Thanks.

 

[LRL]

Ah, I'm an idiot. The QueryTable has a property called, oddly enough, Name.
All I have to do is either access itor change the name myself with a simple
assignment.

 

[Jim Gordon MVP]

Very good. The size of the table adjusts automatically when the query
is refreshed. I don't think you can programatically control the size.

-Jim