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]

Thanks Andrew
I will work on it during today, and if you don’t mind I will post later

 

[Bagsy]

Andrew
I have been working on this job sheet & trying to figure out what the problem is, I deleted all the links I had & then created new links to a price book
='G:\09. Price Books\[Drill pipe price book (Linked).xlsm]Sheet1'!G6
I am getting the same problem saying that the workbook contains links that cannot be updated and in the link list it is showing the Price book. The thing is I have changed a few things & the links are actually updating OK.
It is as if I am getting a link error on anything I set up, this was working Ok a few days ago but something seem to have changed. I have checked in advanced settings and the “update links to other documents” is checked.

 

[Andrew Poulsom]

I wasn't able to recreate you problem, but now you have a different setup. Do you want to put your workbooks on a share like Box.com so that I can take a look? Obviously I won't be able to use your drive setup.

 

[Bagsy]

Can I use drop box Andrew

 

[Andrew Poulsom]

Yes that's fine.

 

[Andrew Poulsom]

I downloaded your workbooks. The links in '_Job book.xlsm' are to 'Drill pipe price book (Linked).xls' but you uploaded 'Drill pipe price book (Linked).xlsm'. Maybe that's the cause of the problem.

 

[Bagsy]

Apologies Andrew
I was in the process of changing the links over & sent the wrong file, I have uploaded the correct file, the problem still exists

 

[Andrew Poulsom]

I don't get the message if I put 'Drill pipe price book (Linked).xlsm' in the same folder as '_Job book.xlsm'. I do see the Edit Links dialog but I expect that's in your Workbook_Open event procedure.

 

[Bagsy]

Andrew
The on open event should just delete the links to the Job Book, this is so once the job sheet has been created the links will not update again, to put them back just press the reset button & Save
When you re open the job book you will get the update links dialog box. This was perfectly OK a few days ago and nothing was changed until we developed this current problem.
If the on open event is what is causing the update links dialog box to open each time, is there a line of code I can use to prevent it.

Many thanks
Bagsy

 

[Andrew Poulsom]

I don't know because your VBA project is locked. As I said I don't get the message that links can't be updated.