Links to closed sheets not updating

vbacoder

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi everyone,

I have some links to cells in closed worksheets that do not seem to update to the value in the source cell unless the source worksheet is open.

When I open my workbook, I get a message telling me that some of the links could not be updated, and I have the option to edit links or continue. Upon editing links, when I enter refresh, nothing changes. The values are still not pulled into the workbook from the closed sheets.

The only way to get values in seems to be to open the source worksheets, which defeats the object of the exercise.

The source worksheets are located on a networked drive, so the network may be part of the problem. I have permissions to read and modify the files.

Please could anyone help me fgure out a possible cause for the problem I'm having?

Thank you.

vcoder
 
Sort by date Sort by votes
Looking at the following link:

http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/

...it appears that there are a number of work-arounds for the use of INDIRECT to access data in closed workbooks. Of the examples, the MOREFUNC.XLL add-in (with the INDIRECT.EXT statement) looks like the best option. However, the main problem of this is that the workbook will not work properly if used on a computer that does not have this add-in installed. This is not very good.

I'm thinking of writing a function that can do something similar to the INDIRECT.EXT function in the MOREFUNC add-in. Does anyone know INDIRECT.EXT is written? Do you think it is actually opening source file, copying the data and inserting this into the target sheet?

Any help would be much appreciated.

vcoder
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Not sure. My preference is open the external references as hidden workbooks and read-only. You'll won't even know they are there and get faster performance to boot.
 
Last edited:
Upvote 0
Hi Xenou,

Thanks for your reply. I'm not sure how to open the external references as hidden workbooks. Is this similar to opening the workbook without updating the screen, as may sometimes be done in a macro?

vcoder
 
Upvote 0
Yes and no. Turning off screen updating keeps the opening of workbooks itself is more invisible to the user. But of course you can't leave Excel that way. Hidden workbooks are simply hidden - just like a hidden sheet, but for the whole workbook.

This is not fully tested but I've assembled some code that I use and you can try it out as a solution - put all the subs in the ThisWorkbook module, and then call the OpenMyLinks Sub in your Workbook_Open event. The hidden workbooks will stay hidden until you close excel or take some explicit action to close them. If you really forget and try to open one of these you'll probably get a message asking if you want to re-open the workbook. You should probably then say yes as your "hidden" copy is read-only (unless you alter the arguments to always open writable workbooks).

Code: 
[COLOR="SeaGreen"]'******************************************************************************[/COLOR]
[COLOR="SeaGreen"]'//These routines should all be placed in the ThisWorkbook module and[/COLOR]
[COLOR="SeaGreen"]'//    a call made to sub OpenMyLinks from your Workbook_Open event procedure[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] OpenMyLinks(ByRef wb [COLOR="Navy"]As[/COLOR] Workbook, _
    [COLOR="Navy"]Optional[/COLOR] blnReadOnly [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR] = True, [COLOR="Navy"]Optional[/COLOR] blnHide [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR] = True)
    [COLOR="SeaGreen"]'Open linked sources as hidden workbooks[/COLOR]
    [COLOR="SeaGreen"]'If linked source is already open, no action taken[/COLOR]
    
    [COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR] [COLOR="SeaGreen"]'//Array of links as filepaths[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] strWbName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] myCalc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    
        a = wb.LinkSources(xlExcelLinks)
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] IsEmpty(a) [COLOR="Navy"]Then[/COLOR]
            Application.DisplayAlerts = False
            Application.ScreenUpdating = False
            myCalc = Application.Calculation
            Application.Calculation = xlCalculationManual
            [COLOR="Navy"]For[/COLOR] i = LBound(a) [COLOR="Navy"]To[/COLOR] UBound(a)
                [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] WorkbookIsOpen(CStr(a(i))) [COLOR="Navy"]Then[/COLOR]
                    wb.OpenLinks a(i), ReadOnly:=blnReadOnly
                    [COLOR="Navy"]If[/COLOR] blnHide [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]Call[/COLOR] HideWb(a(i))
                    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]Next[/COLOR] i
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.Calculation = myCalc
        
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'--------------------------------------[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] HideWb(ByVal arg [COLOR="Navy"]As[/COLOR] String)
    arg = StrReverse(Left(StrReverse(arg), InStr(1, StrReverse(arg), "\") - 1))
    Windows(arg).Visible = False
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'--------------------------------------[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] WorkbookIsOpen(strWbNameOrWbFullName [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    s = Workbooks(Parse_FileName(strWbNameOrWbFullName)).Name
    [COLOR="Navy"]If[/COLOR] Err [COLOR="Navy"]Then[/COLOR]
        WorkbookIsOpen = False
    [COLOR="Navy"]Else[/COLOR]
        WorkbookIsOpen = True
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]
[COLOR="SeaGreen"]'--------------------------------------[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] Parse_FileName(arg [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]If[/COLOR] InStr(1, arg, "\", vbBinaryCompare) > 0 [COLOR="Navy"]Then[/COLOR]
        Parse_FileName = _
            StrReverse(Left(StrReverse(arg), InStr(1, StrReverse(arg), "\") - 1))
    [COLOR="Navy"]Else[/COLOR]
        Parse_FileName = arg
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]
[COLOR="SeaGreen"]'******************************************************************************[/COLOR]
 
Upvote 0
Okay - my apologies. I guess in my previous use of my own code I've always handled the hiding of windows in the external workbooks (so adding the code to do that here was a mess).

I've been testing this out and found that the app doesn't seem to know about the new windows from within the ThisWorkbook module. I guess ThisWorkbook only knows about itself. So the code to hide the workbooks must go in a standard module - and we can send it the array of links.

Here's the revisions (a little rusty still I think but at least it seems to work). I lost the distinction b/n the already-open and newly-opened workbooks in the process so I'll take some more time with this this week. (anyone have some ideas?). Also, you'd probably want to comment out the error handling while testing this for your situation - it's not helpful to not know about errors sometimes.

CODE IN STANDARD MODULE
Code: 
[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] MyWindowsHide(ByVal a [COLOR="Navy"]As[/COLOR] Variant)
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] IsEmpty(a) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] i = LBound(a) [COLOR="Navy"]To[/COLOR] UBound(a)
            s = StrReverse(Left(StrReverse(a(i)), InStr(1, StrReverse(a(i)), "\") - 1))
            Windows(s).Visible = False
            Workbooks(s).Saved = True
        [COLOR="Navy"]Next[/COLOR] i
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

CODE IN THISWORKBOOK
Code: 
[COLOR="Navy"]Option Explicit[/COLOR]
[COLOR="Navy"]Private[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] Workbook_Open()
    [COLOR="Navy"]Call[/COLOR] OpenMyLinks(ThisWorkbook)
    [COLOR="Navy"]Call[/COLOR] MyWindowsHide(a) [COLOR="SeaGreen"]'//Comment this out if you prefer windows not be hidden.[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="SeaGreen"]'******************************************************************************[/COLOR]
[COLOR="SeaGreen"]'//These routines should all be placed in the ThisWorkbook module and[/COLOR]
[COLOR="SeaGreen"]'//    a call made to sub OpenMyLinks from your Workbook_Open event procedure[/COLOR]

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] OpenMyLinks(ByRef wb [COLOR="Navy"]As[/COLOR] Workbook, _
    [COLOR="Navy"]Optional[/COLOR] blnReadOnly [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR] = True)
    [COLOR="SeaGreen"]'Open linked sources as hidden workbooks[/COLOR]
    [COLOR="SeaGreen"]'If linked source is already open, no action taken[/COLOR]
    
    [COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] strWbName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] myCalc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] sTemp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    
        a = wb.LinkSources(xlExcelLinks)
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] IsEmpty(a) [COLOR="Navy"]Then[/COLOR]
            Application.DisplayAlerts = False
            Application.ScreenUpdating = False
            myCalc = Application.Calculation
            Application.Calculation = xlCalculationManual
            [COLOR="Navy"]For[/COLOR] i = LBound(a) [COLOR="Navy"]To[/COLOR] UBound(a)
                [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] WorkbookIsOpen(CStr(a(i))) [COLOR="Navy"]Then[/COLOR]
                    wb.OpenLinks a(i), ReadOnly:=blnReadOnly
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]Next[/COLOR] i
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.Calculation = myCalc
        
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'--------------------------------------[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] WorkbookIsOpen(strWbNameOrWbFullName [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    s = Workbooks(Parse_FileName(strWbNameOrWbFullName)).Name
    [COLOR="Navy"]If[/COLOR] Err [COLOR="Navy"]Then[/COLOR]
        WorkbookIsOpen = False
    [COLOR="Navy"]Else[/COLOR]
        WorkbookIsOpen = True
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

[COLOR="SeaGreen"]'--------------------------------------[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Function[/COLOR] Parse_FileName(arg [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]If[/COLOR] InStr(1, arg, "\", vbBinaryCompare) > 0 [COLOR="Navy"]Then[/COLOR]
        Parse_FileName = _
            StrReverse(Left(StrReverse(arg), InStr(1, StrReverse(arg), "\") - 1))
    [COLOR="Navy"]Else[/COLOR]
        Parse_FileName = arg
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]
[COLOR="SeaGreen"]'******************************************************************************[/COLOR]
 
Last edited:
Upvote 0
Hi Xenou,

Many thanks for your recent replies and accompanying code.

I'm going to have a look at it to see how I can construct a user Function that carries out a similar set of actions that I can call within the worksheet. The main thing that I'm concerned about is the syntax. Your examples are well structured, so I will use this as a guide.

Thank you.

vcoder
 
Upvote 0
Good. If you have anything to add feel free. I think I'll do some tweaking/testing too, this week. Note that when hiding the windows I set the saved bit to true because "hiding" will itself prompt the user for a save. Or, to put it another way, Excel wants to know if you want the workbook to actually be saved as a hidden workbook - which you probably don't want unless this workbook is intended to permanently be hidden no matter how it is opened. I also used a lot of copious error-handling probably because of the constant danger of bad links - though, as a result, the routine itself won't alert you to any bad links (and that might be a bug of sorts).
 
Upvote 0
I found this code from another forum.

It attempts do pull in data from a closed workbook using the Excel4Macro statement within a User Function, but it doesn't work:

Code: 
Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function
 
Last edited:
Upvote 0
ok,

I've tracked down the original version of the above code, found at the following url: http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Code: 
Public Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
    Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)

End Function

The problem is that the function doesn't seem to work when the components of the string are entered into various cells that are passed as arguments in the function call in the cell. The result to =GetValue(p,f,s,a) is "#Value"

It works when called from a separate sub as follows:

Code: 
Sub TestGetValue()

'activating the sheet
    Sheet1.Select

    p = Range("A1").Value
    f = Range("A2").Value
    s = Range("A3").Value
    a = Range("A4").Value
    
    Range("A5").Value = GetValue(p, f, s, a)

    MsgBox GetValue(p, f, s, a)
End Sub

or...

Code: 
Sub TestGetValue()
    p = "c:\XLFiles"
    f = "Budget.xls"
    s = "Sheet1"
    a = "A1"
    MsgBox GetValue(p, f, s, a)
End Sub

Please could anyone help me to see why the function does not work directly, but only works when called by a separate sub?

Thank you.

vcoder
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA: Open and close all source files to refresh Master Workbook data
Replies
1
Views
459
t0ny84
T
Sean15
  • Question
Struggling with updating links from a closed worksheet.
Replies
3
Views
728
Fluff
Fluff
spacecaptainsuperguy
  • Question
links not updating!
Replies
0
Views
554
spacecaptainsuperguy
spacecaptainsuperguy
L
  • Question
Getting Error : UNABLE TO REFRESH We couldn't get updated values from a linked workbook
Replies
2
Views
1K
luckyros
L
J
  • Question
VBA Script - Can't figure out one error...
2
Replies
15
Views
236
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,932
Messages
6,122,332
Members
449,077
Latest member
jmsotelo

Share this page

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