How to export a Microsoft Access table/query into an Excel file and vice versa?

Exporting and importing database tables in Microsoft Access using Excel file format without using format specification can be done by combining two DoCmd methods, these are:

  1. Docmd.OutputTo
  2. DoCmd.TransferSpreadsheet

OutputTo command is used to export tables/queries to an external file.  Below is an example that exports a query to an Excel file.

DoCmd.OutputTo acOutputQuery, "Query01", acFormatXLS

The command will prompt automatically a file dialog box for you to specify where to save the exported data.

TransferSpreadsheet command is used to import external Excel file into a table in your Microsoft Access database.  Below is an example that imports an external Excel file.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpTable", CurrentProject.Path & "\Query01.xls", True

These two commands are very useful in transferring data from one system to the other using any external device like USB or the Internet.

Advertisements
About

Software developer living in the Philippines.

Posted in Microsoft Access
4 comments on “How to export a Microsoft Access table/query into an Excel file and vice versa?
  1. abhi says:

    can u explain this with code how to it or mail me at ondspot.abhi@gmail.com

  2. saravanan ganesan says:

    As I need to export the ms-access tables to excel file with the help of javascript… So can u plz help me out on thz???

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: