I have a named range in my €˜FileA.xls file that references an array of data.
But say if Im working in €˜FileB.xls and €˜FileA.xls is closed, is there
any way in VB to call out and import the named range from €˜FileA.xls into
€˜FileB.xls?

You'd have to open FileA.xls - this code, placed in a module of
FileB.xls, will open FileA.xls (or use it if it's already opened), use
a range called "NamedRange", and copy its content to Sheet2 in
FileB.xls, starting on A1. If FileA.xls is already open, it will
still perform the copy, but will not close the file.

Public Sub ImportNamedRange()
Dim rng As Excel.Range
Dim wkb As Excel.Workbook
Dim blnClose As Boolean

Const strName As String = "NamedRange"

blnClose = False
Set rng = ThisWorkbook.Sheets("Sheet2").Range("A1")

On Error Resume Next
Set wkb = Application.Workbooks("FileA.xls")
If Err.Number < 0 Then
Set wkb = Application.Workbooks.Open("FileA.xls")
blnClose = True
Err.Clear
End If
On Error GoTo 0

wkb.Names(strName).RefersToRange.Copy _
ThisWorkbook.Sheets("Sheet2").Range("A1")

If blnClose Then wkb.Close (False)
End Sub



I can't think of a simpler way to do this.


On Oct 18, 4:46 pm, EA wrote:
I have a named range in my 'FileA.xls' file that references an array of data.
But say if I'm working in 'FileB.xls' and 'FileA.xls' is closed, is there
any way in VB to call out and import the named range from 'FileA.xls' into
'FileB.xls'?

Replace this:

wkb.Names(strName).RefersToRange.Copy _
ThisWorkbook.Sheets("Sheet2").Range("A1")

with this:

wkb.Names(strName).RefersToRange.Copy rng


On Oct 19, 1:21 am, iliace wrote:
You'd have to open FileA.xls - this code, placed in a module of
FileB.xls, will open FileA.xls (or use it if it's already opened), use
a range called "NamedRange", and copy its content to Sheet2 in
FileB.xls, starting on A1. If FileA.xls is already open, it will
still perform the copy, but will not close the file.

Public Sub ImportNamedRange()
Dim rng As Excel.Range
Dim wkb As Excel.Workbook
Dim blnClose As Boolean

Const strName As String = "NamedRange"

blnClose = False
Set rng = ThisWorkbook.Sheets("Sheet2").Range("A1")

On Error Resume Next
Set wkb = Application.Workbooks("FileA.xls")
If Err.Number < 0 Then
Set wkb = Application.Workbooks.Open("FileA.xls")
blnClose = True
Err.Clear
End If
On Error GoTo 0

wkb.Names(strName).RefersToRange.Copy _
ThisWorkbook.Sheets("Sheet2").Range("A1")

If blnClose Then wkb.Close (False)
End Sub

I can't think of a simpler way to do this.

On Oct 18, 4:46 pm, EA wrote:



I have a named range in my 'FileA.xls' file that references an array of data.
But say if I'm working in 'FileB.xls' and 'FileA.xls' is closed, is there
any way in VB to call out and import the named range from 'FileA.xls' into
'FileB.xls'?- Hide quoted text -

- Show quoted text -