creating ODBC query object in EXCEL

[teletubby5e]

I have a spreadsheet that I am working on that has customer numbers, and addresses. I am wanting to add on their telephone numbers. I have an ODBC linked table that has a table that has these customer numbers (AND MORE) as well as their associated telephone numbers. How do I make a query that goes something like this.

run query on odbc table source "CUST.dsn" with value in Excel spreadsheet "cust.xls,cell A1" and output value from column L of whatever row it finds a match on to cell F1

would i be better off exporting the cust table to a second spreadsheet, and then creating an excel spreadsheet to spreadsheet query?

thanks, for any help on this, or pointing me in the right direction.

 

[SkipVought]

Hi,

You will loop thru each row in the sheet and use the data in that row as the criteria for your SQL statements

Sub Macro1()
   Dim sConn, sSQL, sPath, sDB, lRow
   
   sDB = "Inventory"          'database name
   
   sPath = "C:\My Documents"  'path to daatabase

'your connect string here???
   sConn = "ODBC;DSN=Excel Files;"
   sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
   sConn = sConn & "DefaultDir=" & sPath & ";"
   sConn = sConn & "DriverId=790;"
   sConn = sConn & "MaxBufferSize=2048;"
   sConn = sConn & "PageTimeout=5;"

'this defines the range of values in column A
   With Sheets("TargetSheet")
      Set rng = Range(.[A1], .[A1].End(xlDown))
   End With
   
'the loop
   lRow = 1
   For Each r In rng
   'put the value in the sql criteria
      sSQL = "SELECT I.PartID, I.Quantity "
      sSQL = sSQL & "FROM `" & sPath & "\" & sDB & " I "
      sSQL = sSQL & "WHERE (I.PartID='" & r.Value & "')"
           
       With Sheets("QuerySheet").QueryTables(1)
         .Connection = sConn
         .CommandText = sSQL
           .Refresh BackgroundQuery:=False
       End With
   'write to the target sheet
       With Sheets("TargetSheet")
         .Cells(lRow, 5).Value = Sheets("QuerySheet").[A2].Value
       End With
       
       lRow = lRow + 1
   Next
   
End Sub

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[SkipVought]

Hi,

You will loop thru each row in the sheet and use the data in that row as the criteria for your SQL statements

Sub Macro1()
   Dim sConn, sSQL, sPath, sDB, rng, r
   
   sDB = "Inventory"          'database name
   
   sPath = "C:\My Documents"  'path to daatabase

'your connect string here???
   sConn = "ODBC;DSN=Excel Files;"
   sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
   sConn = sConn & "DefaultDir=" & sPath & ";"
   sConn = sConn & "DriverId=790;"
   sConn = sConn & "MaxBufferSize=2048;"
   sConn = sConn & "PageTimeout=5;"

'this defines the range of values in column A
   With Sheets("TargetSheet")
      Set rng = Range(.[A1], .[A1].End(xlDown))
   End With
   
'the loop
   For Each r In rng
   'put the value in the sql criteria
      sSQL = "SELECT I.PartID, I.Quantity "
      sSQL = sSQL & "FROM `" & sPath & "\" & sDB & " I "
      sSQL = sSQL & "WHERE (I.PartID='" & r.Value & "')"
           
       With Sheets("QuerySheet").QueryTables(1)
         .Connection = sConn
         .CommandText = sSQL
           .Refresh BackgroundQuery:=False
       End With
   'write to the target sheet
       With Sheets("TargetSheet")
         r.Offset(0, 5).Value = Sheets("QuerySheet").[A2].Value
       End With
       
   Next
   
End Sub

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[teletubby5e]

Man, That is great! Thanks. I will check it out at work tomorrow. Thanks again!

Jeff