Macro that unlinks the fields in a document, and then deletes itself 1

[CookingFat]

Hi all,

The following macro unlinks the fields in a document, and then deletes itself:

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

Sub UnlinkFields()

Dim oRange As Word.Range

With ActiveDocument
For Each oRange In .StoryRanges
Do
oRange.Fields.Unlink
Set oRange = oRange.NextStoryRange
Loop Until oRange Is Nothing
Next
End With

ThisDocument.VBProject.VBComponents.Remove _
ThisDocument.VBProject.VBComponents("NewMacros")

End Sub

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

The document is "read-only". The idea is that I'd run the above macro, then save the document as a new document, and then when I email the (new) document to someone else they'd receive a document that 1) doesn't contain any fields, 2) doesn't contain any macros, and 3) isn't attached to any template.

However, does anyone know if it's possible to modify the above macro so that it unlinks the fields only within the text boxes in the document? The issue is that, for this particular document, the text boxes contain fields that are linked to an Excel spreadsheet (which I want to unlink and replace with text), but the remaining parts of the document contain a standard hyperlink and a mailto link (which I don't want to replace with text).

Thanks very much,
Steve

Note: The part of the above macro that deletes itself requires the following "Macro Security" option to be ticked: "Trust access to the VBA project object module".

Note: The above post is effectively a (very late) bump of the following post (which has been closed):
thread707-1810731

 

[macropod]

So why not simply save the document as a PDF and email that?

Your present code requires giving trusted access to the VBA Project, which creates a serious security risk for both your PC and any others on its network. All you need do to save a document without macros in it and without giving trusted access to the VBA Project is to save the document in the .docx format.

Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[CookingFat]

Hi Paul,

Thanks very much for your reply - that's really appreciated.

The document is basically a form that I want to email to individual people in .doc (or .docx) format. At my end, the document is linked to an Excel spreadsheet, which populates various text boxes on the form (e.g. it automatically enters the person's name, address, etc). The idea is that I send the person a form that's been partially completed with their details, and then they enter their remaining details and return the form to me.

Before I send the form to the other person, I need to unlink all of the fields in the text boxes (i.e. the fields that link to the above Excel spreadsheet). At present, I unlink the fields by manually pressing Ctrl+Shift+F9 in each text box. The idea of the above macro is that it would unlink all of the fields (with a single click) and then delete itself. This means that when I then save the form (as a new document) and send it to the other person, 1) it doesn't contain any fields, and 2) it doesn't contain any macros. As such, each of the other people would receive a partially completed form in .doc (or .docx) format without any fields or macros.

You're right that if I save the form in .pdf format, then it would effectively unlink all of the fields. However, at present I'm hoping to keep the form in .doc (or .docx) format, as I think the latter format is easier for the other people to complete. To be honest, the above macro pretty much does what I'm looking for except that it unlinks all of the fields within the document (including the fields outside of the text boxes, such as a standard hyperlink and a mailto link that I don't want to replace with plain text), whereas ideally I'd like it to unlink only the fields within the text boxes.

Thanks again,
Steve

 

[macropod]

As I have already told you, simply saving the document in the .docx format deletes all macros without the risks inherent in giving trusted access to the VBA Project.

It's also not apparent why you'd be using textboxes, when the linked objects could just as easily be inserted into single-cell tables - with or without text wrapping. That way, you could simply loop through all the links and unlink those pointing to Excel workbooks without the additional overhead of looping through textboxes. That said, try:

Sub Demo()
Application.ScreenUpdating = False
Dim Rng As Range, Shp As Shape
With ActiveDocument
  For Each Rng In .StoryRanges
    Do
    For Each Shp In Rng.ShapeRange
      With Shp
        If .Type = msoTextBox Then
          With .TextFrame.TextRange
            For i = .Fields.Count To 1 Step -1
              If InStr(.Fields(i).Code.Text, ".xls") > 0 Then .Fields(i).Unlink
            Next
          End With
        End If
      End With
    Next
    Set Rng = Rng.NextStoryRange
    Loop Until Rng Is Nothing
  Next
  .SaveAs FileName:=Split(.FullName, ".doc")(0) & ".docx", _
    FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
End With
Application.ScreenUpdating = True
End Sub

Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[CookingFat]

Thanks very much Paul - that's really good of you.

For info, when I run the above macro, I receive a "Compile error: Loop without Do" - so I'll try adjusting it.

 

[macropod]

Code revised.

Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[CookingFat]

Thanks very much Paul - works perfectly

 

[Andrzejek]

How about a Star for Paul? Click on Great Post!

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[CookingFat]

Thanks Andy.