Select Table and Field of Query by Cell value

[CLR]

Hi All........

I have a macro that runs a Query and works fine for what it is. The problem
is that the Table and Fields to be included in the query are hard coded into
the macro and changing them means changing the macro. I don't want to do
that.....I want to just change a list in Excel.....say in column X. I've
tried to replace WOE.WONUM (WOE is the table name and WONUM is the fieldname)
with Range ("X2").value where WOE.WONUM is entered in X2, but it does not
work.

Here's the complete macro.....any help would be much appreciated.

Sub GetNewData()
Dim DT1 As String, DT2 As String
DT1 = Format(Range("e4").Value, _
"YYYY-MM-DD 00:00:00")
DT2 = Format(Range("e5").Value, _
"YYYY-MM-DD 00:00:00")
'Call the prep macros
Call ClearField 'Runs the macro to clear the old data field
Call DeleteCFcolumnJ 'Runs the macro to delete the old Conditional
Formatting from col J

'Run the Query
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MP2PROD;Description=DSN for MP2 MSSQL
6.0;UID=mp2reader;PWD=reader1;APP=Microsoft Office
XP;WSID=CROBER-XP;DATABASE=MP2PROD" _
), Array(";Network=DBMSSOCN")), Destination:=Range("A8"))
.Sql = Array( _
"SELECT WOE.WONUM, WO.WOTYPE, WOE.WODATE, WOE.CRAFT,
WOTYPE.DESCRIPTION, WO.ASSIGNEDTO, WOCRAFT.ESTLABORHOURS, WOE.REGHRS,
WOE.OTHRS" & Chr(13) & "" & Chr(10) & "FROM MP2PROD.dbo.WO WO,
MP2PROD.dbo.WOCRAFT WOCRAFT, MP2PROD.dbo.WOE WOE, MP2P" _
, _
"ROD.dbo.WOTYPE WOTYPE" & Chr(13) & "" & Chr(10) & "WHERE
WO.CLOSEDATE = WOCRAFT.CLOSEDATE AND WO.CLOSEDATE = WOE.CLOSEDATE AND
WO.SITEID = WOCRAFT.SITEID AND WO.SITEID = WOE.SITEID AND WO.WONUM =
WOE.WONUM AND WO.WONUM = WOCRAFT" _
, _
".WONUM AND WO.WOTYPE = WOTYPE.WOTYPE AND WOCRAFT.CLOSEDATE =
WOE.CLOSEDATE AND WOCRAFT.CRAFT = WOE.CRAFT AND WOCRAFT.SITEID = WOE.SITEID
AND WOCRAFT.WONUM = WOE.WONUM AND ((WOE.WODATE>={ts '" & DT1 & "" _
, _
"'} And WOE.WODATE<={ts '" & DT2 & "'}))" & Chr(13) & "" & Chr(10) &
"ORDER BY WOE.WODATE, WOE.WONUM" _
)

.FieldNames = False 'True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With

'sort the new data field
Range("a8").Select
Selection.Sort Key1:=Range("C8"), Order1:=xlAscending,
Key2:=Range("A8") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

Range("A3").Select

'Call the completion macros
Call PrepOThrs 'Runs the macro to "prepare" the OTHRS field by replacing
blanks with zeros
Call AddFormulas 'Runs the macro to restore the formulas to columns I and J
Call AddCFcolumnJ 'Runs the macro to restore Conditional Formatting to
column J
Call SetColumnWidths 'Runs the macro to establish proper column widths
Range("A8").Select
End Sub