Send Variables To Msquery Via Entry In Excel Cell Range?: Send variables to MSQuery via entry in Excel cell range? Excel help response (11 Nov 2006)

Actually, I'm using my own code, very similar though. I've posted it below. If I run it as it's own module, it runs fine and creates the rows I want. If I move it to a sub-procedure and use a true data value, it also works fine, if I use what is below where it is in a sub-procedure and uses a variable, it fails with that odbc error. The value is getting populated, I can tell from debugging, but it fails with the same error you were getting. Any ideas??

VBA Code:

Sub cboProjTypNm_Change()
'
' SearchProjNm Macro
' Macro recorded 11/10/2006 by LPasseretti
'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=D:\Work\2-Luxottica\ResourceMgmt\Testing\ResourcePlanning\IT 2006 Resource Planning.xls;DefaultDir=D:\Work\2-Luxottica\Reso" _
        ), Array( _
        "urceMgmt\Testing\ResourcePlanning;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScan" _
        ), Array( _
        "Rows=8;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
        )), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT ProjPlan.Category, ProjPlan.`Proj vs Act`, ProjPlan.`Proj Type/Name`, ProjPlan.Team, ProjPlan.`Team Resp`, ProjPlan.Rsce, ProjPlan.Role, ProjPlan.`Assoc Type`, ProjPlan.Month, ProjPlan.`Wk/Mo`," _
        , _
        " ProjPlan.Hours" & Chr(13) & "" & Chr(10) & "FROM ProjPlan ProjPlan" & Chr(13) & "" & Chr(10) & "WHERE (ProjPlan.`Proj Type/Name`=" & cboProjTypNm.Value & ")" _
        )
        .Name = "Query from RescPlan"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Send variables to MSQuery via entry in Excel cell range?

Excel Help for Send Variables To Msquery Via Entry In Excel Cell Range? in Automating Data Analysis Tasks

Send Variables To Msquery Via Entry In Excel Cell Range?