VB code in access to import and export of excel

[sethson22]

Hello ,

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


let me know , if anybody knows the same

[OfficeDummy]

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.

[sethson22]

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

[OfficeDummy]

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.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                            "tblAccess", "YourExcelTable",    False, "A2:Z16000"
 

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

[dbpros]

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