Break save a word document from excel using VBA

[robovacuum_2]

Hi all,

I have created the following code to update my word document (by refreshing links to cells in an external excel workbook) and then save as a new macro-free word document (including a dynamic file name that reflects the following day's date).

I would like to break all links to excel prior to saving the new word file, such that the new file can not be updated should the excel file be refreshed (I.e., the links in word would be replaced with the refreshed excel cell value alone rather than maintaining the link)

Has anyone done this before / able to assist here?

See current code below

-----------------------------------

Sub Update_links()
Dim objWord As Object
Dim objdoc As Object

Set objWord = CreateObject("Word.Application")
Set objdoc = objWord.documents.Open("C:\Users\Desktop\NPF v1.docm")
objWord.Visible = True
objdoc.Activate

Application.AskToUpdateLinks = True
objWord.Options.updatelinksatopen = refreshlinks

objdoc.SaveAs ("C:\Users\Desktop\today\" & "BB 1" & Format(Sheets("BB (dynamic)").Range("C14").Value, "dd-mmm-yy"))
objdoc.Close

End Sub

 

[sijpie]

Hi Robovacuum, next time you post code, please use code tags around your code (click on the <vba/> button in the toolbar of the post window)

Something like this should work:

Sub Update_links()
    Dim objWord As Object
    Dim objdoc As Object
    Dim shapeLoop As Shape
    
    Set objWord = CreateObject("Word.Application")
    Set objdoc = objWord.Documents.Open("C:\Users\Desktop\NPF v1.docm")
    objWord.Visible = True
    objdoc.Activate
    
    Application.AskToUpdateLinks = True
    objWord.Options.UpdateLinksAtOpen = refreshlinks
    
    With objdoc
        'loop through all OLE object and break the links
        For Each shapeLoop In .Shapes
            With shapeLoop
                If .Type = msoLinkedOLEObject Then
                    .LinkFormat.BreakLink
                End If
            End With
        Next shapeLoop
        .SaveAs ("C:\Users\Desktop\today\" & "BB 1" & _
                 Format(Sheets("BB (dynamic)").Range("C14").Value, "dd-mmm-yy"))
        .Close
    End