Cannot expand data range - Error message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Please help - I keep getting an error message - "Cannot expand data range"
when I am trying to transfer a table from Access to Excel - I am using
"Transfer Spreadsheet" command with Excel 97. How can i get round this - I
want it to overwrite the current tab in Excel.

Cheers

Smudger
 
John,

This is the "Transfer Spreadsheet" part of the Macro which is failing

Transfer type : Export
Spreadsheet type : Microsoft Excel 97
Table Name : Tbl_050_Output Stage 1
File Name : H:\Complaints\Complaints.xls
Has Field names : Yes
Data Ranges : (This field I have left blank)

Thanks again for your help on this - I want to get it automated and
validated before the weekend. Cheers
 
I don't know what's happening here, Smudger. I've never seen this
message myself and so far can't reproduce it, and the only instance of
it in Google is this here thread.

This is mostly guesswork, but here are some things I'd check:

- is anything else accessing or referring to the worksheet you are
exporting to (which is presumably called "Tbl_050_Output Stage 1"? E.g.
formulas, linked Access tables, ODBC connections, Excel data functions,
etc.?

- are there more than, or nearly, 65,535 records in your table?

Also: why not delete the workbook and export to a new one? Or delete the
worksheet and let the export create a new one?
 
John Nurick said:
I don't know what's happening here, Smudger. I've never seen this
message myself and so far can't reproduce it, and the only instance of
it in Google is this here thread.
Click to expand...

I've seen similar. Here are some steps to reproduce:

1) Create a new Excel table:

CREATE TABLE
[Excel 8.0;HDR=YES;Database=C:\smudger.xls;].MyNewTable
(MyCol1 FLOAT NULL)
;

2) Create a row of data:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\smudger.xls;].MyNewTable
(MyCol1) VALUES (12.34)
;

3) Open the newly-created file in the Excel UI.
4) On the sheet named 'MyNewTable', add the value 56.78 to cell A3.
5) Close and save the workbook.
6) Try inserting another row:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\smudger.xls;].MyNewTable
(MyCol1) VALUES (99.99)
;

I get provider error, 'Cannot expand named range'. I'd expect this
too. The CREATE TABLE created an Excel defined Name ('named Range')
for the Range A1:A1. The first successful INSERT INTO expanded the
Range to A1:A2. Manually entering a value in cell A3 prevented the
Name's Range to be expanded any further, that's why the second INSERT
failed.

On the other hand, using the sheet name, rather than the Name name,
will work:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\smudger.xls;].[MyNewTable$]
(MyCol1) VALUES (99.99)
;

The above is yet another good reason for not using TransferSpreadsheet
syntax. Never trust a wizard to write sql code <g>.

Jamie.

--
 
hi,
you have data in the excel file that is in the query range.
go to excel and delete all data that is below the header
row.
that should solve your problem'
:)
 
hi,
you have data in the target excel range. excel will not
allow data to be overwritten. you will have to delete the
data in the target range in excel.
 
I get provider error, 'Cannot expand named range'. I'd expect this
too. The CREATE TABLE created an Excel defined Name ('named Range')
for the Range A1:A1. The first successful INSERT INTO expanded the
Range to A1:A2. Manually entering a value in cell A3 prevented the
Name's Range to be expanded any further, that's why the second INSERT
failed.

On the other hand, using the sheet name, rather than the Name name,
will work:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\smudger.xls;].[MyNewTable$]
(MyCol1) VALUES (99.99)
;

The above is yet another good reason for not using TransferSpreadsheet
syntax. Never trust a wizard to write sql code <g>.
Click to expand...

That must be it. Thank you. I'm sure I'd have caught it first time if
Smudger had said "named range" rather than "data range".But I only use
TransferSpreadsheet for simple exporting-cum-creation of workbooks and
had never noticed that it creates a named range as well as a named
worksheet.
 
Thanks everyone for your help on this!

I really thought that it would allow you to overwrite as I use this quite
often.
I have deleted out the macro and set it up again with a new Excel sheet - It
ran fine and over night on an autoexec it ran again ok?!

Thanks again to everyone that posted replies

Cheers

Smudger
 
Back
Top