Import and Export Ms Excel data


I wonder how to import data from Ms Excel to Ms Access database.

Also When I export data from Ms Access table, I would like to know how to export 1 table from Ms Access to Ms Excel spreadsheet, then export the second table data append just after the last line of data make by the first export.

Thank a lot.
Who is Participating?
WonHopConnect With a Mentor Commented:
Hello woaini,

It might be helpful to know if you want to use VBA code to do this or not.  If you do use VBA code, it will be helpful to know it you will put the information from Access into an Excel Template or put it into a brand new Excel Workbook.
Hello woaini,

You could link Excel worksheets to Access. To do this select from menu: File - Get External Data - Link Tables. Change the files of type to Excel and choose your XLS file.

After the Excel Sheet is linked that way all the changes made in Excel see also in Access. Plus you can easily move data between Excel and Access tables.

Under the file pull down menu and then select "Get External Data" . Then under the this option select import. This will then prompt you for the file that you want to import. You must select your file type as xls and then locate the file. It will then prompt you through several steps for the import rules that you want to use. Normally with excel it will default the define of the columns to the columns in the xls sheet.  Exporting data is the same thing . File pull down menu "Save As Export". I have never heard of exporting data from Access and appending to a file however. You might actually have to write some VB code that would write to a flat file and then convert this flat file to an excel sheet.  The import should be able to be automated in a macro. The export will probably like I said have to be written in VB and executed.

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

To import an excel worksheet just select File > Get External Data > Import... and then change Type of Files to the desired format and browse to the source file.

One way to export two or more tables to a single excel spreadsheet would be to combine the table by using a UNION query and selecting File > Save As/Export... then To an External File or Database.  Then Select the proper Save as File and location and click OK.

Sample UNION query:

Select * From Table1
Select * From Table2;

I am assuming the table to be of a common format.

woaini, everyone seems to have a good answer to importing/linking (I prefer linking for lots of good reasons).

A quick way to "export" data to Excel is the old cut and paste method.  That gives you the most control over placement in the spreadsheet.  Bring up the table or query data that you want to put into Excel in the Access screen.  Type CTRL+A (select all) and then CTRL+C (copy) to put the data on the clipboard.  Go to your Excel spreadsheet and position the cursor where you want the data and type CTRL+V (paste).  The data you want will be copied to the spreadsheet.  Each copy operation will also copy column (field) names from Access in the first row before the data, so just delete that row if you don't need it.

There are other ways to export data to Excel from Access, (Analyze with MS Excel, TransferText...) but the above method is the very simplest way to add data to Excel or append data to other data already in Excel.
HTH, good luck as usual...
woainiAuthor Commented:
Hi, I want to use VBA code to do all this. I don't want to use like CTRL-C etc. As the person who give a comment before you, I find out the function TransfertSpreadsheet. But it not allow me to do append when exporting. Do they have a way to do this by VBA code ?

Thank a lot

Hey, how come now I cannot add comment without giving points ?
woaini, if you want to export automatically, use this technique (i.e. do the append in Access, then export all of it to Excel), however you decide to implement it:
In Access, build a table or query (lmerrel suggested a UNION query) that contains (like a table) or returns (like a query) *all* of the records that you want to have in Excel, then export that data to Excel in one action.  The result will be one data field in Excel.  Excel has, of course, a row limit, so you might have to break up a large number of rows into smaller sections if you exceed that number (16,000 or so).  If you need more help, comment here and I'll try to work up an example for you.  If you accepted an answer by mistake, check with customer service and they can help with that.

good luck as usual...
Hello woaini...I must say that I was surprised when I saw you accepted my comment.  Please do as tomk suggested and contact customer service.

If you are not familiar with a Union query (by the way, I to think this is the best way to go), you can try adding this code in between your two TranferSpreadsheet Functions to see if this will accomplish what you want.

'This will SetFocus on cell "A1"

'This will keep moving down until it finds a blank cell.
Do Until ActiveCell.Text = "" 
 Selection.Offset(1, 0).Select

I hope this helps.



Hello woaini, I am just checking on the status here.  I am not sure that I deserve the points for this.  Could you please let us know what you did to solve your problem.  If there was no satisfactory answer then the question should be deleted.

The thing is woaini, these guys put some effort into getting the correct answers for people.  No one wants to feel like they have been cheated.  If I were one of the other guys here, I would feel cheated.

I am not sure how the other Forums do things, but here in Access, we try to work as a team.  We will say why something will and will not work.  When someone chooses and answer, they will usually say why they chose that one over the others.

I am sure the others will appreciate an answer also.

Please let us know.


I got a email from woaini explaining that he/she is having problems posting comments, I am working with the customer to resolve this. In the meantime to correct this points issue, I have posted a question to award tomk the points for this question in this area.

Community Support @ Experts Exchange
Thanks Ian.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.