Solved: Word: How to change links to Excel

[Thomas]

I?m using Office 2003
I have a lot of linked Excel-graphs (60) in my Word document. I want to change the source-file by code.
I think I can use the properties ProgID and Linkformat to do this.
But when I type in this little testcode:
Sub test()
Dim s As Shape
For Each s In ActiveDocument.Shapes
Debug.Print s.OLEFormat.ProgID
Next s
End Sub
I always get the error:
"Object variable or With block variable not set"
I can?t understand what i have missed to declare.
Anyone have an idea of whats wrong or if some other method is better to use?

[MOS MASTER]

I?m using Office 2003
I have a lot of linked Excel-graphs (60) in my Word document. I want to
Hi & Welcome to VBAX! :hi:

You're code uses the Shape collection but are you sure the linked excel graphs are in the Shapes collection? (Meaning are they floating and are the really linked?)

If they are you can change them like:
Sub FillListBoxes()
Dim oShape As Word.Shape
Dim sFile As String

sFile = ActiveDocument.Path & "\Test.xls"

For Each oShape In ActiveDocument.Shapes
If oShape.Type = msoLinkedOLEObject Then
If oShape.OLEFormat.ProgID = "Excel.Sheet.8" Then
With oShape.LinkFormat
.SourceFullName = sFile
End With
End If
End If
Next
End Sub


If this doesn't work tell me exactly how you made the OLE excel objects (Step by step) and tell me if the object is in line with text or floating. (So I can suggest other code)

Enjoy! :whistle:

[Thomas]

Hi, I tried your example rows, but they didn?t work. The type-test never successed so mayby I'm wrong about the Shape-collection.

I have made my linkes this way.
Multiple graphs has been (from different sheets in same .xls-file)
linked to a 70 page word document (on various sheets) by "copying" the
graph(s) in the Spreadsheet and then "pasting special" onto the word document as an MS Office Excel Chart Object. I have also copied cells and "paste special" as formatted text.

I hope you can give me some ideas

H?lsningar
Thomas

[MOS MASTER]

Hi, :yes

I'm still not sure because I don't no if it's a shape or InlineShape. Have to no for sure..

Perhaps you could send a dummy example file (Zipped) as attachment.
Make it the way you did the others and I'll have a play with them.

Later..:whistle:

[Thomas]

Hello again, thank you for your engagement.

I have a resolution build in Access were the results is dumped to Excel- and Word-files. So my speciallity is Access and my knowledge in Word isn?t enough to solve my problem.

I have now zipped the two files (.xls & .doc) with links between. I now want to move them to another folder from code in Access and then relink the word document to the moved Excel-file. The copy is already ready and of course you don't have to send me the Acces-code. I need a hint of how to do this. I have tested The Shapes-, InlineShapes- and field-collection but with no success.

The zipped Word file is linked to the Excel-file placed in
'C:\Program\Bright Analyst' (notice program, not programs, swedish version)

Thank you again for your engagement.

/Thomas

[MOS MASTER]

Hi Thomas, :yes

Ok I'll look at it a.s.a.p. :whistle:

[MOS MASTER]

Hi Thomas, :yes

I found a easier way to do this.
If you toggle the view to show field codes you can find and replace the old path with the new path and then update the fields and toggle view back.

Do it like:
Option Explicit

Const OLD_PATH As String = "C:\\Program\\Bright Analyst\\report_dump.xls"
Const NEW_PATH As String = "C:\\Program\\Bright Analyst\\Test Folder\\report_dump.xls"

Sub ReplaceSourceFile()
With ActiveWindow
.View.ShowFieldCodes = True

With .Selection
.Find.ClearFormatting
.Find.Replacement.ClearFormatting
With .Find
.Text = OLD_PATH
.Replacement.Text = NEW_PATH
.Forward = True
.Wrap = wdFindContinue
.MatchCase = False
.Execute Replace:=wdReplaceAll
End With
.WholeStory
.Fields.Update
End With

.View.ShowFieldCodes = False
End With
End Sub


Enjoy! :whistle:

[Thomas]

Thank you, it helped me. But you cant rename the file, if so is the items not updated. But I have fixad that by making new folders in my code.

[MOS MASTER]

Thank you, it helped me. But you cant rename the file, if so is the items not updated. But I have fixad that by making new folders in my code.
Hi Thomas, :yes

You?re most welcome but you?re wrong.
You can change the file name but the file name is used two times in the fieldcode (Check it) once in the path and once to specify the chart location in the excel file. (The part between the block hooks e.g. [bookblah.xls])
So in that case you have to run a separate find & replace action to look for the old filename & replace that with the new filename.

Enjoy! :whistle: