VBA Command to Refresh Excel Charts in Word 2007?

[Paul M. Eldridge]

I've linked a couple of Excel charts to a Word document and would like
to have VBA refresh these charts on demand (the update method for this
particular Word document is set to manual).

Using the mouse, I can click on the Office Button - Prepare - Edit
Links to Files, highlight the source files to be updated and click
Update Now, then Close to return to the document. Unfortunately, the
macro recorder does not allow me to record these keystrokes and I'm at
a loss as to the correct VBA commands and structure. Could anyone
point me in the right direction?

With much thanks,
Paul

 

[Doug Robbins - Word MVP]

If it is OK to update all of the fields in the document, then use

ActiveDocument.Fields.Update

If you only want to update Links in the document, then use

Dim alf as Field
For each alf in ActiveDocument.Fields
If alf.Type = wdFieldLink then
alf.Update
End if
Next

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Paul M. Eldridge]

Hi Doug,

Thanks kindly for your assistance. These two charts are the only
items that are externally linked and I had previously tried your first
suggestion, but without success. This is new territory for me, so
bear with me as I stumble through this with you, but I originally cut
and paste these charts from Excel and chose the "Chart (linked to
Excel data)" option within the accompanying Paste options tag. The
document update method is set to manual and I can refresh the links
either through the Links dialog or by way of the Refresh Data command
within the Chart Tools - Data panel. So just to clarify this in my
own mind, does Word treat these charts as "fields", or do I need to do
something else to have them recognized as such or, alternatively, link
to them in some other fashion so as to have them properly initialized?

Thanks again for helping me complete this last piece of the puzzle.

Best regards,
Paul

 

[Doug Robbins - Word MVP]

What do you see if you press Alt+F9 in the document?

Is it something like

{ LINK Excel.Sheet.8 "Workbookname" "Sheetname![workbookname]Sheetname Chart
1" \a \p }

You said that you Cut the chart from Excel. Did you save the workbook after
doing that? If so, there would be no chart in the workbook for word to
update and when you try to update the Word document manually by selecting
the chart and pressing F9, you would get a message "Objects in this document
contain links to files that cannot be found. The linked information will
not be updated."

In those circumstances, in you use

ActiveDocument.Fields.Update

you do not get the message and nothing happens.

Otherwise, ActiveDocument.Fields.Update does cause a linked chart to be
updated for me.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Paul M. Eldridge]

Hi Doug,

Thanks again for your assistance; much appreciated. Some mixed
results to report. As per your previous note, I retried using the
ActiveDocument.Fields.Update command but it still doesn't work and
nothing happens when I press Alt+F9 (or at least nothing that is
visible onscreen). However, I can confirm the source spreadsheet is
re-saved under the original filename and within the original folder
after the data has been updated by the macro (hence the need to
refresh the document links) and I know the links are valid because I
can force a refresh through either the Links dialog or by way of the
Chart Tools - Data menu. One interesting twist, though... I selected
each chart and pressed F9 as you suggested below and both updated to
reflect the new numbers (go figure, eh?).

Any sense as to what I may have done wrong?

Best regards,
Paul