D
danny.mays
I am using the command DoCmd.TransferSpreadsheet to transfer data from
a named range in Excel to an Access table. This works ok for importing
from one Excel file but not from another which gives the following
error message:
"The Microsoft Jet Engine could not find the object "NamedRange". Make
sure the object exists and that you spell its name and path
correctly."
I have searched the news groups to understand what may cause the
problem. The explanations are:
1. The range name is too long - I have tested and this is not the case
2. The file is corrupted - I doubt this is the case.
For both Excel files TransferSpreadsheet works when the file is
already open. The Excel file that doesn't work has a number of
different characteristics from the working file:
1. It has macros
2. It has protection
3. The named range is grouped
4. There are a number of other worksheets
I've tried eliminating these to solve the problem and am struggling to
understand which causes
the issue.
I see two alternative approaches:
1. Open the file using CreateObject("Excel.Application"). Then
transfer the data using TransferSpreadsheet. Even when correctly
deleting the object this causes an instance of Excel to remain after
each file is opened.
2. Open the file using CreateObject("Excel.Application") and transfer
the data from the named range into a recordset.
I'm in the middle of trying to get option 2 to work (with a few
problems); however, for simplicity I would prefer to use
TransferSpreadsheet.
Can anyone shed any light onto what's causing the problem?
Thanks
Dan
a named range in Excel to an Access table. This works ok for importing
from one Excel file but not from another which gives the following
error message:
"The Microsoft Jet Engine could not find the object "NamedRange". Make
sure the object exists and that you spell its name and path
correctly."
I have searched the news groups to understand what may cause the
problem. The explanations are:
1. The range name is too long - I have tested and this is not the case
2. The file is corrupted - I doubt this is the case.
For both Excel files TransferSpreadsheet works when the file is
already open. The Excel file that doesn't work has a number of
different characteristics from the working file:
1. It has macros
2. It has protection
3. The named range is grouped
4. There are a number of other worksheets
I've tried eliminating these to solve the problem and am struggling to
understand which causes
the issue.
I see two alternative approaches:
1. Open the file using CreateObject("Excel.Application"). Then
transfer the data using TransferSpreadsheet. Even when correctly
deleting the object this causes an instance of Excel to remain after
each file is opened.
2. Open the file using CreateObject("Excel.Application") and transfer
the data from the named range into a recordset.
I'm in the middle of trying to get option 2 to work (with a few
problems); however, for simplicity I would prefer to use
TransferSpreadsheet.
Can anyone shed any light onto what's causing the problem?
Thanks
Dan