Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

Import and Export Ms Excel data

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
woaini
Asked:
woaini
1 Solution
 
paaskyCommented:
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
 
Green_BoyCommented:
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
 
lmerrellCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tomk120999Commented:
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
 
WonHopCommented:
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
 
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 ?
0
 
tomk120999Commented:
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
 
WonHopCommented:
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
 
WonHopCommented:
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
 
ianBCommented:
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
 
WonHopCommented:
Thanks Ian.

WonHop
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now