Share via

Excel doesn't update links when the reference file is opened through a VBA

Anonymous
2017-06-14T14:25:04+00:00

So excel will automatically update links like it should when both the reference and main file are opened when I open the reference file normally.  However, when I open the reference file from a vba on the main file, it does not update.  It won't even update when I manually refresh the links.  I must close both files and reopen the main file for it to properly update.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144K Reputation points Volunteer Moderator
    2017-06-14T14:48:17+00:00

    That issue depends on your code, resp. the settings in the files.

    • Create FileA.xlsx with "A" in A1
    • Create FileB.xlsx with "=[FileA.xlsx]Sheet1!$A$1" in A1
    • Close all files
    • Open FileA.xlsx, write "XX" in A1, save and close the file
    • Create FileC.xlsm and add this code into a regular module and run it:

    Sub Test()

      Workbooks.Open "FileB.xlsx", True

    End Sub

    As you see FileB shows "XX" in A1, no issue.

    • Close all files, don't save!
    • Open FileB.xlsx manually

    You should see a message at the top:

    If you click "Enable Content" the formula updates and the cell shows "XX".

    If you don't see that message, go into the Data tab, click "Edit Links", click the "Startup Prompt..."

    The usual setting is the 1st one, but you might have the second as this picture shows:

    Check also the settings inside the Trust Center\External Content:

    Andreas.

    0 comments