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

--
Don Guillett
SalesAid Software
[email protected]
"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> 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

[K Dales]

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


"ExcelMonkey" wrote:

> 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
>

[Jake Marx]

Hi ExcelMonkey,

ExcelMonkey wrote:
> 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]

Jake Marx wrote:
> 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]