How do I populate an EXCEL spreadsheet with data from a SQL database using VBA.

wademi
wademi used Ask the Experts™
on
Cane someone send me an example of how to retrieve data from a SQL database and populate a Sheet in Excel.

I need an example of the database connection string and the code to populate the data from for example 5 fields from the database. I need the data to be copies starting from cell A1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Most Valuable Expert 2011
Top Expert 2011
Commented:
For example:

Sub GetSQLData()
   ' Sample demonstrating how to return a recordset from a SQL Server db
   ' Requires reference to Microsoft ActiveX Data Objects library

   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, i As Long
   Set cn = New ADODB.Connection
   On Error Resume Next
   With cn
      .Provider = "sqloledb"
      .ConnectionString = "Data Source=VSQL;Initial Catalog=database_name;" 'User ID=blah;"
      .Open
   End With
   strQuery = "viewName"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, cn, adOpenStatic
   With rst
      'Populate field names
      For i = 1 To .Fields.Count
         ActiveSheet.Cells(1, i) = .Fields(i - 1).Name
      Next i
      ' Copy data
      ActiveSheet.Range("A2").CopyFromRecordset rst
      .Close
   End With
   Set rst = Nothing
   cn.Close
   
   Set cn = Nothing
End Sub

Open in new window

Author

Commented:
Hi Rorya
When I tried to compile your code I get an error at
 Dim cn As adodb.Connection

The error message says "User-defined tye not defined"

Most Valuable Expert 2011
Top Expert 2011

Commented:
You have to set a reference to the ActiveX Data objects library, as mentioned at the start of the code. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial