Array and Named Ranges
 

I have an excel model ("first.xls") which takes array data
("DataArray")from a second model ("second.xls"). I normally just copy
and paste this data from "second.xls" to "first.xls". However I want
to use VBA to pull the data from "second.xls" into a VBA
array("PriceArray") while "first.xls" is running.

I have set up a named cell in first.xls ("NamedCell")which has a text
string refering to the named range in "second.xls"(i.e. "NamedCell" in
"first.xls" has the text "DataArray").

The code fails on the last line. I am unable to transfer the array
into VBA.



Private Sub GetPricingFiles()
'This sub opens up the pricing files associated with this model
'The file paths and file names need to be entered into the appropriate
ranges
'in the excel sheets
Dim objbook As Workbook
Dim strbookname As String
Dim PricingFileNamedRange As String
Dim ImportArray As Variant

'Open Source spreadsheet
strbookname = "second.xls"

'Pass text from named cell into VBA variable
PricingFileNamedRange = Range("NamedCell").Value

'Check to see if file is already open before opening
For Each objbook In Workbooks
If objbook.Name = strbookname Then
MsgBox ("This file is already open. Please close it and start
again.")
Exit Sub
End If
Next objbook

'Open file if file is not opened
Workbooks.Open Filename:="C:\second.xls"

'Once file is opened, transfer data from DataArray in second.xls to
'PriceArray in VBA

PriceArray = Workbooks(strbookname).Range(PricingFileNamedRange )

End Sub

Thanks TS

Array and Named Ranges
 

" wrote...
...
The code fails on the last line. I am unable to transfer the array
into VBA.


Private Sub GetPricingFiles()
...
PriceArray = Workbooks(strbookname).Range(PricingFileNamedRange )

End Sub

Even if second.xls is a single sheet, Excel 2.1 format .XLS file, and despite
the fact that you could create external reference links to cells in it using
just =second.xls!X99, that doesn't change the fact that the Workbook class
doesn't have Range properties. Try

PriceArray = Workbooks(strbookname).Worksheets(1).Range(Pricing FileNamedRange)

--
To top-post is human, to bottom-post and snip is sublime.

Array and Named Ranges
 

PriceArray =
Workbooks(strbookname).Names(PricingFileNamedRange ).RefersToRange

--
Regards,
Tom Ogilvy


wrote in message
om...
I have an excel model ("first.xls") which takes array data
("DataArray")from a second model ("second.xls"). I normally just copy
and paste this data from "second.xls" to "first.xls". However I want
to use VBA to pull the data from "second.xls" into a VBA
array("PriceArray") while "first.xls" is running.

I have set up a named cell in first.xls ("NamedCell")which has a text
string refering to the named range in "second.xls"(i.e. "NamedCell" in
"first.xls" has the text "DataArray").

The code fails on the last line. I am unable to transfer the array
into VBA.



Private Sub GetPricingFiles()
'This sub opens up the pricing files associated with this model
'The file paths and file names need to be entered into the appropriate
ranges
'in the excel sheets
Dim objbook As Workbook
Dim strbookname As String
Dim PricingFileNamedRange As String
Dim ImportArray As Variant

'Open Source spreadsheet
strbookname = "second.xls"

'Pass text from named cell into VBA variable
PricingFileNamedRange = Range("NamedCell").Value

'Check to see if file is already open before opening
For Each objbook In Workbooks
If objbook.Name = strbookname Then
MsgBox ("This file is already open. Please close it and start
again.")
Exit Sub
End If
Next objbook

'Open file if file is not opened
Workbooks.Open Filename:="C:\second.xls"

'Once file is opened, transfer data from DataArray in second.xls to
'PriceArray in VBA

PriceArray = Workbooks(strbookname).Range(PricingFileNamedRange )

End Sub

Thanks TS