Links to Other Workbooks - Updating / Refreshing - INDIRECT vs Hardcoded

[Jeff Meyers]

I have an issue with links to other workbooks updating (or actually not updating).

I am using Excel 2010 on a PC with WIN 7.

Department managers complete financial projection files (workbooks). I have prepared a summary file (workbook) that will pull data from each of the departmental files. These departmental financial projection files will be updated monthly (with new files names) so I set-up my summary workbook to link to the other workbooks using INDIRECT() functions (along with INDEX() and MATCH()) as I would like the links to be dynamic.

The links work great as long as the workbooks I am linking to (using the INDIRECT() function) are opened. But if they are not opened then all of my INDIRECT() function links return a #REF! error.

If I were to "hardcode" the links:

='[filename.xlsm]worksheet name'!cell ref
​

in my summary file (workbook) then I am given the option to update links when I open the file. Even then the INDIRECT() function links are not updated.

Note: In both situations all files (workbooks) are located in the same folder.

In summary:

• INDIRECT() function links will not update unless the linked file is open
• "Hardcode" links will update (option presented) even when the linked file is NOT open

Both are links, yet they are updated in different ways. Is this correct? What am I missing? Any suggestions to correct this? By correct this I mean that I would like to open my summary file (workbook) and have any and all links updated / refreshed without having to open additional files (workbooks).

Thanks!

 

[Smitty]

You can use INDIRECT.EXT, which is part of the More-Func add-in.

HTH,

 

[BenMiller]

Excel is programmed to only recognize the INDIRECT function when the referenced sheet is open. Sadly, there is no way around it.






*pause for sadness*




But! There is good news. The amazing, amazing people right here have created a free, essential add-in for Excel, called Morefunc. One of the 66 new functions they give you is INDIRECT.EXE which addresses the exact problem you are experiencing. It will behave just like the regular INDIRECT function, but it will also work when the referenced sheet is not open.

Good Luck!

 

[Jeff Meyers]

Definite sadness. And surprise at that limitation (in my opinion) within the INDIRECT() function.

I appreciate the heads-up on the More-Func add-in, but I have a couple of issues with it:

1. I do not have administrator rights to install it on my work PC
2. probably more importantly - I will not be the only one to use this file (workbook) so that would entail getting other users to install the add-in (and they have the same #1 issue above)

At this point it appears that instructions for this summary workbook will include the directions to open the other referenced workbooks.

At least it's a step up from the old methodology (not mine): "To update the summary report we just enter the amounts from the departmental files, or sometimes we copy and paste the amounts."

Thank you for the info and direction Smitty and Ben. Much appreciated.

 

[Smitty]

To make things a bit easier for users, you can add some code to the Workbook_Open/Close events that will automatically open/close the dependent workbooks when the master is opened.

 

[BenMiller]

I'm overcome with sadness

At my old work, I had everyone install the add-in!

 

[Jeff Meyers]

I'm overcome with sadness

At my old work, I had everyone install the add-in!

Unfortunately I don't have that luxury / authority. Would be nice though.

To make things a bit easier for users, you can add some code to the Workbook_Open/Close events that will automatically open/close the dependent workbooks when the master is opened.

I like that approach. I'm think I'm going to experiment with that today. The only thing that the summary file (workbook) is used for is to view and / or print.

So basically I would need to set an event on opening the summary file to supress screen updating, then open the departmental files (workbooks) - at which point my INDIRECT() function links would update, then close the departmental files, and then enable screen updating. My coding skills are definitely lacking so look for me to start a new thread with some questions on that aspect.

Thanks again guys!

 

[Smitty]

If you record a macro opening, then closing the workbooks in question, you'll have the correct file paths/syntax, which you can then cut/paste into the Open/Close events.

Feel free to post what you come up with so someone can check it for you.

 

[Jeff Meyers]

I did end up using your suggestion - starting with the Macro recorder, testing and tweaking, recording some more, getting guidance from MrExcel.com, more testing and tweaking, and this is what I ended up with.

Someone from here (in another thread) suggested that I test to see if the referenced workbooks exist or are already open (and they even provided code for it). I didn't go that route as I look for those workbooks to be completed before opening this summary.


In addition, I coded it to run with 2 options:

• the first opens the referenced workbooks so that the INDIRECT() functions update, then it pastes all of the amounts as values, copies and saves it as a separate workbook (that can be distributed to others), and then closes all of the other workbooks (and does not save the updated Summary file)
• the second opens the refenced workbooks so that the INDIRECT() functions update and then allows me to do any editing on the Summary file

Anyways, here is the code in case anyone wants to take a look at it:

Private Sub Workbook_Open()
'
    Dim msgResponse As VbMsgBoxResult
'
    msgResponse = MsgBox("Do you want to view / print the Projections Summary?" _
        & vbCrLf & vbCrLf & _
        "Clicking 'NO' will allow editing of this workbook." _
        , vbQuestion + vbYesNo)
    If msgResponse = vbYes Then
        JustViewPrint
    ElseIf msgResponse = vbNo Then
        EditTheFile
    End If
'
End Sub

Option Explicit
Sub JustViewPrint()
'
    JVPStartInfo
    OpenGroupFiles
    ConvertToValues
    CopyAndSave
    CloseGroupFiles
    JVPEndInfo
'
End Sub
Sub EditTheFile()
'
    Application.ScreenUpdating = False
    OpenGroupFiles
    Application.ScreenUpdating = True
'
    MsgBox "You may now proceed with editing."
'
End Sub
Sub JVPStartInfo()
'
    MsgBox "Note: There will be a delay while the report is updated." & vbCrLf & _
           "A notification will advise you when this process is completed." & vbCrLf & _
           "Click OK to begin the process."
'
    Application.ScreenUpdating = False
'
End Sub
Sub JVPEndInfo()
'
    Application.ScreenUpdating = True
'
    MsgBox "Report update process completed!"
'
    ActiveWorkbook.Close False
'
End Sub
Sub OpenGroupFiles()
'
    Dim intCounter As Integer
    Dim arrFileNames As Variant
    Dim wbSummary As Workbook
'
    arrFileNames = Array("B10", "B13", "B16", "B19", "B22")
    Set wbSummary = ActiveWorkbook
'
    Application.Calculation = xlCalculationManual
'
    For intCounter = LBound(arrFileNames) To UBound(arrFileNames)
        Workbooks.Open Filename:=Sheets("MAIL").Range("B5").Value & "\" & _
            Sheets("MAIL").Range(arrFileNames(intCounter)).Value
        Windows(wbSummary.Name).Activate
    Next intCounter
'
    Application.Calculation = xlCalculationAutomatic
'
End Sub
Sub ConvertToValues()
'
    Dim wbSummary As Workbook
'
    Set wbSummary = ActiveWorkbook
'
    Windows(wbSummary.Name).Activate
    Sheets("SSP Summary").Select
'
    ActiveSheet.Unprotect
    Range("RNG_3B") = Range("RNG_3B").Value
    Range("RNG_3D") = Range("RNG_3D").Value
    Range("RNG_3E") = Range("RNG_3E").Value
    Range("RNG_COUNTY") = Range("RNG_COUNTY").Value
    Range("RNG_MISC") = Range("RNG_MISC").Value
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'
End Sub
Sub CopyAndSave()
'
    Dim wbSummary As Workbook
    Dim wbSumValues As Workbook
    Dim strFileName As String
    Dim strFileNameTrim As String
'
    Set wbSummary = ActiveWorkbook
    strFileName = wbSummary.Sheets("MAIL").Range("B6").Value
    strFileNameTrim = Left(strFileName, Len(strFileName) - 5)
'
    Worksheets("SSP Summary").Copy
    Set wbSumValues = ActiveWorkbook
    wbSumValues.SaveAs Filename:= _
        wbSummary.Sheets("MAIL").Range("B5").Value & "\" & _
        strFileNameTrim & " - VALUES - " & Format(Now, "yyyymmdd-hhmm") & _
        ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'
    Windows(wbSummary.Name).Activate
'
End Sub
Sub CloseGroupFiles()
'
    Dim intCounter As Integer
    Dim arrFileNames As Variant
    Dim wbSummary As Workbook
    Dim wbToClose As Workbook
'
    arrFileNames = Array("B10", "B13", "B16", "B19", "B22")
    Set wbSummary = ActiveWorkbook
'
    For intCounter = LBound(arrFileNames) To UBound(arrFileNames)
        Set wbToClose = Workbooks(Sheets("MAIL").Range(arrFileNames(intCounter)).Value)
        Windows(wbToClose.Name).Activate
        ActiveWorkbook.Close False
        Windows(wbSummary.Name).Activate
    Next intCounter
'
End Sub

I'm really pleased with the way it turned out.

A big thanks to everyone that assisted and offered guidance. It is all much appreciated!

 

[jsek29]

I am new to VBA and macros, and I do not fully understand the solution. I want to do the same thing, and can see that this will work. I need clarification on the OpenGroupFiles sub. I see how to define the file name using arrFileNames, but I don't understand how to put the pathway in. I can see that you have coded it, but don't understand the following snippit of code:

Workbooks.Open Filename:=Sheets("MAIL").Range("B5").Value & "\" & _
Sheets("MAIL").Range(arrFileNames(intCounter)).Value
Windows(wbSummary.Name).Activate


What is "MAIL" and what is in cell B5?

Thanks for any help.