I have a process that I support that has started generating an error.

This process imports a spreadsheet from Excel into Access. It then runs
queries to split the data into individual accounts and then it exports the
split up data back to Excel using the following command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Temp55a",
strLoc, True

83 Spreadsheets of 86 get created.
The 84th spreadsheet fails with error number 3434 - Cannot expand named range.
Once the error is encountered the database closes with the process unfinished.

I am completely baffled by this. There is NO named range in the command.

The parameters break down to action type (acExport), Excel version
(acSpreadsheetTypeExcel3), Access table name (Temp55a), File Name (strLoc),
Column headings (True).

I have searched extensively trying to find an answer to this issue with no
luck. In fact, I have not even seen anyone else having a similar issue.

Any help would be greatly appreciated.

Just a thought: there isn't a blank row in the data being exported is there?
Excel may be creating a hidden name for the imported data that isn't being
addressed properly (as you probably know, not being a database package -
despite being used far too much as one! - Excel cannot understand lists with
blank rows).

"JH" wrote:

I have a process that I support that has started generating an error.

This process imports a spreadsheet from Excel into Access. It then runs
queries to split the data into individual accounts and then it exports the
split up data back to Excel using the following command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Temp55a",
strLoc, True

83 Spreadsheets of 86 get created.
The 84th spreadsheet fails with error number 3434 - Cannot expand named range.
Once the error is encountered the database closes with the process unfinished.

I am completely baffled by this. There is NO named range in the command.

The parameters break down to action type (acExport), Excel version
(acSpreadsheetTypeExcel3), Access table name (Temp55a), File Name (strLoc),
Column headings (True).

I have searched extensively trying to find an answer to this issue with no
luck. In fact, I have not even seen anyone else having a similar issue.

Any help would be greatly appreciated.

No. There are no blank rows in the Access table that is being exported.

Any other ideas?

"Smallweed" wrote:

Just a thought: there isn't a blank row in the data being exported is there?
Excel may be creating a hidden name for the imported data that isn't being
addressed properly (as you probably know, not being a database package -
despite being used far too much as one! - Excel cannot understand lists with
blank rows).

"JH" wrote:

I have a process that I support that has started generating an error.

This process imports a spreadsheet from Excel into Access. It then runs
queries to split the data into individual accounts and then it exports the
split up data back to Excel using the following command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Temp55a",
strLoc, True

83 Spreadsheets of 86 get created.
The 84th spreadsheet fails with error number 3434 - Cannot expand named range.
Once the error is encountered the database closes with the process unfinished.

I am completely baffled by this. There is NO named range in the command.

The parameters break down to action type (acExport), Excel version
(acSpreadsheetTypeExcel3), Access table name (Temp55a), File Name (strLoc),
Column headings (True).

I have searched extensively trying to find an answer to this issue with no
luck. In fact, I have not even seen anyone else having a similar issue.

Any help would be greatly appreciated.

acSpreadsheetTypeExcel3 is way out of date. I don't know what version of
Excel you are using, but since 2000, it has been acSpreadsheetTypeExcel9. I
have heard of other problems using the same code. I would suggest you either
align your code to reference the version of Excel you are using or leave it
blank at 9 will be used by Default.
--
Dave Hargis, Microsoft Access MVP


"JH" wrote:

No. There are no blank rows in the Access table that is being exported.

Any other ideas?

"Smallweed" wrote:

Just a thought: there isn't a blank row in the data being exported is there?
Excel may be creating a hidden name for the imported data that isn't being
addressed properly (as you probably know, not being a database package -
despite being used far too much as one! - Excel cannot understand lists with
blank rows).

"JH" wrote:

I have a process that I support that has started generating an error.

This process imports a spreadsheet from Excel into Access. It then runs
queries to split the data into individual accounts and then it exports the
split up data back to Excel using the following command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Temp55a",
strLoc, True

83 Spreadsheets of 86 get created.
The 84th spreadsheet fails with error number 3434 - Cannot expand named range.
Once the error is encountered the database closes with the process unfinished.

I am completely baffled by this. There is NO named range in the command.

The parameters break down to action type (acExport), Excel version
(acSpreadsheetTypeExcel3), Access table name (Temp55a), File Name (strLoc),
Column headings (True).

I have searched extensively trying to find an answer to this issue with no
luck. In fact, I have not even seen anyone else having a similar issue.

Any help would be greatly appreciated.