Updating links

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
467
Office Version
  1. 365
Platform
  1. Windows
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



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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
That's because you are using INDIRECT, which requires that the workbook is open. Can't you open _Job book.xlsm before you open your workbook?
 
Upvote 0
With _Job book.xlsm open try this formula:

=INDEX('[_Job book.xlsm]Sheet1'!C:K,MATCH(REPT("Z",90),'[_Job book.xlsm]Sheet1'!C:C),9)
 
Upvote 0
I thought the workbook open event did just that
Anyway I have tried what you suggested and it does not seem to make any difference
In fact I don’t think this was down to the upgrade, I have been told it has only just occurred, its like the file no longer exists but it is updating OK
 
Upvote 0
Sorry Andrew
I have just seen your 2nd reply, I will try that, Many thanks
 
Upvote 0
Andrew
Thanks for all your efforts, I Am having real trouble with this, not sure what the problem is, it may take a while to sort out.
I will post later if you don’t mind
Is this formula below correct
=INDEX('[_Job book.xlsm]Sheet1'!A:C,MATCH(REPT("Z",90),'[_Job book.xlsm]Sheet1'!C:C),2)
This is working but I am not sure if it is the correct way to do it.
What it should be doing is look at C:C if these is data entered then enter the information from B;B
I have to do it this way because column B:B is populated all the time with the next w/o Number.
 
Upvote 0
Andrew
I have changed all the formulas as you suggested and I still have the same problem
I have tried
commenting out the Private Sub Workbook_Open()
Opening the job book and leaving open
Then opening the Job sheet
I still get the message that the workbook contains links that cannot be updated
And they then update OK,
we seemed to have just developed this glitch in the last couple of days & not from the update as I first thought.
This is very annoying I cannot work out what is going on
 
Upvote 0
Thanks Andrew
I don’t have any REF Values when I open the job sheet I just the
message that the workbook contains links that cannot be updated although the cells have updated
As soon as I press continue everything works perfectly

I Have the following formulas in cells which populate from the job book
Customer
=INDEX('[_Job book.xlsm]Sheet1'!C:K,MATCH(REPT("Z",90),'[_Job book.xlsm]Sheet1'!C:C),7)
P/O No.
=INDEX('[_Job book.xlsm]Sheet1'!C:K,MATCH(REPT("Z",90),'[_Job book.xlsm]Sheet1'!C:C),9)
W/O No.
=INDEX('[_Job book.xlsm]Sheet1'!A:C,MATCH(REPT("Z",90),'[_Job book.xlsm]Sheet1'!C:C),2)
Rig
=INDEX('[_Job book.xlsm]Sheet1'!C:K,MATCH(REPT("Z",90),'[_Job book.xlsm]Sheet1'!C:C),8)


I will spend a bit more time on it and post later
Thanks I really appreciate your help
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top