Building automated reports in Excel by connecting directly to an ERP database is a good shortcut to have fresh and accurate information to base your decisions on. This method facilitates data processing and manipulation in VBA before results and conclusions are displayed and a further availability for organizing, calculations and sorting after Excel tables are populated.

Connecting to SQL Server

To connect to a database server you must switch to code writing and in Project Explorer (left side or visit View menu if not visible) select ThisWorkbook and add the following code in here:

Public Con As New ADODB.Connection

Private Sub Workbook_Open()

Dim r As New ADODB.Recordset
Con.ConnectionString = “driver={SQL Server};Server=SQLServerName;uid=UserName;pwd=Password”
Con.Open
Con.CommandTimeout = 20
Con.Execute “table”

End Sub

You also have to go to Tools / Preferences… to have the Microsoft ActiveX Data Objects 2.5 Library checked in order for this type of connection object to be available.

The connection works only after you save and reopen the Excel file as the connection is made only when the file is opening.

Using the connection to the database

The “r” variable as ADODB.Recordset can be used after this in any Procedure or Function to query the tables of the database and process the outputs. Don’t forget to define the variable as ADODB.Recordset in every macro.

If the connection breaks for any reason, the Excel file should be closed and reopened in order to reset it. Excel files which contain VBA macros must be saved with .xlsm or .xls extensions as .xlsx doesn’t save the VBA code behind the Excel spreadsheets.