Updating links

[Bagsy]

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

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

 

[Bagsy]

Andrew
My apologies I have locked unlocked some many times in the last few days
I have Sent a message with the password

 

[Andrew Poulsom]

There is no Workbook_Open code in the workbook. I'm not getting the 'Edit Links' dialog now.

 

[Bagsy]

Andrew
My apologies I have locked unlocked some many times in the last few days
I have Sent a message with the password

 

[Bagsy]

Andrew I don’t understand
In the work book, Job sheet in “this workbook” module there is code beginning
Private Sub Workbook_Open()
Are you saying this is not in the copy you downloaded, I have checked the drop box folder and the job sheet in there has the Private Sub Workbook_Open() code
This is really strange I am still getting the edit links dialog box

 

[Bagsy]

Andrew
It it something that is peculiar to the job sheet, I have carried out the following
1. Commented out Workbook_Open() code
2. Deleted all other sheets apart from the data validation sheet & DP & BHA Inspection sheet
3. When I open the job sheet I still get the edit links dialog box for both links
I then deleted the data validation sheet and opened the job sheet and just got the edit links dialog box looking for the job book.
I then deleted the four links on the DP & BHA Inspection sheet and opened the job sheet which as you would expect opened without a problem
I then carried out as below
1. copied the links to a new test worksheet it opened up without any problem
2. I then copied a link back from the test worksheet and again I got the edit links dialog box back

 

[Andrew Poulsom]

I only see a Workbook_BeforeSave event procedure.

 

[Bagsy]

OK Thanks Andrew
We are at crossed purposes here, my apologies. Sequence of events for us to create a Job sheet
We open the Job Book type in as required and save
We then open up the job sheet (this is the one with the Workbook_Open() code) and this is the workbook that is giving me these edit links dialog box problem.

The Job book is linked to the customer name & rig name lists, on Price book
This is opening up and updating links correctly (this has the Workbook_BeforeSave event procedure)

The job Sheet is the one that is causing me a problem, this is linked to the customer name & rig name lists, on price book
And the last entries on the Job Book which I am now using the Index formulas you suggested.
The edit links dialog box comes up for both linked sheets
If I delete the validation sheet I get the edit links dialog box just for the Job Book
If I delete the links to the job book I get edit links dialog box just for the drill pipe price book

If I copy the index formula you suggested from the Job Sheet to a new clean workbook save & shut down, when I re open the new workbook links perfectly, no edit links dialog box comes up

If I copy a link to Drill pipe price book for the customer name & rig name lists from the Job Sheet to a new clean workbook save & shut down, when I re open the new workbook links perfectly no edit links dialog box comes up

The links are updating OK on the job book, its just that I am getting a edit links dialog box saying there is a problem, when there doesn’t appear to be the case.
This edit links dialog box problem just seemed to have manifested in the last week, when there have been no modifications made.

I have other workbooks that are linked to the customer & rig list on the drill pipe price book & these are all fine.
Many thanks for your time on this

 

[Andrew Poulsom]

In '_Job Sheet.xltm you have a couple of names that refer to an external workbook. For me:

Conn_Validation ='C:\02. Stock Lists\Current Stock lists\[Noble Drilling - Julie Robertson.xls]Validation List'!$A$1:$A$150
Insp_Validation ='C:\02. Stock Lists\Current Stock lists\[Noble Drilling - Julie Robertson.xls]Validation List'!$C$1:$C$31

If I delete them, save and close, I don't get the message about links when I reopen the workbook.

 

[Bagsy]

Thanks Andrew
That it, I must of accidently created these names when I created the job sheet, its only just become a problem because I have just converted the stock lists to Xlsm. I don’t think I would have ever found this without your help, it never occurred to me that is was a name that was causing this problem.
I am very grateful, this was so frustrating many thanks for your time & effort
Gary