Strange TransferSpreadsheet Behavior

[JoeM]

Using Office 2007...

I have some VBA code to import 6 different worksheets in a single workbook.
If I want to import a single tab, just using the name of the sheet as the "Range" argument, it works just fine. That code looks like this:

DoCmd.TransferSpreadsheet acImport, 10, "tbl-EN_Records", myExcelFileName, True, "Enrollment!"

Where "Enrollment" is the worksheet name that the data resides on.

Now suppose that I do not want to import all the columns on my "Enrollment" tab. I want to exclude the first two columns. So I have set up a named range named "Rng_Enrollment". So I change my code to look like this:

DoCmd.TransferSpreadsheet acImport, 10, "tbl-EN_Records", myExcelFileName, True, "Rng_Enrollment"

Now here is the strange part. My second bit of code posted above will only run if the Excel workbook is open, whereas the first bit of code will run fine on a closed workbook.

Using worksheet names in the Range argument does not seem to require the workbook to be open.


Is that normal? In using named ranges in importing data via Transferspreadsheet, does the Excel workbook need to be open?

Thanks

[ranman256]

NO, it does NOT require the file to be open.
I have run this, and it imports all just fine closed.

[JoeM]

NO, it does NOT require the file to be open.

That is what I always thought too, but I am experiencing some unexplained behavior that appears to contradict that statement.

If I try to run it, I get the following error message:
Run-time error '3011':
The Microsoft Access database engine could not find the object 'Rng_Enrollment'. Make sure the object exists and that you spell its name and path name correctly.

Yet, if I open the Excel file on my computer first, and then run the process, it successfully imports the named range without any errors.

Can anyone explain this behavior, or what has to be done to get around it (I suppose I could have the database open my Excel file first, import my ranges, then close, but it would be nice not to have to do that)?

[ItsMe]

Maybe it is a version thing, xls vs. xlsx. Or maybe it is that there are some features like formatting and or themes that is causing Access to not interpret the file correctly. Like you mentioned, might have to go the route of automation.

[JoeM]

Maybe it is a version thing, xls vs. xlsx. Or maybe it is that there are some features like formatting and or themes that is causing Access to not interpret the file correctly. Like you mentioned, might have to go the route of automation.

Interesting thought...

So it is an Access 2007 database (accdb), but the Excel file was an "xls" file, to allow users with older versions to use it.
I tried re-saving the file as an "xlsx" file, and lo and behold, it worked without having to open the Excel file first!

Interesting behavior. Thanks for helping me identify the issue.

[ranman256]

I WILL get errors if the XL file is open, just like yours.

[JoeM]

I WILL get errors if the XL file is open, just like yours.

No, I was saying exact opposite.
With the "XLS" file, I was getting errors if the Excel file was NOT open. If it was open, it would run just fine.