Accessing data in a Querytable

[shaunk]

There is a lot of information around on how to add a querytable to an excel worksheet using VBA. I have achieved this with no prblems.
However, I now wish to access the data in the querytable and there is no informtion I can find anywhere on how to do this.
I assume the .name property of the add method can be used to identify the querytable.

The sql for the querytable itself returns rows of KPI data relating to a particular set of "reporting units", one row for each month across a series of years.
I need to access the set of rows relating to each reprting unit and copy the data (with transpose) to another sheet. Each set of rows will be seperated by filler rows into their respecive reporting units.
I have done this arkwardly but with no reference to the querytable as a named object.
Thanks

 

[PHV]

The data are in the QueryTable.Destination range.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shaunk]

Thanks. Questions as follows.
1) Help contents on .destination is as follows:
"Returns the cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed"

Help on .Resultrange is as follows:
"Returns a Range object that represents the area of the worksheet occupied by the specified query table"

The two statements below do the same thing. One using .Resultrange, the other .Destination.

Set QT_Loc = ActiveSheet.QueryTables(3).Destination
Cells(QT_Loc.Row, QT_Loc.Column).Select
Set QT_Loc = ActiveSheet.QueryTables(3).ResultRange
Cells(QT_Loc.Row, QT_Loc.Column).Select

So I am struggling to sse the difference. I don't think that Resultrange returns a rnage of cells at all, just the top left corner.

2) I have added a Querytable with the .name property set to "Raw_Data"

On executing the code
QT_Name = ActiveSheet.QueryTables(1).Name
MsgBox QT_Name

The name returned is 'External_Data 14".

I thought I could reference the Querytable by name

 

[PHV]

So I am struggling to sse the difference
Try this:
ActiveSheet.QueryTables(3).ResultRange.Select

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

how about setting up a dynamic range who's name never changes so you can reference that instead ??

faq68-1331

 

[PHV]

xlbo, isn't .ResultRange such dynamic range ?

 

[xlbo]

PHV - yes but I am never 100% certain that XL isn't going to just rename the Querytable for me. I therefore prefer to take no chances and set up my own range to reference.

 

[shaunk]

OK. Thanks for the clarifiction between .destination and .resultrange.

So what is the point of .name, if it can't be used to reference the QT?

It seems you have to know the index into the Querytables collection of the QT you want.

 

[SkipVought]

Hi,

Reference the SHEET, Range Name and QueryTable

    With Sheets("Orders")
        With .Range("qryOrders").QueryTable
            .Connection = sConn
            .CommandText = sSQL
            .Refresh BackgroundQuery:=False
        End With

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[xlbo]

you can reference the name - it's just I am never 100% certain that it won't be changed. Names of querytables are changed under certain criteria. Because I am not sure what those criteria are, I prefer to use a named range and reference that instead. I also make it a point to only have 1 querytable per sheet so I can always reference it with:

sheets("sheet name").querytables(1)