excel function that gets data from ODBC data source

Question

very open ended question. Need just a general answer ...

From Excel I want to run SQL against an ODBC data source.   " select distinct Season from StyleMst ".  Take the results and populate a column in an excel spreadsheet.  Or use the results as the contents of a dropdown list that appears when a cell in a specific column in the spreadsheet is clicked.

I need to know the basics of how to do that in excel.  2007 is the version that my code will run against.

thanks,

Answer 1

very open ended question. Need just a general answer ...

From Excel I want to run SQL against an ODBC data source.   " select distinct Season from StyleMst ".  Take the results and populate a column in an excel spreadsheet.  Or use the results as the contents of a dropdown list that appears when a cell in a specific column in the spreadsheet is clicked.

I need to know the basics of how to do that in excel.  2007 is the version that my code will run against.

thanks,

 

You can let Excel do the heavy lifting.  See

Cascading queries

http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/

Answer 2

Thanks for posting the resolution as well. It would indeed help other users who use the community frequently.

Answer 3

I kind of have my answer. Use the ADODB to connect to a database. Then use ADODB.Recordset to read rows from the table.

Sub Hello2()

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

With cn

    .Provider = "MSDASQL"

    .ConnectionString = "DSN=dsn_192_168_1_160;UID=xxxxxxxL;PWD=xxxxxxxL;"

    .Open

End With

strQuery = "select * from dunn.ids846p where styl < 7000"

Dim Rs1 As ADODB.Recordset

Set Rs1 = cn.Execute(strQuery)

strSeas = ""

Do While Not Rs1.EOF

  ActiveCell.Offset(1, 0).EntireRow.Insert

  ActiveCell.Offset(1, 0).Select

  ActiveCell.Offset(0, 0).Value = Rs1![seas]

  ActiveCell.Offset(0, 1).Value = Rs1![styl]

  ActiveCell.Offset(0, 2).Value = Rs1![clnm]

  ActiveCell.Offset(0, 3).Value = Rs1![Size]

  ActiveCell.Offset(0, 4).Value = Rs1![ShelfQty]

  Rs1.MoveNext

Loop

Rs1.Close

cn.Close

End Sub