Importing from a closed workbook

[Alan]

Not sure why this happens

I have an excel work book with multiple sheets. I only want to import the
current day and have used the TransferSpreadsheet method below.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test", True, "26-06-09$"

This works fine if the Workbook is open, however if closed it only creates
the first column and no data ??

Is there something I am missing - I have several similar worksheets to
import and trying to avoid opening and closing the 12 workbooks to allow
import to access

 

[Tom van Stiphout]

On Fri, 26 Jun 2009 02:33:01 -0700, Alan

Take another look at the Filename argument. Is your Excel file really
in the root on C, and has no extension? That may be a problem.

-Tom.
Microsoft Access MVP

 

[fredg]

Not sure why this happens

I have an excel work book with multiple sheets. I only want to import the
current day and have used the TransferSpreadsheet method below.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TEST",
"C:\Test", True, "26-06-09$"

This works fine if the Workbook is open, however if closed it only creates
the first column and no data ??

Is there something I am missing - I have several similar worksheets to
import and trying to avoid opening and closing the 12 workbooks to allow
import to access

No need to open a spreadsheet to import the data.
TransferSpreadsheet will work if the spreadsheet is closed.
You have something else going on here.
What does the "26-06-09$" represent?
Do you have a worksheet (or Range) with that exact name?

 

[Alan]

Fred

Sorry didnt make that explicit
The Workbook has several 'dated' sheets ie 25-06-09 /26-06-09 / etc
Not my naming convention but one provided !

Still puzzled
Thanks for your help