XL Charts to Word

[Risky Dave]

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

 

[Risky Dave]

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

 

[Jon Peltier]

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]

Dangerous Dave -

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:

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

 

[Risky Dave]

Jon,


As ever, my thanks.

Glad to see your site is back up )

Dave

 

[Jon Peltier]

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

 

[ryguy7272]

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---