Import and Export Ms Excel data

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.

Regards,
Paasky

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.

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
UNION ALL
Select * From Table2;

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

lmerrell

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

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"
Range("A1").Select

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


I hope this helps.

Thanks

WonHop

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.

Thanks
WonHop

Hi,

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.

Ian
Community Support @ Experts Exchange

Thanks Ian.

WonHop