refer to dynamic range in closed workbook with ADO
 

I have a listbox on a userform.

Am trying to read a named range in a closed workbook, and then retur
the range to a listbox.

If it's a static named range then its fine. If it's a dynamic name
range(offset etc.) then the range cannot be read into the control.

Any ideas anyone?
Cheers,
matt

--
MattShoreso
-----------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...nfo&userid=347
View this thread: http://www.excelforum.com/showthread.php?threadid=37703

refer to dynamic range in closed workbook with ADO
 

Open the workbook.

--
Regards,
Tom Ogilvy


"MattShoreson"
wrote in message
news:MattShoreson.1q9c2e_1118149649.1216@excelforu m-nospam.com...

I have a listbox on a userform.

Am trying to read a named range in a closed workbook, and then return
the range to a listbox.

If it's a static named range then its fine. If it's a dynamic named
range(offset etc.) then the range cannot be read into the control.

Any ideas anyone?
Cheers,
matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=377032

refer to dynamic range in closed workbook with ADO
 

MattShoreson wrote:
If it's a static named range then its fine. If it's a dynamic named
range(offset etc.) then the range cannot be read

If you know the worksheetname and the starting cell address (say, cell
E4 on Sheet1):

SELECT * FROM [Sheet1$E4:IV65535];

If the range is the only one on the worksheet:

SELECT * FROM [Sheet1$];

Pay attention to whether you need HDR=YES or HDR=NO.

refer to dynamic range in closed workbook with ADO
 

This isn't an really a solution per se, but here's a workaround:

While you can't access dynamic named ranges in a closed workbook, what you
CAN do is to hard-code the dynamic ranges when the data workbook closes. In
the data workbook, add the following code to the PersonalWorkbook code sheet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Me.Names("ListName")
.Value = ("=Sheet1!" & .RefersToRange.Address)
End With
End Sub

Private Sub Workbook_Open()
Me.Names("ListName").Value =
"=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A), 1)"
End Sub

When the data workbook closes, it hard-keys the range of the "ListName."
When the workbook is opened again (ostensibly to add data), the hard-keyed
value is replaced with the dynamic formula that will capture any additional
rows in the named ranged. When the workbook is again closed, the complete
range is hard-keyed again, and so on.

-EW


"MattShoreson" wrote:


I have a listbox on a userform.

Am trying to read a named range in a closed workbook, and then return
the range to a listbox.

If it's a static named range then its fine. If it's a dynamic named
range(offset etc.) then the range cannot be read into the control.

Any ideas anyone?
Cheers,
matt.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=377032

refer to dynamic range in closed workbook with ADO
 

Eric,

Thanks for the answer. That's the road I took. Resizing and renamin
the ranges on closing.

Tom, the whole reason for using ADO was in order not to open th
workbook thereby making the workbook invisble to the user.
Screenupdating wouldn't cut the mustard either. Cheers for th
response though

--
MattShoreso
-----------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...nfo&userid=347
View this thread: http://www.excelforum.com/showthread.php?threadid=37703

refer to dynamic range in closed workbook with ADO
 

the whole reason for using ADO

First mention of the term ADO was in the above quoted line - not in the
original question - so I had no specific knowledge of what you were about.
Sounded like a linking/binding problem.

Cheers as Well.

--
Regards,
Tom Ogilvy

"MattShoreson"
wrote in message
news:MattShoreson.1qd9ql_1118333118.3891@excelforu m-nospam.com...

Eric,

Thanks for the answer. That's the road I took. Resizing and renaming
the ranges on closing.

Tom, the whole reason for using ADO was in order not to open the
workbook thereby making the workbook invisble to the user.
Screenupdating wouldn't cut the mustard either. Cheers for the
response though.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=377032

refer to dynamic range in closed workbook with ADO
 

LOL - and the title.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=377032