Link to workbook with changing name

thewesties

Board Regular
Joined
Apr 18, 2002
Messages
75
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top