Import for specific worksheet

[Guest]

Based on other posts, instead of linking I set up tables with the data types
I need. I need to import data from workbooks that have multiple worksheets.
I now want to set up a macro to import them consecutively. These will be
updated periodically. I plan to use delete queries to empty the tables and
then run the macro to import the current data.

My problem is referencing the worksheet. I can import spreadsheets where
there is only tab no problem. I've tried adding the worksheet name with
several variations without success. Please tell me what I'm doing wrong.

F:\CKT Lists\Affiliate DS3 List.xls, "Myworksheet!"

Tried $ instead of ! as well. Could the problem be the version of Excel?

Thanks,
Mary

 

[pietlinden]

Based on other posts, instead of linking I set up tables with the data types
I need. I need to import data from workbooks that have multiple worksheets.
I now want to set up a macro to import them consecutively. These will be
updated periodically. I plan to use delete queries to empty the tables and
then run the macro to import the current data.

My problem is referencing the worksheet. I can import spreadsheets where
there is only tab no problem. I've tried adding the worksheet name with
several variations without success. Please tell me what I'm doing wrong.

F:\CKT Lists\Affiliate DS3 List.xls, "Myworksheet!"

Tried $ instead of ! as well. Could the problem be the version of Excel?

Thanks,
Mary

F:\CKT Lists\Affiliate DS3 List.xls!Myworksheet"