Workbook Open Change Links

[brumby]

Hi There,

I have a complex workbook which is shared. To work round the Macros / VBA in a shared workbook, I have used a hyperlink to open another set of workbooks to allow my macros to run.

Is it possible if I can have the following -

example. This week my shared workbook is called "Wk_32_Airbag.xlsx" I click hyperlink in the "planning" worksheet which opens a workbook called "SAP_BOM_External"

I have already linked SAP_BOM_EXTERNAL worksheet "planning" to my main sheet, so this week ok.

Next week I use new workbook called "Wk_33_Airbag.xlsx" worksheet planning to open "SAP_BOM_EXTERNAL", however as I have opened it from a new week, i would like the file opened from the hyperlink to now read Wk_33_Airbag.xlsx

The VBA will have to sit in the SAP_BOM_EXTERNAL workbook I guess with an 'upon open" command???

Many Thanks

 

[steve the fish]

Im not sure where you are going to get the week number from as it isnt clear. Maybe this macro could help? This type of macro can alter the file name based on a cell value and if the file then exists in the specified location it assigns a variable to it and opens it.

Dim myWeek As String, DirFile As String, wb As Workbook

myWeek = Trim(Sheets("Sheet1").Range("A1").Value)
If Len(myWeek) = 0 Then Exit Sub

DirFile = "C:\Users\Steve\Desktop\Wk_" & myWeek & "_Airbag.xlsx"
If Len(Dir(DirFile)) = 0 Then
    MsgBox "File does not exist"
    Exit Sub
Else
    Set wb = Workbooks.Open(DirFile)
End If