By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,717 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,717 IT Pros & Developers. It's quick & easy.

Import/Export access tables to xls file

P: 21
hey guys,
I have a access (2000 format) database. I want to be able to export all the tables in it onto a single .xls file and import it from an identical .xls file (for example if i email the tables ONLY to another person who has the same Db structure, he should be able to cleanly import the tables and see the data on his DB). I can currently do 2 things.
1- export all tables into one .xls file using
Expand|Select|Wrap|Line Numbers
  1. strFullPath = "D:\Database\ALL_DATA.xls"
  2. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tbl_1", strFullPath, False
  3. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tbl_2", strFullPath, False
  4. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tbl_3", strFullPath, False
  5.  
I found this code online, but I cant come up with a code that lets me import the tables in such a manner.

2- I can use a macro to export/import. I can do this export/import on individual tables (into individual expective .xls files) perfectly, but once i try to import more than one table from the .xls file it gives a problem. It messes up the tables' format. Meaning it expects tbl_2 to have the same tructure as tbl_1.

What can be wrong here? And how can i fix it... I would prefer to use a fixed version solution 1 as it feels more contrete to me (personal opinion).
Thanks guys!
Jul 20 '10 #1
Share this Question
Share on Google+
2 Replies


P: 21
I dont know if i'm asking too much fo you guys (and MS office), but is it possible to maybe send ONLY the newer portion of the database into the .xls sheet?
I mean say its a continuously growing database.. I want to send ONLY the records that i inserted/modified today, rather than send everything.
I dont have a timestamp or anything to give a sense of time on the editing, but the only way i can think of is for access/excel to manually compare and remove the ones that havent changed.... Is this possible?
Jul 20 '10 #2

NeoPa
Expert Mod 15k+
P: 31,769
If you use the same value in strFullPath, does it not write the data into separate worksheets in the same spreadsheet?

As for exporting only the deltas (differences between one day and the next) this will only be possible if you design the possibility into your data structure. Databases don't work on magic. There must be a logical way to discern the data before it can be separated. I would suggest adding a date of update field. With deltas you also need to make sure you consider all of :
Additions
Amendments
Deletions

Have you considered this aspect?
Jul 20 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.