M
Mike
I am working with a project that consist of multiple workbooks, one wb
is the main program, the second wb is an inventory of items and the
remaining are calculation workbooks. The main program opens the
inventory and calculations sheets based on user actions.
The inventory workbook, "c:\Inventory 050107.xls", contains named
ranges with inventory of items and their price. the name property for
this file is "Company_x_Inventory". for example there is a named
range rngBoltTypes and rngBoltPrices. these ranges are dynamic and
will change in count. the calculation workbook will need to lookup
the price of a bolt based on the type.
the code and spreadsheet refers to named ranges in the inventory
workbook. For example, the calculation workbook has the following in
a cell formula:
=LOOKUP($a$1,'c:\Inventory 050107.xls'!rngBoltTypes,'c:\Inventory
050107.xls'!rngBoltPrices)
I added the inventory workbook as a reference in the calculation
workbook, to allow the use of the named ranges. So far everything
works well.
The issue that I am encountering is the client wants to be able to
store and use historical inventory files. For example. there is an
inventory file "c:\Inventory 020107.xls" and "c:\Inventory
050107.xls". Both contain the same range names and structure. Through
code, the variable strInventoryToUse is set to the appropriate
inventory file path & name
Through the code in the main program, the reference to the inventory
in VBA will added during run time
ActiveWorkbook.VBProject.References.AddFromFile
strInventoryToUse
and removed with the BeforeClose event
ActiveWorkbook.VBProject.References.Remove strInventoryToUse
First Question. is changing the reference a save practice? what is
the risk?
Second question and more important. even though the reference is
changed, the lookup formula still is looking at 'c:\Inventory
050107.xls'!rngBoltTypes. Is there a way to refer to the named ranges
in the referenced file instead of the file name? For example:
=LOOKUP($a$1,'Company_x_Inventory'!
rngBoltTypes,'Company_x_Inventory'!rngBoltPrices)
Or automatically update the links from the old inventory file to the
file specified by the code?
is the main program, the second wb is an inventory of items and the
remaining are calculation workbooks. The main program opens the
inventory and calculations sheets based on user actions.
The inventory workbook, "c:\Inventory 050107.xls", contains named
ranges with inventory of items and their price. the name property for
this file is "Company_x_Inventory". for example there is a named
range rngBoltTypes and rngBoltPrices. these ranges are dynamic and
will change in count. the calculation workbook will need to lookup
the price of a bolt based on the type.
the code and spreadsheet refers to named ranges in the inventory
workbook. For example, the calculation workbook has the following in
a cell formula:
=LOOKUP($a$1,'c:\Inventory 050107.xls'!rngBoltTypes,'c:\Inventory
050107.xls'!rngBoltPrices)
I added the inventory workbook as a reference in the calculation
workbook, to allow the use of the named ranges. So far everything
works well.
The issue that I am encountering is the client wants to be able to
store and use historical inventory files. For example. there is an
inventory file "c:\Inventory 020107.xls" and "c:\Inventory
050107.xls". Both contain the same range names and structure. Through
code, the variable strInventoryToUse is set to the appropriate
inventory file path & name
Through the code in the main program, the reference to the inventory
in VBA will added during run time
ActiveWorkbook.VBProject.References.AddFromFile
strInventoryToUse
and removed with the BeforeClose event
ActiveWorkbook.VBProject.References.Remove strInventoryToUse
First Question. is changing the reference a save practice? what is
the risk?
Second question and more important. even though the reference is
changed, the lookup formula still is looking at 'c:\Inventory
050107.xls'!rngBoltTypes. Is there a way to refer to the named ranges
in the referenced file instead of the file name? For example:
=LOOKUP($a$1,'Company_x_Inventory'!
rngBoltTypes,'Company_x_Inventory'!rngBoltPrices)
Or automatically update the links from the old inventory file to the
file specified by the code?