CODEWITHSUNDEEP
excelpowershellms-word
DamianB
DamianB

Reputation: 471

How to copy chart from Excel to Word?

I'm running a script to automate a bit of our reporting work.

We have data from our alerting system and We copy that into an excel sheet which has a chart in it.

We then copy the chart from the excel sheet to a word document.

This isn't that hard until you think about the fact we're doing it for ~50 servers every month.

I've been able to automate the dumping the data into the excel spreadsheet with the chart in it and updating the chart. I'm getting stuck with copying and pasting the chart from Excel to Word:

$xl = new-object -comobject excel.application 
$xl.Visible = $true

$wb = $xl.workbooks.open("C:\blah\Servername_graph.xlsx")
$ws = $wb.worksheets.item(1)
$charts = $ws.ChartObjects()
$chart = $charts.Item(1)
$a = $chart.copy

$wd = new-object -comobject Word.application
$wd.visible = $true
$path = "C:\blah\doc.docx"
$doc = $wd.documents.open($path)

As you can see from the code I am opening up the excel sheet, selecting the chart and copying it. I'm opening the doc but unsure as to how to paste it.

Upvotes: 4

Views: 3503

Answers (2)

rohrl77
rohrl77

Reputation: 3337

You can also use something like this:

cht1.CopyPicture Appearance:=xlScreen, Format:=xlPicture

With tbl.cell(4, 1).Range
        .Paste 'Paste Graph
        .InlineShapes(1).ScaleWidth = 100
End With

Where tbl.cell(4, 1).Range is a reference to a table or cell in word

The advantage to this method is that you are not using .Select

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200503

Like this:

$wd.Selection.Paste()

To paste the chart as a picture use the PasteSpecial method:

$default = [Type]::Missing
$wd.Selection.PasteSpecial($default, $default, $default, $default, 9, $default, $default)

Upvotes: 4

Related Questions