How to Access a Range in Another Workbook

[SteveM]

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

 

[Guest]

Hi Steve,

See if this helps. Macro is in workbook Model and data is in workbook Data.

It copies from Data workbook to workbook Model:-

Sub test()

Dim wbData As Workbook
Dim wbModel As Workbook
Dim rng As Range

Set wbData = Workbooks("Data.xlsm")
Set wbModel = ThisWorkbook

Set rng = wbData.Sheets("Sheet1").Range("A1:A10")

rng.Copy Destination:=wbModel.Sheets("Sheet1").Range("B1")

End Sub

 

[Basilisk96]

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

 

[Guest]

Hi again Steve,

My last answer not quite right for your question. You indicated a named
range in Data so use this instead.

Sub test()

Dim wbData As Workbook
Dim wbModel As Workbook
Dim rng As Range

Set wbData = Workbooks("Data.xlsm")
Set wbModel = ThisWorkbook

With wbData.Sheets("Sheet1")
Set rng = .Range("Drange")
End With

rng.Copy Destination:=wbModel.Sheets("Sheet1").Range("B1")

End Sub

 

[SteveM]

Hi again Steve,

My last answer not quite right for your question. You indicated a named
range in Data so use this instead.

Sub test()

Dim wbData As Workbook
Dim wbModel As Workbook
Dim rng As Range

Set wbData = Workbooks("Data.xlsm")
Set wbModel = ThisWorkbook

With wbData.Sheets("Sheet1")
Set rng = .Range("Drange")
End With

rng.Copy Destination:=wbModel.Sheets("Sheet1").Range("B1")

End Sub

Thanks very much guys, but I figured out this single line solution.

Set rng = Workbooks("Data.xls").Sheets("DataSheet").Range("DRange")

Note that the workbook is already open.

SteveM

P.S. I should read the documentation more often

 

[Guest]

A caveat - there could be problems if there are global and local references
that use the same name:
http://xldynamic.com/source/xld.Names.html

FWIW - I've not seen another method that does not require the workbook to be
open. Once the workbook is closed, you'll get "object required" run-time
errors if you try to do anything with the rng variable.

 

[Basilisk96]

A caveat - there could be problems if there are global and local references
that use the same name:http://xldynamic.com/source/xld.Names.html

FWIW - I've not seen another method that does not require the workbook to be
open. Once the workbook is closed, you'll get "object required" run-time
errors if you try to do anything with the rng variable.

Thanks, that's a handy reference!

 

[Guest]

quite welcome

Thanks, that's a handy reference!