Can anybody help with a problem I have developed since upgrading to Excel 2010
I have a work sheet with some formulas as below
=OFFSET(INDIRECT("'[_Job book.xlsm]Sheet1'!C"&MATCH(REPT("Z",90),'G:\03. Operational Data\[_Job book.xlsm]Sheet1'!C:C)),0,8)
These link to a job book & populate cells with information from the job book.
I am now getting the message when I open the work book that the workbook contains links that cannot be updated, i f I select continue the links all update fine, if I select edit links they are shown as OK.
It is as if I am getting the message before it has time to open the job book
Can anybody shed any light on this please
Thanks in advance
Bagsy
I have a work sheet with some formulas as below
=OFFSET(INDIRECT("'[_Job book.xlsm]Sheet1'!C"&MATCH(REPT("Z",90),'G:\03. Operational Data\[_Job book.xlsm]Sheet1'!C:C)),0,8)
These link to a job book & populate cells with information from the job book.
I am now getting the message when I open the work book that the workbook contains links that cannot be updated, i f I select continue the links all update fine, if I select edit links they are shown as OK.
It is as if I am getting the message before it has time to open the job book
Can anybody shed any light on this please
Thanks in advance
Bagsy
Code:
Private Sub Workbook_Open()
Dim WB As Workbook
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Set Wkbk = ActiveWorkbook
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
Sheets("DP & BHA Inspection").Select
If Range("S1") = 1 Then
CurrentSheet.Select
Exit Sub
Else
Application.ScreenUpdating = False ' turn off the screen updating
Sheets("DP & BHA Inspection").Select
ActiveSheet.Unprotect Password:="xxxx"
Set WB = Workbooks.Open("G:\03. Operational Data\_Job book.xlsm", True, True)
Wkbk.Activate
Application.Run "Replacelinks"
WB.Close False ' close the source workbook without saving any changes
Set WB = Nothing ' free memory
ActiveSheet.Protect Password:="xxxx"
ActiveSheet.Select
CurrentSheet.Select
Application.ScreenUpdating = True ' turn on the screen updating
End If
End Sub