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

 

[Tom Ogilvy]

Open the workbook.

--
Regards,
Tom Ogilvy


"MattShoreson" <[email protected]>
wrote in message
news:[email protected]...

 

[peregenem]

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.

 

[Guest]

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]

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

 

[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]...

 

[MattShoreson]

LOL - and the title.