Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Link to workbook with changing name

  1. #1
    Board Regular thewesties's Avatar
    Join Date
    Apr 2002
    Location
    Superfund, NJ
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular thewesties's Avatar
    Join Date
    Apr 2002
    Location
    Superfund, NJ
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •