Worksheet within Excel Workbook

[rpkfish]

I know that when I create an External Data Import from an Excel Workbook I can define which worksheet I wish to use.



My current problem is that I have a Workbook with four Worksheets, three of which feed (append) data into different Tables in my database. The 4th one supplies the data.

How do I import from the correct worksheet using ImportExportSpreadsheet? Or is there an alternative way. I need the macro so that it can be a Menu driven option.

Help, please!

[CJ_London]

Not quite sure what you are asking - perhaps some example data will help me/us to understand.

whichever method you use, you can only 'link' to one worksheet/range per import/export - but you can have 4 import/exports, one for each worksheet/range.

If these tables need to interact in some way as part of the import process, either import or link to each worksheet/range so you have 4 tables in your db - then use append and/or update queries in the usual way

[rpkfish]

I am happy that I have to have 4 separate ImportExportSpreadsheet statements but how do I define the Worksheet within the Workbook. Every example of coding that I can find refers to Workbook.xlsx. Should I say Workbook.worksheet.xslx? When I look at the statement in Saved Imports it only refers to Workbook.xlsx despite the fact that I pointed to the correct worksheet in the creation.

[CJ_London]

as part of the creation you are given the opportunity to create a name for the table - which defaults to the name of the worksheet

What code are you actually using?

[ssanfu]

Maybe Ken Snell's site will help.

See
Import Data from All Worksheets in a single EXCEL File into Separate Tables via TransferSpreadsheet (VBA)
http://www.accessmvp.com/kdsnell/EXC...pAllWktsSepTbl

If you want to import each Excel worksheet to a specific Access table (append or overwrite), you will have to modify the code.