Remove Link to Word

[jsartain]

I currently have a excel sheet doing calculations and linking them to a word document. I have a macro written that opens a word document. The document itself has links to the excel sheet to correctly fill in the needed data.

My problem is that I need to have the Word document able to be emailed without having these links. In Excel, I would just copy and special paste values. Is there a function to do the same in Word (via VB in excel)?

An idea of my current code follows:

Sub CommandButton1_Click()

Dim wdApp As Object
Dim wdDoc As Object
Location = ThisWorkbook.Path_ & "Template FolderGECO.doc"
Set wdApp = CreateObject_("Word.Application")
Set wdDoc = wdApp.Documents.Open_(Location)
wdApp.Visible = True

With wdApp
.Dialogs(wdDialogFileSaveAs).Show
End With

Set wdObj = Nothing
Range("A1").Select


Any ideas on how to remove the current links in the Word document?

 

[Corticus]

Hi!

There are several special pastes in Word that should work for you: Formatted Text(RTF), Unformatted Text, and Unicode Unformatted Text, I believe any of these should eliminate links. If you wish to automate this, record the macro in Word, then check out this post to see how to use Word VBA in Excel through a library reference:

http://216.92.17.166/board/viewtopic.php?topic=21589&forum=2

HTH,
Corticus

 

[jsartain]

Corticus,

Thank you for the reference. Maybe my problem is the methodology. I have a template in Word that has all kind of verbage. It also has about 20 fields of linked data to my excel workbook. The fields were created using the paste special and creating a link. It was done this way so that when the Word template is opened, it updates the links with the new information from excel. I then want to remove those links so the document is fixed with the data from excel. If I do not break the links, the next time the Word document is opened, it will update the information from Excel. This will not work because I need this information fixed everytime the word document is saved as a different file.

Is there maybe a better way to link to Word through VB? If so, how do you define fields to be updated from excel?

 

[Corticus]

Allright, got it:

I was wrong about pasting to break the links, but-

Open your Word doc, click on edit, then click on links.

You'll see you have several options: break the link, force a manual update, etc etc.

 

[jsartain]

Corticus,

You have it exactly. I would like to do the exact process as edit/links/break link in Word. My problem still remains that I need this to automated. I need this to be done automatically through excel when the word document is opened and the links are updated. I have users that would be unable to do the process of manually breaking links everytime.

 

[Corticus]

I'm not to hot with Word VBA, but, try recording the macro in Word, then pasting it into your Excel procedure, keeping in mind that you need new references(see link above).

Also, manual updates should allow you to send a snapshot of the .doc at a certain time.

Unfortunately, I'm sick, so I have to leave work now, but I'll be back tommorrow.

Good Luck,
Corticus
This message was edited by Corticus on 2002-10-14 12:52

 

[jsartain]

Thanks for help. I did try to record this macro in Word, but it did not record anything.

 

[bmmarti3]

Looking for some advice on this exact issue.

I need to press a button in an excel table that opens a MS word doc and updates all the linked feilds (not an issue).

Now I want a module that can automatically un-link the fields in Word that were updated from excel.

Any ideas?

 

[bmmarti3]

I have this to open the word doc:

---

Sub automateword()


Set wordapp = CreateObject("word.Application")


wordapp.documents.Open "C:\Documents and Settings\martinbm\Desktop\MF Proposal Gen\MF Proposal Gen.doc"


wordapp.Visible = True


End Sub

---

And I have this macro that can remove all hyperlinks from my desired word doc:

---


Sub RemoveHyperlinks()
'Remove all hyperlinks from the active sheet
ActiveDocument.Fields.Unlink
End Sub

---

Is there a way to use the open the specific word doc command to open the word doc and then run the remove hyperlinks macro after opening?

 

[bmmarti3]

Sub automateword()


Set wordapp = CreateObject("word.Application")


wordapp.Documents.Open "C:\Documents and Settings\martinbm\Desktop\MF Proposal Gen\MF Proposal Gen.doc"


wordapp.Visible = True

wordapp.Run "ProposalGen.NewMacros.RemoveHyperlinks"
' Note that the project name and module name are required to
' path the macro correctly.

End Sub

---

This seems to be working.

Note ProposalGen is the Project Name, NewMacros is the Module name, and Remove Hyperlinks is the sub command name.

And the Remove Hyperlinks module loaded in my MS Word doc template is the one from the post I made right above.