Breaking linked tables using VBA

[alexransome]

Hello,
I have a sheet that has data in the cells and that data is linked to areas in a word document.

I'd like to break the link just before the word doc is saved so that the data doesn't update when opening next. The next person to open the word document will not want to see a dialog box asking to update the links (my boss!!!)

This is my code so far ...

'Open Word and view the VAF-MASTER file
    Set wordapp = CreateObject("word.Application")
    wordapp.Documents.Open "\\FileLocation\FileName.docx"
    wordapp.Visible = True
    
'Break Excel links in the doc
    With ActiveDocument.Fields.Unlink
    
'Auto save
    wordapp.ActiveDocument.SaveAs "\\FileLocation\FileName-" & Sheets("eMail").Range("E16")

Thanks for any assistance and help.

 

[Yongle]

I think your VBA is in Excel so ...ensure that you have added reference to Microsoft Word Object Library
(in VBA editor \ Tools \ References \ scroll down \ mark the correct box OK )

Try this ...

Sub alexransome()
    Dim fld As Word.Field, wordApp As Word.Application, doc As Word.Document

'Open Word and view the VAF-MASTER file
    Set wordApp = CreateObject("word.Application")
    Set doc = wordApp.Documents.Open("\\FileLocation\FileName.docx")
    wordApp.Visible = True
   
'Loop through fields
    For Each fld In doc.Fields
            If fld.Type = wdFieldLink Then fld.LinkFormat.BreakLink
    Next fld
   
'Auto save
    doc.SaveAs "\\FileLocation\FileName-" & Sheets("eMail").Range("E16")
End Sub

 

[alexransome]

Thankyou for your suggestion. It works almost perfectly, the thing is that when the word document is saved then the formatting of the now broken links changes back to the standard format.

I did have to change the "* MERGEFORMAT" to "* CHARFORMAT" so that formatting would be lost. I tried all the other options of pasting unformatted links, RTF etc.

 

[alexransome]

Just noticed that the word document linked fields in the MASTER copy which is opened has ...\a \f 5 \r } at the end of the linked field when using ALT-F9 to view the links. In the fields where the formatting changes this is at the end ... \a \t \u \* CHARFORMAT }.

What would change in endings mean?

 

[Yongle]

I did not test the code so it is useful to know this
It happens presumably because Word formats the link at time of creation (taking the formatting from the source?) and destroys the formatting when the link is broken
When I get some time (which will not be today) I will see if I can fix that

What is the linked object and how are you creating the link in the first instance?

 

[Aureliusz Nowakowski]

I dont know hot to make a graph please let me know how to do it

 

[Fluff]

@Aureliusz Nowakowski
Please do not "Hijack" other peoples threads. You need to start a thread of your own.
Thanks

 

[alexransome]

I did not test the code so it is useful to know this
It happens presumably because Word formats the link at time of creation (taking the formatting from the source?) and destroys the formatting when the link is broken
When I get some time (which will not be today) I will see if I can fix that

What is the linked object and how are you creating the link in the first instance?

The linked object are just cells in the sheet. What I have done is to copy the cell and then paste (via paste button top left of ribbon bar) special, as unformatted text