Links stop updating with "Unable to Read File"

[gordon3d]

I am linking calls from one spreadsheet to another spreadsheet on the same server but in another folder. It worked quite well for 6 months and now it does not update. When I open the file, it comes up with "Unable to Read File" and after clicking "OK", the file opens but all links comes up with "#NA". Here is the link:

=IF(VLOOKUP($K$1,'\\w2k3fs3\shared\Lab\003. Management Tools\RGT Job Register.xls'!Job_Acc,2,FALSE)="","",VLOOKUP($K$1,'\\w2k3fs3\shared\Lab\003. Management Tools\RGT Job Register.xls'!Job_Acc,2,FALSE))

The is the source sile folder: \\w2k3fs3\shared\Lab\003. Management Tools\
This is the source file: RGT Job Register.xls

If I copy this line into another cell, the link works:
'\\w2k3fs3\shared\Lab\003. Management Tools\RGT Job Register.xls'!A7

But is I use the entire vlookup line, it fails:
VLOOKUP($K$1,'\\w2k3fs3\shared\Lab\003. Management Tools\RGT Job Register.xls'!Job_Acc,2,FALSE)

Here is the spanner. If I open the RGT Job Register file (whether as normal or as read only, all the links, including the original full line, update!

So, I cannot confirm the formula is good. Just something to do with the linking process. Something has changed. I ask our IT department but cannot locate any clues.

Anyone can help think of the possible hiccups?

 

[xenou]

Hi Gordon,

I generally go out of my way to avoid links. Yes, they can be very useful, but maintaining them sometimes is troublesome. That said, I don't avoid them completely (probably because I am lazy, and they do the job that I need them to do).

1)
In your earlier post, the stuff about Access is apparently only a way to create the bug - thanks Microsoft for showing us that.
*Edit: this is in regard to this page at MS Support: http://support.microsoft.com/kb/824172/en-us

2)
It's probably nothing but normally in links the filename is in brackets:
'\\w2k3fs3\shared\Lab\003. Management Tools\[RGT Job Register.xls]'!A7

(I'm not quite sure how you can report that the above works when not in a vlookup because it appears to lack a sheet name...but I'll step over that)

3)
Vlookups and links are the worst combination of all. If the file you are reading has become to large (the lookup table), then that will become a problem. Maybe this is why it worked fine for you for six months but doesn't now. I've occasionally moved data to an "archive" tab to keep the lookup table smaller (under 15,000 rows for sure, maybe less).

4)
Before I forget, you'd better make sure that named range (Job_Acc) hasn't been deleted or altered by someone.

5)
You can cut your processing time down by using two columns with the above formula:
A1: <LookupValue>
A2: =VLOOKUP(A1,SomeTable,2,0)
A3: =If(ISNA(A2),"",A2)
This means excel doesn't have to run two lookups to calculate the result.

5)
Probably the simplest solution here is to open the file. When I do use lookups, especially on large lookup tables, that's what I do. Mostly, it just makes everything go faster - in some cases, much faster. In a few instances, I even create the lookup on the fly with VBA, update the data, then cut the connection - so I don't have a link except at the time that I update the data (rather like pulling data from a database). This code will just automatically open the links for you. I guess it would be nice to close the file too when you are done... Most of my linked files are hidden so I don't even think about them that much.

If you can use vba, here's the code I use in my Workbook_Open event:

Private Sub Workbook_Open()
    Dim arLinks As Variant
    Dim intIndex As Integer
    arLinks = ThisWorkbook.LinkSources(xlExcelLinks)
        
        Application.DisplayAlerts = False
        On Error Resume Next
        If Not IsEmpty(arLinks) Then
            For intIndex = LBound(arLinks) To UBound(arLinks)
                ThisWorkbook.OpenLinks arLinks(intIndex)
            Next intIndex
        End If
        On Error GoTo 0
        Application.DisplayAlerts = True
        
        ThisWorkbook.Activate

End Sub

Hope this helps - as I said, links are not going to be the most enjoyable part of your Excel experience. But used carefully, and avoiding lots of lookups in other workbooks, will probably make things go more smoothly. In generally, if you do have a lot of links, I'd just go ahead and open the other workbook anyway. It can be useful to use Window | Hide from the menu to hide the other workbook so it doesn't clutter up your workspace.

Maybe someone will have a better and more direct solution. --AB

 

[gordon3d]

Thanks for that! Let me have a little digest and test out your recommendations. I think you are right about the Vlookup stalling if the source list gets too big. Here is a clue:

Fail with #N/A error: VLOOKUP($K$1,'\\w2k3fs3\shared\Lab\003. Management Tools\RGT Job Register.xls'!Job_Acc,2,FALSE)

Works good with correct result: '\\w2k3fs3\shared\Lab\003. Management Tools\[RGT Job Register.xls]Acc'!A7

You see, beside the name range "Job_Acc", there is really nothing too different between them....just failing of vlookup.

NOTE: I have checked Job_Acc is correct name as it works if both the source file (RGT Job Register) and the file with the vlookup is opened within the same instant of Excel.

Be back in a day to comment again.

 

[paun_shotts]

Hi Gordon,

I generally go out of my way to avoid links. Yes, they can be very useful, but maintaining them sometimes is troublesome. That said, I don't avoid them completely (probably because I am lazy, and they do the job that I need them to do).

1)
In your earlier post, the stuff about Access is apparently only a way to create the bug - thanks Microsoft for showing us that.
*Edit: this is in regard to this page at MS Support: http://support.microsoft.com/kb/824172/en-us

2)
It's probably nothing but normally in links the filename is in brackets:
'\\w2k3fs3\shared\Lab\003. Management Tools\[RGT Job Register.xls]'!A7

(I'm not quite sure how you can report that the above works when not in a vlookup because it appears to lack a sheet name...but I'll step over that)

3)
Vlookups and links are the worst combination of all. If the file you are reading has become to large (the lookup table), then that will become a problem. Maybe this is why it worked fine for you for six months but doesn't now. I've occasionally moved data to an "archive" tab to keep the lookup table smaller (under 15,000 rows for sure, maybe less).

4)
Before I forget, you'd better make sure that named range (Job_Acc) hasn't been deleted or altered by someone.

5)
You can cut your processing time down by using two columns with the above formula:
A1: <LookupValue>
A2: =VLOOKUP(A1,SomeTable,2,0)
A3: =If(ISNA(A2),"",A2)
This means excel doesn't have to run two lookups to calculate the result.

5)
Probably the simplest solution here is to open the file. When I do use lookups, especially on large lookup tables, that's what I do. Mostly, it just makes everything go faster - in some cases, much faster. In a few instances, I even create the lookup on the fly with VBA, update the data, then cut the connection - so I don't have a link except at the time that I update the data (rather like pulling data from a database). This code will just automatically open the links for you. I guess it would be nice to close the file too when you are done... Most of my linked files are hidden so I don't even think about them that much.

If you can use vba, here's the code I use in my Workbook_Open event:

Private Sub Workbook_Open()
    Dim arLinks As Variant
    Dim intIndex As Integer
    arLinks = ThisWorkbook.LinkSources(xlExcelLinks)
       
        Application.DisplayAlerts = False
        On Error Resume Next
        If Not IsEmpty(arLinks) Then
            For intIndex = LBound(arLinks) To UBound(arLinks)
                ThisWorkbook.OpenLinks arLinks(intIndex)
            Next intIndex
        End If
        On Error GoTo 0
        Application.DisplayAlerts = True
       
        ThisWorkbook.Activate

End Sub

Hope this helps - as I said, links are not going to be the most enjoyable part of your Excel experience. But used carefully, and avoiding lots of lookups in other workbooks, will probably make things go more smoothly. In generally, if you do have a lot of links, I'd just go ahead and open the other workbook anyway. It can be useful to use Window | Hide from the menu to hide the other workbook so it doesn't clutter up your workspace.

Maybe someone will have a better and more direct solution. --AB

Hi,
I am having the same issue.
I have used your VBA code listed above, this worked in opening the linked files, HOWEVER.. I received the error message "Unable to read file" as soon as I opened my workbook, then after pressing OK on the error message, the linked files then opened and then the values updated.
How can I remove the error message? I already have selected the option on my links to "Dont display the alert and update the links" But im still getting the alert.