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

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

--
Regards,

OssieMac


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

On Nov 19, 9:06 pm, SteveM <sbm...@vzavenue.net> 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

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

--
Regards,

OssieMac


"OssieMac" wrote:

> 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
>
> --
> Regards,
>
> OssieMac
>
>
> "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
> >

On Nov 19, 9:39 pm, OssieMac <Ossie...@discussions.microsoft.com>
wrote:
> 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
>
> --
> Regards,
>
> OssieMac
>
> "OssieMac" wrote:
> > 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
>
> > --
> > Regards,
>
> > OssieMac
>
> > "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

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

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" wrote:

> On Nov 19, 9:06 pm, SteveM <sbm...@vzavenue.net> 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
>

On Nov 19, 11:49 pm, JMB <J...@discussions.microsoft.com> wrote:
> 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!

quite welcome

"Basilisk96" wrote:

> On Nov 19, 11:49 pm, JMB <J...@discussions.microsoft.com> wrote:
> > 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!
>