Change the Excel source for an embedded chart - Word 2002

[Simon Cleal]

Changing the Excel source for an embedded chart - Word 2002


Hi,

I have a word document with numerous links to individual cells, ranges and
charts in an Excel sheet.

I'm trying to change the links programmatically so that all Excel objects
will point to a different spreadsheet.

I've written the code below to do this :

Public Sub ChangeSource

Dim intCount As Long
Dim strNewLink As String

strNewLink = "\\bwci-server-1\svr\_Template\ValnTemplate_CI_Test.XLS"

With ActiveDocument
' Loop through all the fields in document.
For intCount = 1 To .Fields.Count
With .Fields(intCount)
' If the field's type is an excel link then...
If .Type = 56 Then
With .LinkFormat
' change the source to the new source
.SourceFullName = strNewLink
End With
End If
End With
Next intCount
End With

End Sub

The code works fine for individual cells or ranges of cells but falls over
on the charts with error 6083 "Objects in this document contain links to
files that cannot be found. The linked information will not be updated."


This seems to be connected with the way Word writes the field code for a
linked chart as opposed to a linked cell

The linked cell field code has only one reference to the filename eg. {
LINK Excel.Sheet.8 "\\\\bwci-server-1\\SVR\\_Template\\ValnTemplate_CI.XLS"
"MemChngeData!R8C3" \a \t }

Whereas the linked Chart field code has 2 eg. { LINK Excel.Sheet.8
"\\\\bwci-server-1\\SVR\\_Template\\ValnTemplate_CI.XLS"
"MemChngeGraph![ValnTemplate_CI.XLS]MemChngeGraph Chart 3" \a \p }

I suspect that my code is changing the first occurrence of the filename but
not the second for the charts.

I’d like to know 2 things:

1) What is the property or code that I need, to change the links for charts?
2) How do I detect that I’m looking at a chart and not cells (as they both
seem to have the same type - 56)?

Thanks for your help
Simon

 

[Helmut Weber]

Hi Simon,

at the risk of making a foll out of myself and
according to my present level of knowledge,
I'd try to change the field's code,
not .SourceFullName.

As I have no idea how to find out in an elegant way
whether the link points to an Excel-range or an Excel-chart,
there would be a lot of tedious string processing to be done.

It seems that if the last switch is "/p" and if
there is a string like "chart x" in the field's code,
then the link points to an Excel-chart.

Don't know about field switches at all.
It is a question for "Macropod",
who knows all about fields.

At least this works for a document with
only two fields which are linked to two different excel-charts.

With ActiveDocument
.Fields(2).Code = .Fields(1).Code
.Fields.Update
End With

So it seems, all you got to do,
is to get the field code right.

Hmm..., not that easy.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Russ]

LinkFormat.Type Returns the link type. Can be one of the following
WdLinkType constants: wdLinkTypeImport, wdLinkTypeInclude, wdLinkTypeOLE,
wdLinkTypePicture, wdLinkTypeReference, or wdLinkTypeText. Read-only Long.

Are they both type reference? Or is the chart an OLE? An OLE may be a shape,
but we are dealing with fields here, right?

The brute force method may be to use instr with the field code and change
the fields.

Changing the Excel source for an embedded chart - Word 2002


Hi,

I have a word document with numerous links to individual cells, ranges and
charts in an Excel sheet.

I'm trying to change the links programmatically so that all Excel objects
will point to a different spreadsheet.

I've written the code below to do this :

Public Sub ChangeSource

Dim intCount As Long
Dim strNewLink As String

strNewLink = "\\bwci-server-1\svr\_Template\ValnTemplate_CI_Test.XLS"

With ActiveDocument
' Loop through all the fields in document.
For intCount = 1 To .Fields.Count
With .Fields(intCount)
' If the field's type is an excel link then...
If .Type = 56 Then
With .LinkFormat
' change the source to the new source
.SourceFullName = strNewLink
End With
End If
End With
Next intCount
End With

End Sub

The code works fine for individual cells or ranges of cells but falls over
on the charts with error 6083 "Objects in this document contain links to
files that cannot be found. The linked information will not be updated."


This seems to be connected with the way Word writes the field code for a
linked chart as opposed to a linked cell

The linked cell field code has only one reference to the filename eg. {
LINK Excel.Sheet.8 "\\\\bwci-server-1\\SVR\\_Template\\ValnTemplate_CI.XLS"
"MemChngeData!R8C3" \a \t }

Whereas the linked Chart field code has 2 eg. { LINK Excel.Sheet.8
"\\\\bwci-server-1\\SVR\\_Template\\ValnTemplate_CI.XLS"
"MemChngeGraph![ValnTemplate_CI.XLS]MemChngeGraph Chart 3" \a \p }

I suspect that my code is changing the first occurrence of the filename but
not the second for the charts.

I¹d like to know 2 things:

1) What is the property or code that I need, to change the links for charts?
2) How do I detect that I¹m looking at a chart and not cells (as they both
seem to have the same type - 56)?

Thanks for your help
Simon