Microsoft Excel can connect to a database and retrieve data to be used in a report. First you have to insert all necessary References in the Tools Menu.
In the Project Explorer window select ThisWorkbook and insert the following lines in the code window:
Public con As New ADODB.Connection
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If con.State = adStateOpen Then con.Close
Set con = Nothing
End Sub
Private Sub Workbook_Open()
Dim r As New ADODB.Recordset
con.ConnectionString = "driver={SQL Server};Server=IP; uid=USERID;pwd=PASS"
con.CommandTimeout = 120 con.Open
con.Execute "USE DATABASE"
End Sub
You can now insert a new module in the project explorer window and start writing your code.Example:
Sub ReadTable()
Dim r As New ADODB.Recordset
i=2
r.Open "SELECT name,age FROM DATABASE WHERE age>18", ThisWorkbook.con
While NOT r.EOF
Sheets("Sheet 1").cells(i,1)=r("name")
Sheets("Sheet 1").cells(i,2)=r("age")
i=i+1
r.MoveNext
Wend
r.Close
Set r = Nothing
End Sub
Run the module and enjoy.
