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

VB code in access to import and export of excel

P: 2
Hello ,

How to import and export the excel file into Access through VB Code


let me know , if anybody knows the same
Mar 10 '08 #1
Share this Question
Share on Google+
4 Replies


P: 18
Hello ,

How to import and export the excel file into Access through VB Code


let me know , if anybody knows the same
Hello sethson22, try using TransferSpreadsheet method for import.
Mar 10 '08 #2

P: 2
Hello sethson22, try using TransferSpreadsheet method for import.

i don't have any idea about this method , can you elobrate more .............how i can use this method
Mar 11 '08 #3

P: 18
i don't have any idea about this method , can you elobrate more .............how i can use this method
Access Help or Google offer very good explanations on this topic.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
  2.                                             "tblAccess", "YourExcelTable",    False, "A2:Z16000"
  3.  
This code snippet imports an Excel workbook named "YourExcelTable" into "tblAccess" - this your target table within your Access database. If you haven't created one, TransferSpreadsheet will create it automatically.

"A2:Z16000" is imported range.

Beware if your Excel workbook has multiple spreadsheets! Then you'll have to import them separately, and you have to tell it VBA in the range. For example, "Sheet1!A1:D5" will import only cells A1:D5 from Sheet1, but won't touch other worksheets.

Good luck,
OfficeDummy
Mar 11 '08 #4

P: 15
If you use the Access import wizard, it will let you build/create an Import Specification, which tells Access how to interpret each field (as number, string, etc) and where to put it. I believe you can create an Export Specification as well.

hope this helps

http://www.db-pros.com
Mar 11 '08 #5

Post your reply

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