Link to workbook with changing name

[thewesties]

I need to create a link to an excel workbook on my network whose name is changing weekly.
The naming convention of the file is based on the previous week ending date (ie last Friday was 4/12/02 so the name of the file is WE041202.xls.)
The physical path to the file is constant on the network. I have concatenated several cells to create the formula as follows:

Cell I12: ='Spreadsheet Manager'!$H$1 (this is a worksheet in this workbook)
Cell I13: =IF(MONTH(I12)<10,CONCATENATE(0,MONTH(I12)),MONTH(I12))
Cell I14: =IF(DAY(I12)<10,CONCATENATE(0,DAY(I12)),DAY(I12))
Cell I15: ==RIGHT(YEAR(I12),2)
Cell I16: =CONCATENATE("='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE",I13,I14,I15,".xls]Prod Weekly'!$I$6")

Based on the above formulas, this is how the data is displayed:
04/12/02 (<--Cell I12)
04 (<--Cell I13)
12 (<--Cell I14)
02 (<--Cell I15)
='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE041202.xls]Prod Weekly'!$I$6 (<--Cell I16)

I would like CELL I17 to use the value of Cell I16 as the basis for the formula.

The source for I12 (in the current Worksheet 'ASAP #'!I12) is changed weekly.
The reason I don't just manually change the link is the 'Spreadsheet Manager' worksheet links to ASAP # as well as 71 other worksheets in this workbook.


My ultimate goal is to take my result (which is shown in Cell I16) and convert it from text to the actual formula.
As it is right now, if I Copy and Paste Value of I16 in to I17 I get the following:
='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE041202.xls]Prod Weekly'!$I$6
I have tried creating macros to edit the cell, but then the data would be hard coded in the macro to always type WE041202.


[ This Message was edited by: thewesties on 2002-04-19 17:46 ]

[ This Message was edited by: thewesties on 2002-04-22 09:04 ]

[ This Message was edited by: thewesties on 2002-04-22 12:43 ]

[thewesties]

After a seriously distracted weekend, and a miserable morning, I have come up with a resolution myself. If anyone needs it, here it is.

I went in to the sheet where I had to create the concatenated formula and created an Worksheet_Activate sub as follows:

Private Sub Worksheet_Activate()
Dim PartA As String
Dim PartB As String
Dim UploadLink As String
Dim UploadValue As String

PartA = "='G:USPCCmaCard_Fee_ControlEXPROARDailyProd[WE"

Range("I16").Select
PartB = ActiveCell.Value

UploadValue = ".XLS]Prod Weekly'!$I$6"
UploadLink = PartA & PartB & UploadValue
Range("I17").Select
ActiveCell.Formula = UploadLink

Range("A1").Select

End Sub

Each time the worksheet was opened, it worked like a charm. I then used it for every other link I needed on that workbook with no problems.