Variable Cell Reference In Macro To Get External Data: Send variables to MSQuery via entry in Excel cell range? Excel help response (04 Feb 2009)

VBA Code: (FIXED CRITERIA WORKS FINE.

Sub B08DATAGL_Query()
' MACRO TO RUN WITH FIXED CRITERIA WORKS JUST FINE
' B08DATAGL_Query Macro
' Rebuilds MSQuery for DATAGL Tab.
'
'
Columns("A:D").Select
    Selection.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABODBC;PWD=MABODBC", Destination _
        :=Range("A3"))
        .CommandText = Array( _
        "SELECT GLACC_PER.COM_CODE, GLACC_PER.PERIOD_NO, GLACC_PER.CLOSE_BAL, GLACC_PER.ACCOUNT" & Chr(13) & "" & Chr(10) & "FROM SYSADM.GLACC_PER GLACC_PER" & Chr(13) & "" & Chr(10) & "WHERE (GLACC_PER.COM_CODE= 'B08') AND (GLACC_PER.PERIOD_NO<=200812) AND (GLACC_P" _
        , "ER.CLOSE_BAL<>0)" & Chr(13) & "" & Chr(10) & "ORDER BY GLACC_PER.PERIOD_NO DESC")
        .Name = "DATAGLQUERY1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

VBA Code: (vARIABLE CRITERIA GIVES COMPILE ERROR: Expected: list seperator or )

Sub B08DATAGL_Query_VARIABLE()
' MACRO TO RUN WITH VARIABLE CRITERIA DOES NOT WORK
' B08DATAGL_Query Macro
' Rebuilds MSQuery for DATAGL Tab.
'
Dim ComCode As Variant
Dim Transdate As Date
ComCode = Range("E2").Text
Transdate = Range("F2").Value

Columns("A:D").Select
    Selection.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABODBC;PWD=MABODBC", Destination _
        :=Range("A3"))
        .CommandText = Array( _
        "SELECT GLACC_PER.COM_CODE, GLACC_PER.PERIOD_NO, GLACC_PER.CLOSE_BAL, GLACC_PER.ACCOUNT" & Chr(13) & "" & Chr(10) & "FROM SYSADM.GLACC_PER GLACC_PER" & Chr(13) & "" & Chr(10) & "WHERE (GLACC_PER.COM_CODE= "&ComCode&" ) AND (GLACC_PER.PERIOD_NO<="&Transdate&") AND (GLACC_P" _
        , "ER.CLOSE_BAL<>0)" & Chr(13) & "" & Chr(10) & "ORDER BY GLACC_PER.PERIOD_NO DESC")
        .Name = "DATAGLQUERY1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Hi All,
Using the code posted in this thread a few years ago I made some changes to my VBA code.
My purpose was to change the fixed criteria Macro to a variable criteria that is a Cell reference in excel sheet. However when I do that i get Compiling error: Expected: list seperator or )
I get the error where I have typed the variable criteria formula { &" ) AND (GLACC_PER.PERIOD_NO<=" }
I am not good with VBA if you can help it would be appreciated.

Thanks heaps- [email protected]

VARIABLE CELL REFERENCE IN MACRO TO GET EXTERNAL DATA

Excel Help for Variable Cell Reference In Macro To Get External Data in Automating Data Analysis Tasks

Variable Cell Reference In Macro To Get External Data