Solved

Import and Export Ms Excel data

Posted on 2000-02-16
11
720 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Expert Comment

by:tomk120999
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:woaini
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Ian.

WonHop
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now