Paste Excel chart in a specific place in a Word-document (report) exported from Excel?

[perola.rike]

I have 300 rows of data (psychological tests) that are converted to text and exported from Excel to a formatted report in Word (the code for this is approx 500 lines so only an extract of this code is pasted in this thread, the "export chart" part).

Part of this Word-report, the data in use are graphically presented in a chart.
Unfortunately, the chart are pasted at the bottom of the Word document, I want it to be pasted in a specific place inbetween some specific text lines.

I've read some about Word bookmarking etc, but I really can't figure it out and make it fit my code.

Any tips on how to paste the chart in a specific place in the Word report?

The whole code is saved as a txt document here: https://www.dropbox.com/s/du3dp9vs1qoisyy/code report.txt?dl=0

Best regards,
Per-Ola




'Copy/paste profilchart
'Dim word As Object
Dim doc As Object
Dim chrt As ChartObject
' Dim i As Integer
On Error Resume Next
'(For i = 1 To 1)
Sheets("profilark").Activate

For Each chrt In ActiveSheet.ChartObjects
i = i + 1
Next

ActiveSheet.ChartObjects.Item(i).Activate
ActiveChart.CopyPicture Appearance:=xlScreen, Format:=xlPicture
With WordApp.Selection
'Paster Chart
.Range.Paste
Application.CutCopyMode = False

End With

'Save the Word file And Close it
With WordApp
.ActiveDocument.SaveAs Filename:=SaveAsName
.ActiveWindow.Close
'Kill the Object
.Quit
End With

Set word = Nothing
'Reset status bar
Application.StatusBar = ""
MsgBox "Autoreport " & savename & ".doc was saved in " & ThisWorkbook.Path
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

 

[Jon Peltier]

In the far recesses of memory I had a project that populated a Word report with tables (ranges) and charts from Excel. I don't recall the specifics, but in general it went like this.

Bookmarks were inserted at various places in the Word document. The bookmarks had prefixes like "tbl" and "cht" for whether the bookmark was to accept a range of cells (table) or a chart. In both cases, the range or chart were copied as a picture then inserted, so it wasn't a live linked connection or anything. The rest of the bookmark name matched the name given to the range or chart in Excel.

The code lived in Excel. It found the active document in Word, then looped through the bookmarks in the document, and determined the range of the bookmark (meaning the point where the cursor would be if it were at the bookmark). Based on the bookmark name, the program found the object in Excel, copied it as a picture, then pasted it at the range in Word where it had found the bookmark.

There were a lot of associated features. The client wasn't proficient at either Excel or Word, so he would go through the Word document and insert a string like [<TableQ12a>] or [<ChartQ12a>] where he wanted the Excel content to magically appear and it would insert a bookmark named tblQ12a or chtQ12a, then find the range or the chart in Excel that showed the responses to survey question 12a, and name the range tblQ12a chart chtQ12a. This ensured that the bookmarks and names all matched up.

I did several similar projects, but this one was the most involved, and probably the one that I was paid the least per hour.