Transferspreadsheet with an Embedded Chart

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a word document which contains an embedded chart. I would like to use
the Docmd.transferspreadhseet method in my Access database to update the
source of the embedded chart. Initially I was using a linked chart and this
worked fine. But now that I am trying to do it with an embedded chart, I
don't know how to properly reference the worksheet in the embedded chart.
What is the naming convention for the embedded worksheet?? And is this
possible to do??
 
Hi Marjie,

I've never tried this myself, but I think your code will have to hunt
through the Word.Document object until you find the embedded object that
is the chart, then activate it (in effect launch it in Excel), get hold
of the resulting Excel workbook object, and finally find the worksheet
range and use Range.CopyFromRecordset to update the data.

What fun!
 
Thanks, I will try that. Can you give me some idea of how to "hunt through
the Word.document"? And is there a good place to find what properties,
functions, commands are available in VBA code when working with a application
object?
 
I'd guess that you need to iterate through the Document's Shapes
collection and/or its Fields collection, depending on how the chart was
inserted. If you have used Tools|References to add references to the
Word and Excel object libraries, you can use the Object Browser to
explore the Word and Excel object models: just press F2 in the VB
editor. On a good day, if you select something in the Object Browser and
hit F1, it will then find the relevant topic in Word or Excel Help.

Also, check out the Word MVPs website at http://word.mvps.org, which is
a treasure trove of information about Word. If you can't fing what you
want there or at http://groups.google.com, ask in one of the Word
newsgroups.
 
Thank you very much. I was not aware of the object browser. That should be
a big help to me.

Marjie
 
Back
Top