View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Basilisk96 Basilisk96 is offline
external usenet poster
 
Posts: 37
Default How to Access a Range in Another Workbook

On Nov 19, 9:06 pm, SteveM wrote:
This seems pretty trivial but it's not clear to me how to do refer to
range object in an external source.

I want to access and use a named Range in a second workbook. I'm
thinking that I can declare a Range variable in my ActiveWorkbook and
set that equal to the external range. So:

Say the primary Workbook is Model.xls and the one I want to access is
Data.xls, and the Data named Range is DRange

So in a Model module

Sub Something()

Dim rng as Range
Set rng = Workbooks("Data.xls")...Range("DRange")

But I don't know how to finish the line.

I know I'm not asking for clever, only mundane. But if you could help
that would be great.

Thanks,

SteveM


Steve,
There may be other ways to do this - I'll tell you how I've dealt with
this in the past, and maybe someone else can chime in as well.
The data structure you're looking for is Names. It contains, among
other things, "named ranges" from a workbook. If the range is in
another workbook, you have to open it, access the range, process it,
then close the workbook. Here's an example:

So in a Model module:

Sub Something()
Dim rng As Range
Dim wb As Workbook
Set wb = Workbooks.Open("C:\FullPathToYourFile\Data.xls")
Set rng = wb.Names("DRange").RefersToRange
'Do useful stuff with rng here, such as display its Address, for
example...
MsgBox rng.Address
wb.Close SaveChanges:=False 'or SaveChanges:=True, depends on
your needs
Set wb = Nothing
Set rng = Nothing
End Sub

It seems to me there should be a less "hackish" way, one that doesn't
keep the other workbook open during the processing? I'm all ears.

Cheers,
-Basilisk96