Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 45
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
make camera object refer to dynamic range Mica Excel Discussion (Misc queries) 0 March 27th 10 01:14 AM
Can you make a chart's data *always* refer to a dynamic range name? Chrisso Charts and Charting in Excel 2 August 24th 07 12:30 PM
Help - Does value exist in range of CLOSED workbook? c mateland Excel Worksheet Functions 9 April 16th 07 06:53 PM
How to copy a range to a closed workbook max Excel Programming 1 July 16th 04 11:13 AM
How do you refer to a dynamic named range? Ian Staines Excel Programming 3 September 14th 03 10:48 PM


All times are GMT +1. The time now is 11:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"