Data Migration, Excel, how to, Technology

Export Data from MS Access to Excel Template with Formatting

“Summary: Are you confused about how to export data from MS Access to Excel? You will get all of your questions answered in this blog as we have provided several ways to help you.”

Access and Excel are the two most powerful tools developed by Microsoft with each one of them having their specialties. MS Access is great for data management, displaying data subsets & common queries, automation for common events, and other such information management activities.

MS Excel on the other hand cracks up when working with hundreds or thousands of spreadsheets. However, it is brilliant for data analysis, calculations, statistical comparison, chart management, etc. Users go for the application that suits their requirements the most.

“In our department, we use Access and Excel, both. I am a long-time user and prefer to store my data in Access databases but my immediate senior prefers to work with Excel. I am now supposed to export data tables/reports from MS Access to Excel but when I tried to do so, my data went corrupt. How do I recover the file and migrate it?”

If you have similar queries, then keep up with the upcoming sections to help you find a solution.

Reasons to Convert MS Access to Excel File Format

Below are the main reasons why you need to convert Microsoft Access database (MDB/ACCDB) to XLSX format: 

• Excel offers powerful analysis tools like pivot tables.
• Create customizable reports easily in Excel.
• Share data widely for collaboration using Excel.
• Excel’s manipulation features aid complex calculations.
• Visualize data effectively with Excel’s charts.
• Excel files ensure compatibility and easy sharing.
• Customize data presentation and formatting in Excel.
• Use Excel for quick and simple data backups.
• Integrate Access data with other applications using Excel.
• Ensure broad accessibility by sharing data in Excel format.

How to Export Data From MS Access to Excel? – Automated Tech

You can execute this task manually as well as with the help of software. But keeping in mind the limitations of a manual method, we highly recommend a trustworthy tool option i.e., Access MDB converter tool.

Download Now Purchase Now

Features of the Software:

• • Export data tables, queries, forms, relational modules, and reports from Access to Excel with a user-friendly interface.
  • Quickly scans and repairs corrupt and deleted Access database before converting.
  • Provides dual options to save .mdb file: Excel per table and Excel per Database.
  • No size limitations are imposed on the source file when converting to Excel more than 65000 rows.
  • Option to migrate only the selective tables from the Access table database (MDB/ACCDB) to Excel (.xlsx).
  • Offers two export options: schema only or schema with data.
  • Converts data from MS Access versions 2007, 2010, 2011, 2013, and the latest versions.
  • Compatible to run on Windows 11, 10, 8, 7, XP earlier releases.

Must Read:
Learn different ways to Recover Data From Corrupt Excel File

Steps to Export Data From MS Access to Excel

1. Download the software, install, and launch it.

 

2. Browse the Access file that you need to move to Excel. You can load a corrupted Access (MDB/ACCDB) file here as well.

3. Next, the software provides you with a preview of the entire data before converting it.

4. After that from the “Select Export Option” dropdown list, pick the Excel (.xls) aspect.

5. At last, provide a destination location, and click on the Export/Save button with the desired Export Options.

Done! It’s that easy to do it. On the plus side, you can transfer all of the data from MS Access to Excel, which is impossible with the manual method described below.

How to Extract Data From Microsoft Access to Excel Template with Formatting? – A Complete Guide 

Follow the methods given below to convert MS Access database to Excel template. However, the ideal solution is to use the above professional tool to perform the migration without any data loss. Still, for the share of knowledge and free ways, you can refer to the following manual methods. 

How to MS Access Export Report to Excel (.xlsx) Format using MS Access?

When you export data from MS Access to Excel, the former application creates a copy of your data & stores it in a file that Excel can use. You can export a query, form, report, or table from a .mdb or .accdb file into an Excel file. Perform the following given manual steps:

• Launch the MS Access application. Next, open the database you want to migrate.
• Now, select the data tables you desire to export.
• After that, navigate to the “External Data” → “Excel” icon → “Export” group.
• Henceforth, select the location where to save the Excel file.
• Pick the desired Excel version (e.g., Excel Workbook (*.xlsx)).
• Click “OK” to proceed.
• Then, briefly configure additional export options, such as formatting, data range, column headers, etc. 
• Lastly, make sure that the exported Excel file template has the correct information.

How to Migrate Data From Access to Excel Template Using MS Excel?

There is another way to manually import the data from Access (MDB/ACCDB) to XLSX format i.e. through the use of Microsoft Excel. The access database contains several tables, queries, forms, reports, and many more that can be easily converted to the required file format.

• Open the Microsoft Excel on your system.
• Go to the “Data” on the Excel ribbon.
• You can directly import an Access file (MDB/ACCDB) in Excel by clicking on the Data tab and selecting Get Data (from the Get & Transform Data section). 
• Select “From Database“, then click on “From Microsoft Access Database“, and select the required Access file. 
• Next, browse to the location of your Access database file (MDB or ACCDB) and choose it. 
• Select the specific table or query from the Access database to import. 
• Then, tap on the “Finish” button.
• Once you have done the process, review the data in your Excel worksheet.

How to Export Data from MS Access to Excel using Marco?

If you want to transfer the database records from Access to .xlsx format using a macro. Then, it is necessary to create a macro that will perform the export operation. By doing this, you can migrate the tables/ reports/ queries/ and other content from the Access database to Excel. Follow the step-by-step guide given below to achieve this: 

Step 1. Open Microsoft Access

Launch the MS Access application on your machine to export database records to the desired file format. 

Step 2. Create a New Macro

• Navigate to the “Create” tab.
• Select “Macro” to create a new one.

Step 3. Add the ExportAction

• In the macro design view, choose “TransferSpreadsheet” from the Action Catalog.
• Place the “TransferSpreadsheet” action onto the macro design grid.

Configure the action with these properties:

Spreadsheet Type: Excel (.xls) or Excel Workbook (.xlsx) based on your Excel version.

Database Object: Specify the table or query for export.

• Specify the File Name, indicating the path and name of the Excel file for export.
• Choose “Yes” for Has Field Names if your data includes field names; opt for “No” if it doesn’t.

Step 4. Save and Run the Macro

• Save the macro by giving it a name and closing the macro design view.
• To run the macro, go back to the “Database Tools” tab, click on “Run Macro,” and select the macro you just created.
• The data should now be exported to the specified Excel file.

Additional Tips:

• Close Excel before running the macro.
• Confirm the correct file path and necessary write permissions.
• Customize your macro to include additional tasks as needed.

How to Export Data from Access to Excel using VBA?

VBA aka Visual Basic for Applications. This is used to write scripts in VBA to automate the process of conversion. Make sure to abide by the steps given below to get the desired results. 

Step 1. Open Excel and Access

• Open both Excel and Access, as you’ll be working with both applications.

Step 2. Enable Developer Tab in Excel

If you don’t have the Developer tab visible, you need to enable it. Go to Excel and follow these steps:

• Click on “File” in the top left corner.
• Choose “Options” at the bottom of the left-hand menu.
• In the Excel Options dialog, select “Customize Ribbon.”
• Check the “Developer” option in the right column.
• Click “OK.”

Step 3. Open VBA Editor

In Excel, open the VBA Editor by going to the Developer tab and clicking “Visual Basic.”

Step 4. Write VBA Code

In the VBA Editor, create a script to connect to Access, fetch data, and transfer it to Excel. Here is an example:

Sub ExtractDataFromAccessToExcel()

    ‘ Set a reference to Microsoft ActiveX Data Objects Library:

    ‘   1. In the VBA Editor, go to Tools -> References.

    ‘   2. Check “Microsoft ActiveX Data Objects x.x Library” (x.x can be a version number).

    ‘ Declare variables

    Dim conn As Object

    Dim rs As Object

    Dim strSQL As String

    Dim i As Integer

    ‘ Set the connection string

    Dim connStr As String

    connStr = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\Your\Database.accdb;”

    ‘ Create a new connection

    Set conn = CreateObject(“ADODB.Connection”)

    ‘ Open the connection

    conn.Open connStr

    ‘ SQL Query to retrieve data (replace “YourTable” with your actual table name)

    strSQL = “SELECT * FROM YourTable;”

    ‘ Create a new recordset

    Set rs = CreateObject(“ADODB.Recordset”)

    ‘ Open the recordset

    rs.Open strSQL, conn

    ‘ Transfer data to Excel starting from cell A1

    For i = 1 To rs.Fields.Count

        Cells(1, i).Value = rs.Fields(i – 1).Name

    Next i

    ‘ Transfer data from recordset to Excel

    Range(“A2”).CopyFromRecordset rs

    ‘ Close connections

    rs.Close

    conn.Close

    ‘ Clean up

    Set rs = Nothing

    Set conn = Nothing

End Sub

Step 5. Run the VBA Code

In the VBA Editor, find the module where you pasted the code. If you don’t have a module, you can insert one by right-clicking on the left sidebar, selecting “Insert,” and choosing “Module.”

• Copy and paste the VBA code into the module.
• Close the VBA Editor.
• In Excel, go to the “Developer” tab.
• In the Developer tab, click on “Macros” or “Macros Security” (depending on your Excel version).
• In the “Macro” dialog box, you should see the macro “ExtractDataFromAccessToExcel.” Select it.
• Click “Run.”

Limitations of Free Solution to Export Data from Microsoft Access to Excel

• You cannot export modules or macros to Excel.
• If the form, report, or datasheet you’re trying to export contacts subform, subreport, or sub datasheet, then you can only export the main forms, report, or datasheet.
• You’ll have to repeatedly export the subreport, subform, and sub datasheet individually.
• In a single manual export operation, you cannot export more than one database object.
• You can export reports only in .xls format and not in newer .xlsx format.

If you have a corrupted source file, these methods will not work for you and you’ll need to install the software.

Frequently Asked Questions