VBA With Selection.ListObject.QueryTable as string?

[mrbillk]

I built an Excel workbook that uses Microsoft Query via VBA to pull data in from another Excel workbook. I pass different parameters to the VBA to perform a search for purchase order information. My problem is that this must run on various version of Excel (from 2000 to 2010) therefore I find the Excel version and set up separate WITH QueryTable statements to include or exclude the .ListObject.
One uses With Selection.ListObject.Query to include the .ListObject. (table in 2007 and later) but another one uses just Selection.Query. I can’t find a way to set the WITH statement as a String to either include the .ListObject. or exclude it based on the Excel version. When I tried setting a string variable to include or exclude it the query fails with “Object Required”. This should be simple, but I can’t seem to find a solution. What I tried is shown below. Appreciate any suggestions.

Dim LObject as String
LObject = ".ListObject."
With Selection & LOject & QueryTable ' need a way to build this statement

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Sorry, but VBA expressions like that can't be built using strings

Try a conditional approach like this instead...

Dim QT As QueryTable


If [I] [your test for version is True][/I] Then
    Set QT = Selection.ListObject.QueryTable
Else
    Set QT = Selection.QueryTable
End If


With QT
    '....your code continues...

 

[mrbillk]

Thanks for the quick response,
That worked very well, and like I thought was very easy. Never thought about using Dim QT as QueryTable. With that I can pull a lot of code out of this routine.
Thanks again.