refer to dynamic range in closed workbook with ADO

[MattShoreson]

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.

[Tom Ogilvy]

Open the workbook.

--
Regards,
Tom Ogilvy


"MattShoreson" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[[email protected]]

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.

[Eric White]

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
>
>

[MattShoreson]

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.

[Tom Ogilvy]

> 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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[MattShoreson]

LOL - and the title.