Remove Link to Word

jsartain

New Member
Joined
Mar 24, 2002
Messages
24
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?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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

New Member
Joined
Mar 24, 2002
Messages
24
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

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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

New Member
Joined
Mar 24, 2002
Messages
24

ADVERTISEMENT

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

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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

New Member
Joined
Mar 24, 2002
Messages
24

ADVERTISEMENT

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

bmmarti3

New Member
Joined
Feb 23, 2010
Messages
4
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

New Member
Joined
Feb 23, 2010
Messages
4
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

New Member
Joined
Feb 23, 2010
Messages
4
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.
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,257
Latest member
Calion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top