Hi, I am using Access to populate a bunch of cells in Excel and then I want
to define a name for these cells...

Here's the business end of my code:

strAddress = "=Story!" & rngStories.Address

'Redefine range in the Order Pad
mxlWbkOrderPad.Names.Add _
Name:="StoryListFill", _
RefersToR1C1:=strAddress

Using the debug window strAddress is:

?strAddress
=Story!A2:C239

The problem is that in the Excel workbook I cannot find the name in the Name
box. However in using the menu Insert name define to display the
define name dialog this is the address for the name!

=Story!'A2':Story!$IE:$IE

If strAddress = "=Story!$A$2:$C$239" then Excel triggers an error: 1004

It seems to me that for some reason Excel has treated A2 as a workbook
reference, maybe?

I can manually define this name directly in Excel using th define name dialog.

Any idea why this is happening and, more importantly, how to have the
correct address for the name?

Any ideas or suggestions appreciated. :-)

Many thanks
Jonathan

With strAddress as "=Story!$A$2:$C$239" try changing
RefersToR1C1:=strAddress to RefersTo:=strAddress
so that a relative reference is not used.

Hope this helps
Rowan


"Jonathan" wrote:

Hi, I am using Access to populate a bunch of cells in Excel and then I want
to define a name for these cells...

Here's the business end of my code:

strAddress = "=Story!" & rngStories.Address

'Redefine range in the Order Pad
mxlWbkOrderPad.Names.Add _
Name:="StoryListFill", _
RefersToR1C1:=strAddress

Using the debug window strAddress is:

?strAddress
=Story!A2:C239

The problem is that in the Excel workbook I cannot find the name in the Name
box. However in using the menu Insert name define to display the
define name dialog this is the address for the name!

=Story!'A2':Story!$IE:$IE

If strAddress = "=Story!$A$2:$C$239" then Excel triggers an error: 1004

It seems to me that for some reason Excel has treated A2 as a workbook
reference, maybe?

I can manually define this name directly in Excel using th define name dialog.

Any idea why this is happening and, more importantly, how to have the
correct address for the name?

Any ideas or suggestions appreciated. :-)

Many thanks
Jonathan

Thanks Rowan

Jonathan

"Rowan Drummond" wrote:

With strAddress as "=Story!$A$2:$C$239" try changing
RefersToR1C1:=strAddress to RefersTo:=strAddress
so that a relative reference is not used.

Hope this helps
Rowan


"Jonathan" wrote:

Hi, I am using Access to populate a bunch of cells in Excel and then I want
to define a name for these cells...

Here's the business end of my code:

strAddress = "=Story!" & rngStories.Address

'Redefine range in the Order Pad
mxlWbkOrderPad.Names.Add _
Name:="StoryListFill", _
RefersToR1C1:=strAddress

Using the debug window strAddress is:

?strAddress
=Story!A2:C239

The problem is that in the Excel workbook I cannot find the name in the Name
box. However in using the menu Insert name define to display the
define name dialog this is the address for the name!

=Story!'A2':Story!$IE:$IE

If strAddress = "=Story!$A$2:$C$239" then Excel triggers an error: 1004

It seems to me that for some reason Excel has treated A2 as a workbook
reference, maybe?

I can manually define this name directly in Excel using th define name dialog.

Any idea why this is happening and, more importantly, how to have the
correct address for the name?

Any ideas or suggestions appreciated. :-)

Many thanks
Jonathan

You're welcome.

"Jonathan" wrote:

Thanks Rowan

Jonathan

"Rowan Drummond" wrote:

With strAddress as "=Story!$A$2:$C$239" try changing
RefersToR1C1:=strAddress to RefersTo:=strAddress
so that a relative reference is not used.

Hope this helps
Rowan


"Jonathan" wrote:

Hi, I am using Access to populate a bunch of cells in Excel and then I want
to define a name for these cells...

Here's the business end of my code:

strAddress = "=Story!" & rngStories.Address

'Redefine range in the Order Pad
mxlWbkOrderPad.Names.Add _
Name:="StoryListFill", _
RefersToR1C1:=strAddress

Using the debug window strAddress is:

?strAddress
=Story!A2:C239

The problem is that in the Excel workbook I cannot find the name in the Name
box. However in using the menu Insert name define to display the
define name dialog this is the address for the name!

=Story!'A2':Story!$IE:$IE

If strAddress = "=Story!$A$2:$C$239" then Excel triggers an error: 1004

It seems to me that for some reason Excel has treated A2 as a workbook
reference, maybe?

I can manually define this name directly in Excel using th define name dialog.

Any idea why this is happening and, more importantly, how to have the
correct address for the name?

Any ideas or suggestions appreciated. :-)

Many thanks
Jonathan