How to copy a chart from Excel to Word?

Question

I am running a script to automate working with reports.

We have data from our notification system, and we copy it into an Excel sheet, in which there is a chart.

Then we copy the diagram from the excel sheet into a text document.

It is not so difficult until you think about the fact that we do this for ~ 50 servers every month.

I was able to automate dumping data into an Excel spreadsheet with a chart in it and updating the chart. I am stuck in copying and pasting charts 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 open the excel sheet by selecting the chart and copying it. I open a document, but don’t know how to insert it.

Answer 1

Like this:

 $wd.Selection.Paste() 

To insert a chart as an image, use the PasteSpecial method:

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

Answer 2

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 - link to a table or cell in a word

The advantage of this method is that you do not use .Select