Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Named Ranges in Array Formulas | Excel Worksheet Functions | |||
Use named ranges in array formula | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |