QueryTable Problem

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I have a worksheet that contains two ranges that I want to use as
Query Tables. I have set up the first query and it works fine, since
then I have copied and pasted the code into a new sub and modified it
to point to the second range but I get an "Application-defined or
object-defined error" at the line:

With QTable.QueryTable

Could anyone suggest what is going wrong or put me on the right track
again?

Code is shown below ....

Sub GetFCVvalue(ByVal FCVTag As String)

Dim QTable As Range

With ThisWorkbook.Worksheets("System")
Set QTable = .Range("Flow")
End With

' Run the query
With QTable.QueryTable ' Sub fails here ...

' Query definition here

End With

End Sub

Regards,

Alan
 
QTable is a Range, not a Query table.

Try this

Set MyTable = Sheets("Sheet1").QueryTables(1)
With MyTable

End With
 
QTable is a Range, not a Query table.

Try this

Set MyTable = Sheets("Sheet1").QueryTables(1)
With MyTable

End With















- Show quoted text -
Click to expand...

Whilst I understand your comment I do not understand why the code
works the first time around with the range and not the second. Any
further comments would be useful.

Alan
 
I'm not sure why it works the 1st time. I don't like the fact you were
usingg a range object to refer to a QueryTable. It don't know the rest of
your code and not sure the way FLOW is defined. If you are deleting and
adding the query table in your code the name of the table will change. Your
way specifying FLOW as the name specifically reffers to only the table with
that name. My way it will always find the 1st query table.
 
I have a worksheet that contains two ranges that I want to use as
Query Tables. I have set up the first query and it works fine, since
then I have copied and pasted the code into a new sub and modified it
to point to the second range but I get an "Application-defined or
object-defined error" at the line:

With QTable.QueryTable

Could anyone suggest what is going wrong or put me on the right track
again?

Code is shown below ....

Sub GetFCVvalue(ByVal FCVTag As String)

Dim QTable As Range

With ThisWorkbook.Worksheets("System")
Set QTable = .Range("Flow")
End With

' Run the query
With QTable.QueryTable ' Sub fails here ...

' Query definition here

End With

End Sub
Click to expand...

Alan: You get that error because there is no query table whose ResultRange
property intersects with the range FLOW.

When you get a QueryTable object from a Range object, the Range only needs
to be one cell within the QueryTable.

So it sounds like you manually created a query table for the first range and
you are changing the properties of the QueryTable.

To do that for another range, you need to manually create a query table for
that range. Then you'll be able to mess with its properties too.
 
Back
Top