AS-IS Scenario

Mr. Mike Cole (MIS Executive) has to extract the Sales data every day from MS Access Database. He has to extract the sales data every day and prepare Sales report. He has to send the sales report to his Sales Manager at the end of the day.

TO-BE Scenario

Normally he would have to open MS Access and write query to extract the data and then he will have to copy the sales data from MS Access to excel. It is little bit time consuming and hence he takes the help of Macro to extract the data and paste in excel sheet. Within a matter of seconds the task gets completed.
Let’s take a look at the procedure to extract data from MS Access to Excel.
Before writing code to extract data follow the below steps.
Steps to Create employee database, creating table and inserting records into the table.

Step 1. Open MS Access
Step 2. Create Sales_Data Table
Step 3. Import data into Sales_Data Table.
Step 4. Save the Sales_Data.accdb file in C:Sales_Data_Ms_AccessSales_Data.accdb.
I have provided the Sales_Data_Raw_Data where Sales Data is present and also Sales_Data.accdb so that you can save in the mentioned path (C:Sales_Data_Ms_AccessSales_Data.accdb). Macro will open the Sales_Data.accdb and extract the data from Sales_Data.accdb

Step 5. Open Sales_Data.xlsm excel file.
Step 6. Insert new sheet
Step 7. Rename the Sheet to Sales_Data (Sales Data will be imported in this sheet.)
Step 8. Press Alt+F11
Step 9. Go to Insert Menu
Step 10. Select Module
Step 11. Module will be inserted
Step 12. Select Module 1
Step 13. Go to Properties Window
Step 14. Change the name to AccessToExel in (name)

Important Note – We have to activate the Microsoft ActiveX Data Objects 6.1 Library. I am using MS Office 2013 so it will differ according to the MS Office Version.
Step 15. Go to Visual Basic (Alt+F11) Tools References Select Microsoft ActiveX Data Objects 6.1 Library Click on OK

Step 16. Type the below code in the Code Window.
Option Explicit
Sub AccessToExcel()
Dim ConObj As ADODB.Connection
Dim RecSet As ADODB.Recordset
Dim ConCmd As ADODB.Command
Dim ColNames As ADODB.Fields
Dim DSource As String
Dim iloop As Integer
DSource = “C:Sales_Data_Ms_AccessSales_Data.accdb”
Set ConObj = New ADODB.Connection
Set ConCmd = New ADODB.Command
With ConObj
.Provider = “Microsoft.ACE.OLEDB.12.0”
.ConnectionString = DSource
.Open
End With
ConCmd.ActiveConnection = ConObj
ConCmd.CommandText = “SELECT * FROM Sales_Data”
ConCmd.CommandType = adCmdText
Set RecSet = ConCmd.Execute
Set ColNames = RecSet.Fields
For iloop = 0 To ColNames.Count – 1
Cells(1, iloop + 1).Value = ColNames.Item(iloop).Name
Next
Sheets(“Sales_Data”).Range(“A2”).CopyFromRecordset Data:=RecSet
Columns(“A:P”).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Columns.AutoFit
Range(“A1”).Select
ConObj.Close
End Sub
Step 17. Go to Macro sheet in Sales_Data.xlsm file
Step 18. Go to Developer Tab
Step 19. Go to Insert in Controls Section
Step 20. Select Button (Form Control)

Step 21. Place the Button inside the excel sheet.
Step 22. Right click on the Button and select Edit Text
Step 23. Change the Text to Extract data from MS Access
Step 24. Right Click on the Button
Step 25. Select Assign Macro and Select AccessToExcel
Step 26. Click on AccessToExcel Button to run the Macro.