OzGrid

How to Resize word charts/pictures in excel

Requirement:

The user has written VB code in Excel to paste a series of charts as pictures into word:

"WordApp.Activedocument.bookmarks.Add Range:=WordApp.Selection.Range, Name:="datachart"

Sheets("Sheet3").Select
ActiveChart.ChartArea.Select
ActiveChart.CopyPicture

WordApp.Selection.Goto Name:="datachart"
WordApp.Selection.Paste"

The problem is user needs to be able to re-size these charts (either in word or excel) so that they are all a smaller size in the word document.

So how does the user  re-size these using Excel VBA?

https://www.ozgrid.com/forum/forum/other-software-applications/excel-and-or-word-help/10431-resizing-word-charts-pictures-in-excel

Solution:

This will reduce the a chart on a worksheet:

    
ActiveChart.Parent.Width = ActiveChart.Parent.Width * 0.7
ActiveChart.Parent.Height = ActiveChart.Parent.Height * 0.7

This will ensure the chart stays reduced when copied to word:

Sub Chart2Word()

    Dim WordApp As Object
    
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = True
    WordApp.documents.Add
    WordApp.activedocument.bookmarks.Add Range:=WordApp.Selection.Range, Name:="datachart"
    
    ActiveChart.ChartArea.Select
    ActiveChart.CopyPicture
    
    WordApp.Selection.Goto Name:="datachart"
    WordApp.Selection.Paste
    WordApp.activedocument.inlineshapes(1).Width = WordApp.activedocument.inlineshapes(1).Width * 0.7
    
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Andy Pope.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery