_.........................
From Rem 4 in the second demo code, ( Sub FoxyMultiCellNamedRanges() ) we begin to incorporate named ranges into the to bring in the data from the two data files into the main file……
Rem 4 using named ranges for data in data ranges from data workbooks
We will create/Add 4 named ranges in total, one for each data range in the two data workbooks, and a name referring to the ranges in the main file where we want to bring in those values
To help emphasise that named ranges are actually objects which can be “in” any workbook or workbook worksheet, and which can then be referenced from other workbooks in order to get information about to which range they refer, we will introduce another workbook, “StoredNamedRangeNameObjects.xls”. This file will only contain Name objects. We will use this to store the 4 required Named range Name objects. This is quite a crazy idea.
( I can’t imagine why anyone would want to do this. I just thought, well, someone ought to do it , :-) …..Or maybe a security possibility?? – Without the file and the named range object’s information, it could be difficult to use other files requiring this information …..Or if you use worksheets scope, then in the worked example you could devote a worksheet for different data ranges – This would have the named range object or objects , and possibly other information about that data in the spreadsheet. The only thing missing would be the data itself, which might want to be kept elsewhere for, for example, security reasons.. )
Here is the File, “StoredNamedRangeNameObjects.xls” https://app.box.com/s/0dhyk41yumx857539wu0hji5fsekeg2w
Code sections '4b) and '4c) use the worksheet with Name “DataFileNameObjects” for the name objects referring to the two data workbooks ( "Data1.xls" and "Data2.xlsx" ) data ranges.
Code Section '4d) uses worksheet “MainFileNameObjects” for the name objects referring to the two Main workbook ( "MasterFile.xlsm" ) data ranges.
Rem 5 Using the Added data named ranges to bring in data from the data files into the main workbook.
The basic code line for the Import, as described in the last post are adapted to use the Added data named ranges in this sort of way:
Range(“=’C:\MyFolder\[MasturFile.xlsm]Tabelle1'B6:C7”).FormulaArray = “='C:\MyFolder\[Data2.xlsx]Tabelle1'!B6:C7”
becomes:
Range(“=’C:\MyFolder\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import”).FormulaArr ay = “='C:\MyFolder\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data”
Range(“=’C:\MyFolder\[MasturFile.xlsm]Tabelle1'NmsObjDta2Import”).FormulaArray = “='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11:C12”
becomes
Range(“=’C:\MyFolder\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import”).FormulaArr ay = “='C:\MyFolder\[ StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data”
As we are using a different workbook, ( “StoredNamedRangeNameObjects.xls “ ) for the names , both sides of the code lines must have the complete path reference.
( As the workbook “StoredNamedRangeNameObjects.xls” is open at the time of using those formulas, we could use
Range("='[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import").FormulaArr ay = "='[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data"
and
Range(“=’C:\MyFolder\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import”).FormulaArr ay = “='C:\MyFolder\[ StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data” )
_._____
At the end of the second code we have the final range info in the main workbook:
_____ Workbook: MasturFile.xlsm ( Using Excel 2007 32 bit )
Worksheet: Tabelle1
Row\Col Nutrition Energy Food Orange Apfel Suppliment BCAA EAA
The current information in the immediate Window ( that produced by code line 1080 ) will be something like this:
Code:'_= ======== You have 1 named range Name objects in workbook MasturFile.xlsm 1 Name object Name is "MainFoodheader" (the same as you gave) It has workbook scope and it refers to range "=Tabelle1!$B$5" and if in a spreadsheet formula you only want to use "MainFoodheader" with no preceding info about where that named range is, then you must be in any spreadsheet in workbook "MasturFile.xlsm" If you want to be sure to access this named range from anywhere, you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\MasturFile.xlsm'!MainFoodheader" or alternatively use a similar string like this with any of the worksheets in it: "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[MasturFile.xlsm]Tabelle1'!MainFoodheader" '_= ======== You have 1 named range Name objects in workbook Data1.xls 1 Name object Name is "Dta1Foodheader" (the same as you gave) It has workbook scope and it refers to range "=Tabelle1!$B$5" and if in a spreadsheet formula you only want to use "Dta1Foodheader" with no preceding info about where that named range is, then you must be in any spreadsheet in workbook "Data1.xls" If you want to be sure to access this named range from anywhere, you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\Data1.xls'!Dta1Foodheader" or alternatively use a similar string like this with any of the worksheets in it: "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[Data1.xls]Tabelle1'!Dta1Foodheader" '_= ======== You have no named range Name objects in workbook Data2.xlsx '_= ======== You have 4 named range Name objects in workbook StoredNamedRangeNameObjects.xls 1 Name object Name is "DataFileNameObjects!NmsObjDta1Data" (you gave "NmsObjDta1Data") It has worksheet scope and it refers to range "=[Data1.xls]Tabelle1!$B$6:$C$7" and if in a spreadsheet formula you only want to use "NmsObjDta1Data" without any preceding info about where that named range is, then you must be in spreadsheet with tab name "DataFileNameObjects" If you want to be sure to access this named range from anywhere, you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta1Data" Note: The refered to range is in worksheet "Tabelle1" Note also: The refered to range is in File "Data1.xls" 2 Name object Name is "MainFileNameObjects!NmsObjDta1Import" (you gave "NmsObjDta1Import") It has worksheet scope and it refers to range "=[MasturFile.xlsm]Tabelle1!$B$6:$C$7" and if in a spreadsheet formula you only want to use "NmsObjDta1Import" without any preceding info about where that named range is, then you must be in spreadsheet with tab name "MainFileNameObjects" If you want to be sure to access this named range from anywhere, you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta1Import" Note: The refered to range is in worksheet "Tabelle1" Note also: The refered to range is in File "MasturFile.xlsm" 3 Name object Name is "DataFileNameObjects!NmsObjDta2Data" (you gave "NmsObjDta2Data") It has worksheet scope and it refers to range "=[Data2.xlsx]Tabelle1!$B$11:$C$12" and if in a spreadsheet formula you only want to use "NmsObjDta2Data" without any preceding info about where that named range is, then you must be in spreadsheet with tab name "DataFileNameObjects" If you want to be sure to access this named range from anywhere, you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[StoredNamedRangeNameObjects.xls]DataFileNameObjects'!NmsObjDta2Data" Note: The refered to range is in worksheet "Tabelle1" Note also: The refered to range is in File "Data2.xlsx" 4 Name object Name is "MainFileNameObjects!NmsObjDta2Import" (you gave "NmsObjDta2Import") It has worksheet scope and it refers to range "=[MasturFile.xlsm]Tabelle1!$B$11:$C$12" and if in a spreadsheet formula you only want to use "NmsObjDta2Import" without any preceding info about where that named range is, then you must be in spreadsheet with tab name "MainFileNameObjects" If you want to be sure to access this named range from anywhere, you should use "='G:\Excel0202015Jan2016\ExcelFox\Blogs\[StoredNamedRangeNameObjects.xls]MainFileNameObjects'!NmsObjDta2Import" Note: The refered to range is in worksheet "Tabelle1" Note also: The refered to range is in File "MasturFile.xlsm"
Reply With Quote
Bookmarks