CODEWITHSUNDEEP
excelvba
Joe
Joe

Reputation: 128

Updating workbook link sources

I am generating an Excel sheet using Java code + the Apache POI library. In some of the cells, I am inserting a reference to another sheet, which will not work and display '#REF' - which is fine. The intention here is that these cells will update and display correctly once the generated sheet is moved to the actual workbook it is intended to be used in.

When moving the sheet, the cells do not update. I found out that I have to update the link sources.

I've put together some code that will grab all links, iterate them and update their source to the current open workbook.

    Dim wbName As String
    Dim wbDir As String
    Dim i As Integer
    Dim wbLinks As Variant
    
    wbLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    
    If (UBound(wbLinks) > 0) Then
        wbName = Application.ActiveWorkbook.FullName
        wbDir = Left(wbName, InStrRev(wbName, "\"))
        wbDir = Left(wbDir, Len(wbDir) - 1)
        wbName = Mid(wbName, InStrRev(wbName, "\") + 1)

        ChDir wbDir
        For i = 1 To UBound(wbLinks)
            ActiveWorkbook.ChangeLink Name:=wbLinks(i), NewName:=wbName, Type:=xlExcelLinks
        Next i
    Else
        MsgBox ("No links found to update.")
    End If

So this is what I've tried. I recorded a macro of myself doing this manually (updating the source manually works) but even after replicating the code in VBA, it does not work.

Note:

Clicking the cell -> Clicking into the formula box -> Hitting enter = WORKS, no error at all.

Pressing CTRL + ALT + F9 (and with SHIFT) does not update the cells.

Two of the formulas used:

=MASTER!B8

=Management!N10

Upvotes: 0

Views: 672

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

You can try to re-write the formulas with VBA. Use the Range.SpecialCells method to find all formulas with error in a worksheet: Set ErrFormulas = ThisWorkbook.Worksheets("Sheet1").Cells.SpecialCells(xlFormulas, xlErrors)

And then loop through these to update them:

Option Explicit

Public Sub UpdateErrorFormulas()
    Dim ErrorOccured As Boolean
    ErrorOccured = False

    Dim ErrFormulas As Range
    Set ErrFormulas = ThisWorkbook.Worksheets("Sheet1").Cells.SpecialCells(xlFormulas, xlErrors)

    If ErrFormulas Is Nothing Then Exit Sub

    Dim Cell As Range
    For Each Cell In ErrFormulas
        Cell.Formula = Cell.Formula
    Next Cell
End Sub

Upvotes: 1

Related Questions