excel - VLookups Don't Update When Source Workbook is Opened via VBA

Question

I have a main workbook that does calculation on data that is sourced from a separate workbook. I created a button on the main workbook that opens the source workbook for user convenience (that way they don't have to go digging through directories to find the source file).

I assigned the following macro to the button to open the file:

Sub OpenIRTSumReport()
'
' Open the IRT Sum Report as given by the file path in the "START" tab
'
    Dim dir As String
    Dim fileName As String
    dir = Worksheets("START").Range("$C$3").value
    fileName = Worksheets("START").Range("$C$4").value
    
    If dir <> "" And fileName <> "" Then
        Dim path As String
        path = Chr(34) & dir & "" & fileName & Chr(34)
        
        Workbooks.Open path
        
        ThisWorkbook.Activate
    End If
End Sub

My problem: the links in the main workbook still show a #REF! error even after pressing the button to open the workbook. If I open the source workbook manually, the links update fine, but using the VBA macro doesn't update the links, despite the workbook being opened.

I've tried pressing the refresh all button, I've made sure the links and formulas are set to update automatically, I've even tried retyping the formulas in the cell and it still doesn't up.

I believe the #REF! error is occurring in the INDIRECT function within my main workbook links, but it could be the VLOOKUP, I'm not totally sure. Just very lost at the moment.

EDIT: A little more trouble shooting; problem definitely seems to lie in the INDIRECT function. I can retype a VLOOKUP into a different cell using a hard coded reference instead of the INDIRECT and the VLOOKUP works fine.

Cheers, Matthew Mehrtens

question from:https://stackoverflow.com/questions/65598808/vlookups-dont-update-when-source-workbook-is-opened-via-vba

Answer 1

Waitting for answers