Web Query

[ExcelMonkey]

Is it possible in VBA to check a cell range and test to
see if it is the desigated cell for a web query. That is
I want to be able to identify which cell the web query
pastes to.

Thanks

 

[Don Guillett]

If you need for your refresh macro,why not just use

sheets("mysheet").querytables(1).refresh

 

[Guest]

If there is a web query, the results will be in a QueryTable on the sheet;
and the QueryTable.QueryType will be xlWebQuery - so, for example:

Dim QT as QueryTable
For Each QT in ActiveSheet.QueryTables
If Not(Intesect(RangeToCheck,QT.ResultRange) Is Nothing) And _
QT.QueryType = xlWebQuery Then MsgBox QT. Name & ": " & QT.Connection
Next QT

 

[Jake Marx]

Hi ExcelMonkey,

Is it possible in VBA to check a cell range and test to
see if it is the desigated cell for a web query. That is
I want to be able to identify which cell the web query
pastes to.

Each worksheet contains a QueryTables collection. Code like this will give
you the destination range address of a particular QueryTable:

Debug.Print Sheet1.QueryTables(1).Destination.Address

QueryTables can either be web or database driven. If it's a web query, the
following statement should evaluate to True:

Sheet1.QueryTables(1).Connection Like "URL;*"

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Jake Marx]

QueryTables can either be web or database driven. If it's a web
query, the following statement should evaluate to True:

Sheet1.QueryTables(1).Connection Like "URL;*"

Please look at K Dales' post for a better way to do this (QueryType
property).

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]