XL Charts to Word

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Risky Dave - 26 Mar 2008 14:46 GMT

Hi,

I am looking to build a macro that will do the following from a single
button click in XL 2003:

1) Open a predefined Word document that I am using as a template;
2) Copy a selected chart to a specified cell within a table in the document;
2) Repeat for several charts to several different cells in Word.

The Word document is actually an A3 page containing a single 3x3 table. I
want to paste (as pictures) 5 different charts to 5 of these cells. The
charts are already generated within the source workbook on different sheets.

I can copy a given chart to a single cell (chosen by where the cursor is) in
an already open document (thanks Jon Peltier), but how do I:

1) Open the Word document from XL (the name and network path are known and
will always be the same;
2) Move the cursor between cells in Word so that I can paste the next chart
in;
3) Move between XL and Word files within a macro - I can do this between
different XL workbooks using Windows(nnn).Activate, is there something
similar between applications?

If someone can solve these for me, I'm reasonably happy that I can sort the
rest of it out.

TIA Dave

Reply to this Message

Risky Dave - 26 Mar 2008 14:56 GMT

Alternatively....

I can move the cursor between cells using Word macros, so is it possible to
call a Word embedded macro from an XL routine?

TIA

Dave

> Hi,
>
[quoted text clipped - 24 lines]
>
> TIA Dave

Reply to this Message

Jon Peltier - 26 Mar 2008 15:11 GMT

> I can move the cursor between cells using Word macros, so is it possible
> to
> call a Word embedded macro from an XL routine?

It's probably easier to put the code into an Excel macro with the required
references, and run it from within Excel. One stop shopping. You can also
reference the table in word by column and row. Here is some pseudo-code to
enter text into a cell of a Word table:

wdApp.Documents("My Report.doc").Tables(1).Cell(2,2).Range.Text = "ABC"

If you copy the Excel chart, this pastes it as a picture:

wdApp.Documents("My Report.doc").Tables(1).Cell(2,2).Range.PasteAndFormat
wdChartPicture

For best results, you should format your chart in Excel exactly as it will
appear in Word, so it doesn't even need to be resized.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Reply to this Message

Jon Peltier - 26 Mar 2008 15:16 GMT

Dangerous Dave -

> I am looking to build a macro that will do the following from a single
> button click in XL 2003:
[quoted text clipped - 12 lines]
> in
> an already open document (thanks Jon Peltier), but how do I:

You're welcome!

> 1) Open the Word document from XL (the name and network path are known and
> will always be the same;

You need a Word application object (see
http://peltiertech.com/Excel/XL_PPT.html) and a document:

' Reference existing instance of Word
Set WDApp = GetObject(, "Word.Application")
' Open document
Set WDDoc = WDApp.Documents.Open(strPathAndFileName)

> 2) Move the cursor between cells in Word so that I can paste the next
> chart
> in;

See your other post. You don't need to move the cursor, just reference the
range where the item should be pasted.

> 3) Move between XL and Word files within a macro - I can do this between
> different XL workbooks using Windows(nnn).Activate, is there something
> similar between applications?

You don't need to move back and forth, nor to activate objects before
working on them, just use code in Excel that references and manipulates the
objects in Word.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Reply to this Message

Risky Dave - 26 Mar 2008 15:46 GMT

Jon,

As ever, my thanks.

Glad to see your site is back up :-))

Dave

> Dangerous Dave -
>
[quoted text clipped - 49 lines]
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______

Reply to this Message

Jon Peltier - 26 Mar 2008 16:33 GMT

Thanks. The blog is still woefully slow. The hosting company did a major
transition of users to a new platform, without first estimating the required
server loads in the new platform. So we're waiting while they install and
test out some new server boxes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> Jon,
>
[quoted text clipped - 63 lines]
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______

Reply to this Message

ryguy7272 - 26 Mar 2008 16:38 GMT

You sound like you know your way around the VBE, which is great. Below is a
macro that I use; it is pretty awesome actually.

Sub ControlWordFromXL()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
On Error Resume Next

Sheets("Sheet1").Activate
objWord.ActiveDocument.Variables("First_Name").Value =
Range("First_Name").Value
objWord.ActiveDocument.Variables("Last_Name").Value =
Range("Last_Name").Value

‘etc…….
objWord.ActiveDocument.Fields.Update

On Error Resume Next
objWord.Visible = True

End Sub

To make this work, you have to insert DocumentVariables in Word. Open Word
and click Insert > Field > Field Name > DocVariable (then name the
variable…in my example above it is called ‘First_Name’).
Notice: in the macro above it is referenced as:
objWord.ActiveDocument.Variables("First_Name").Value
Notice: you must have a named range in Excel that corresponds to this
DocVariable. In Excel I have a range named ‘First_Name’, and this is
referenced in the macro above as: Range("First_Name").Value

Hope That Helps!!

Regards,
Ryan---

Signature

RyGuy

> Jon,
>
[quoted text clipped - 57 lines]
> > Peltier Technical Services, Inc. - http://PeltierTech.com
> > _______

Reply to this Message