Export Database data to your Microsoft Excel
In this article I will show how to Database Server and Microsoft Excel can talk each other. There may be a scenario where you need to take a report out of bulk data from database to your excel sheet. How do you do that? , Will you manually copy and paste the content to your sheet?.
You can create your own custom macro code to pull database report to your excel instead manually copying the data from database to excel.The below sample shows you how to automate database data to your Excel, the macro code does the stuff
The step by step way:
1. Goto Start -> Open Microsoft Office Excel 2007
2. Goto Main menu and Click 'Excel Option' button , under popular enable 'Show developer tab in the Ribbon'
3. Goto Developer tab and Click 'Visual Basic' button 
4. Open ThisWorkBook as show below 
5. Add the below code in This Workbook
Private Sub Workbook_Open()
Sheet1.Cells.Clear
End Sub
6.Right Click and create a new class, name it as 'DBtoExcel'
7.Add the below code to the DBtoExcel class
Public Function GetDataFromSQL()
'Declare variables
Set objSQLConnection = New ADODB.Connection
Set objRecordSet = New ADODB.Recordset
Dim strSQLQuery As String
'Open Connection
objSQLConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=SQL_Learning;Trusted_Connection=yes;"
objSQLConnection.Open
'Set and Excecute SQL Command
strSQLQuery = "select * from Employees"
'Open Recordset
Set objRecordSet.ActiveConnection = objSQLConnection
objRecordSet.Open strSQLQuery
'Copy Data to Excel
ActiveSheet.Range("A4").CopyFromRecordset (objRecordSet)
End Function
8.Add new ActiveX Button control to the sheet.
9.Double click on the button object and write the below code
Private Sub CommandButton1_Click()
Dim obj As DBtoExcel
Set obj = New DBtoExcel
obj.GetDataFromSQL
End Sub
10. Save your code ,close the excel and reopen again.
11. Click on 'Fetch Db Data ' and get data from your database 
12. Have a sample database table as shown below before creating the macro code
13. You got your table's content in your sheet