Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
637 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
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 …
Suggested Courses

636 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