Importing from a closed workbook

A

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
 
T

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
 
F

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?
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top