Solved

How to change the Source File for a linked Excel Cell in a Word document by Macro and VB

Posted on 2009-05-10
4
616 Views
Last Modified: 2013-12-20
Here is the problem:

I have a Word Document that has an object (Excel chart) links to Chart in another Excel document. So there is this property of Links in Word document that contains Souce File, Item in File, Link Type. By changing the value of these property, different chart can be linked from Word document.

VB macro required:
1) Source File property contains the absolute path of the linked Excel file name, I will like to change the path using VB macro

2) Changing the value of "Item in File" and Link type is also needed.


T8-Weekly-Report--thisweek-.doc
0
Comment
Question by:tindavid
  • 2
4 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24380073
You might need something like this:
Sub ModFields()
Dim fld As Field
For Each fld In ActiveDocument.Fields
    If fld.Type = wdFieldLink Then
        fld.Code = Replace(fld.Code, "\\\\hitfs03", "\\\\NewServer")
    End If
Next fld
End Sub

Open in new window

0
 

Author Comment

by:tindavid
ID: 24391517
Hi Graham;

I want to execute this type of macro from Excel, that is using macro to open an word document and :

1) change the links within the word document
2)  also how can I insert few blank/dummy lines when I find a key word in the word document.

many thanks
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 24393899
This code uses early binding, so you need to set a reference to the Microsoft Word Object library in the Excel VBA editor.

It calls a modified version of the previous macro, then puts two blank lines (paragraphs) after each occurrence the word 'MyKeyword'
Sub EditWordDoc()
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open("C:\MyFolder\MyFile.Doc")
    ModFields wrdDoc
    With wrdDoc.Range.Text
        .Find = "MyKeyword"
        .Replacement.Text = "MyKeyword" & vbCr & vbCr
        .Execute Replace:=wdReplaceAll
    End With
End Sub
 
Sub ModFields(Doc As Word.Document)
Dim fld As Word.Field
For Each fld In Doc.Fields
    If fld.Type = wdFieldLink Then
        fld.Code = Replace(fld.Code, "\\\\hitfs03", "\\\\NewServer")
    End If
Next fld
End Sub

Open in new window

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question