Can't expand named range

[Panos]

Hi,

Does anybody know what the problem is when I export from
Access into excel. The error is can't expand named range
and it's no 3434.

Thanks in advance,

Panos.

 

[Frank Kabel]

Hi
not really sure what you're trying to do. could you explain your issue
with some more detail?

 

[Guest]

Hi Frank,

I've got an access application which produces a report,
then the report outputs into excel. The excel application
opens automatically when you run the access report.

However, whilst the report is being produced Excel
generates an error message saying that "can't expand named
range" and fails to produce the report. It works fine if
there are no rows in the query, ie it automatically opens
the excel spreadhseet and populates the header. It also
works fine for other users, so it has to be some
enviromental issue with Excel on the particular PC but
don't know what the issue is!

Thanks very much in advance,

Panos, London.

 

[Penelope]

Did you manage to resolve this? I have the same issue but see mostly
single-message threads on this subject.

People-Friendly

 

[Jamie Collins]

(e-mail address removed) (Penelope) wrote ...

Did you manage to resolve this? I have the same issue but see mostly
single-message threads on this subject.

I'll try to explain.

Say your Excel data looked like this:

Sheet1.Range("A1:C1").Value = Array("MyCol1", "MyCol2", "MyCol3")
Sheet1.Range("A2:C2").Value = Array(1,2,3)
Sheet1.Range("A3:C3").Value = Array(4,5,6)

and your defined Name ('named range') looked like this:

ThisWorkbook.Names.Add "BookLevelName", Sheet1.Range("A1:C3")

and you ran this SQL:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\MyWorkbook.xls;].BookLevelName
VALUES
(7,8,9);

The table is full, so where would you expect it to put the new row?
Perhaps you'd hope it could look at the range below the defined Name,
determine it was not being used, put the data there and extend the
definition of the Name to include the new row. Unfortunately for you,
this does not happen. I guess there is too much risk associated with
determining whether the area below the defined Name area range is
being used.

This is why I try to use the sheet name where possible e.g.

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\MyWorkbook.xls;].[Sheet1$]
VALUES
(7,8,9);

However, if you have more than one 'table' on the sheet you will get
undesirable results i.e. all tables will be considered one big table
and the insert will no doubt fail.

Probably the best way is to specify a range address:

INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\MyWorkbook.xls;].[Sheet1$A1:C4]
VALUES
(7,8,9);

Jamie.

--