VBA to delete chart image in Word

[edwardj3]

Hi community. Hope you can help a VBA novice.

I've found some VBA code which enables charts in excel to be pasted in to a Word doc. See code below. I want to enhance this further so could you advise on how I do the following:

• delete the charts in the word doc (40+ each with its own bookmark location e.g. Bookmark1?
• When pasting, how I can paste to a specific size?

Current code

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdRng As Word.Range


' get Word application if it's running

Set wdApp = GetObject(, "Word.Application")
On Error Resume Next

If wdApp Is Nothing Then
' word not running so start it and create document
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add
Else
If wdApp.Documents.Count > 0 Then
' get active document
Set wdDoc = wdApp.ActiveDocument
Else
' no active document so create one
Set wdDoc = wdApp.Documents.Add
End If
End If

' get cursor location
Set wdRng = wdDoc.Bookmarks("Bookmark1").Range

' copy chart

Sheets("sheet1").ChartObjects("Chart 2").Chart.ChartArea.Copy


' paste chart
wdRng.PasteSpecial _
Link:=False, _
DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, _
DisplayAsIcon:=False

Thanks in advance.

 

[NdNoviceHlp]

Seems like this would delete all of your charts (and maybe other inlineshapes if they exist?). Untested...

Dim TempObj as Object
For each TempObj in  wdApp.wdDoc.InlineShapes
TempObj.delete
Next TempObj

Not sure how to change the chart size in Word. Maybe trial changing the size before the copy. HTH. Dave

 

[edwardj3]

Thank you very much Dave. Slight amendment - Removed wdApp. and it worked.

I've changed the word doc to a macro version and added some code to change the chart size there. See below for info. Seems to do the job for me.

Dim Inline as InlineShape
For each Inline in ActiveDocument.InlineShapes
Inline.LockAspectRatio = msoFalse
Inline.Height = 250
Inline.Width = 525
Next Inline

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome and the additional code to size the chart in Word. Have a nice day. Dave