ODBC connection through Excel XP

The following connection works fine in Excel: Sub RefreshCOM() UserID = InputBox(“Voer uw Navision Financials UserID in.”) 'Password = InputBox(“Voor uw Navision Financials wachtwoord in.”) 'ToDate = InputBox(“Voer einddatum in”) 'DateYYYY = ToDate MsgBox (ToDate) Worksheets(“COM”).Range(“A1:C1000”).ClearContents Worksheets(“COM”).Activate Range(“A1”).Select With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ “ODBC;DSN=NAV_COM;CSF=Yes;SName=valkieser;NType=tcp;PPath=C:\Program Files\Navision Financials;OPT=Text;IT=All Except DOT;” _ ), Array( _ “QTYesNo=Yes;RO=No;CC=Yes;BE=Yes;UID=user;PWD=password;CN=100. Valkieser Communications;SERVER=N;” _ )), Destination:=Sheets(“COM”).Range(“A1”)) .CommandText = Array( _ “SELECT Grootboekrekening.Nr_, Grootboekrekening.Naam, Grootboekrekening.”“Saldo t/m datum”"" & Chr(13) & “” & Chr(10) & “FROM Grootboekrekening Grootboekrekening” & Chr(13) & “” & Chr(10) & “WHERE (Grootboekrekening.Datumfilter” _ , “<=‘31-12-2002’)” & Chr(13) & “” & Chr(10) & “ORDER BY Grootboekrekening.Nr_”) .Name = “Query from NAV_COM_1” .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub However I would like to use the ‘UserID’ and ‘Password’ in the connection definition. I tried everything, but I can’t figure out how to parse the value of the variable into the connection. Second question is how to parse the value of the ‘ToDate’ variable into the Select-definition. I would like to use it to set a Datefilter. Any help appreciated. Thanks in advance. Best regards, Gerard

Hi, I found the solution. Below you can find the syntax of the statement. Might be helpfull to someone. Sub RefreshCOM() UserID = InputBox(“Voer uw Navision Financials UserID in.”) Password = InputBox(“Voor uw Navision Financials wachtwoord in.”) ToDate = InputBox(“Voer einddatum in.”) Worksheets(“COM”).Range(“A1:C1000”).ClearContents Worksheets(“COM”).Activate Range(“A1”).Select With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ “ODBC;DSN=NAV_COM;CSF=Yes;SName=ubf;NType=tcp;PPath=C:\Program Files\Navision Financials;UID=” & UserID & “;PWD=” & Password & “;CN=100. Valkieser Co” _ ), Array(“mmunications;SERVER=N;”)), Destination:=Range(“A1”)) .CommandText = Array( _ "SELECT Nr_, Naam, ““Saldo t/m datum”” FROM Grootboekrekening " & _ “WHERE (Grootboekrekening.Datumfilter”, “<=‘31-12-2002’)” & “ORDER BY Nr_”) .Name = “Query from NAV_COM_1” .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub