Solved

Import and Export Ms Excel data

Posted on 2000-02-16
11
721 Views
Last Modified: 2012-05-04
Hi,

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.
0
Comment
Question by:woaini
11 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2528430
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
0
 

Expert Comment

by:Green_Boy
ID: 2528481
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.

0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2528490
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
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 3

Expert Comment

by:tomk120999
ID: 2528691
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...
0
 
LVL 2

Accepted Solution

by:
WonHop earned 70 total points
ID: 2529330
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.
0
 

Author Comment

by:woaini
ID: 2529923
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 ?
0
 
LVL 3

Expert Comment

by:tomk120999
ID: 2531097
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...
0
 
LVL 2

Expert Comment

by:WonHop
ID: 2531230
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

0
 
LVL 2

Expert Comment

by:WonHop
ID: 2548414
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
0
 
LVL 5

Expert Comment

by:ianB
ID: 2555772
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
0
 
LVL 2

Expert Comment

by:WonHop
ID: 2555967
Thanks Ian.

WonHop
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question