Connecting to SQL in Excel Macro

To connect to SQL server in Microsoft Excel, you first need to make sure you have a reference to the libraries. When you open up Excel, hold down the alt key and press F11. That will open up the excel visual basic editor. Under tools in the visual basic editor you should select references. Check the Microsoft ActiveX Data Objects 2.0 Library and press okay. That will import the correct libraries you can use in your macro. In your VBAProject folder you should select your workbook. This code is Microsoft Visual Basic 6.5 and Microsoft Excel 2003 Professional Edition.

You can add this code to your workbook:

Private Sub Workbook_Open()

callReport

End Sub

This code will be called when the workbook is opened and will call the callReport function.

This function connects to your database and loops through the data but it doesn't display anything. I will discuss that in my next post.

Sub callReport()

Dim conn As New ADODB.Connection

Dim rec As New ADODB.Recordset

'This is your database connection string. Make sure you add the correct server name and database name, and the correct username and password

conn.Open "Driver={SQL Server};" & _

"Server=Computer;" & _

"Database=catalog;" & _

"Uid=username;" & _

"Pwd=password"

sql = "select * from users"

rec.Open sql, conn

While Not rec.EOF

rec.MoveNext

'Without this you will be in an infinite loop. This will move your record to the next record in the list.

Wend

rec.Close

End Sub