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()
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;"

'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
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



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: