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
626 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.

742 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