Resize Word Metafile from Excel SpreadSheet 1

[JayMan25]

Hey guys i'm having a problem trying to figure out how to resize this metafile image that i'm pasting into word. Here is the code so far. I'm new at this and I'm stuck.


Public Sub Create_Word_Report()

Const wdWindowStateMaximize As Integer = 1
Const wdPrintLayoutView As Integer = 3
Const wdAlignParagraphCenter As Integer = 1
Const wdAnimationShimmer As Integer = 6
Const wdPasteMetafilePicture As Integer = 3
Const wdInLine As Integer = 0
Const wdPageFitFullPage As Integer = 1

Dim WrdDoc As Object
Dim Wrksht As Workbook
Dim Sht As Worksheet

Set WrdApp = CreateObject("Word.Application")

With WrdApp
.Visible = True
.WindowState = wdWindowStateMaximize
.Documents.Add
Set WrdDoc = .ActiveDocument
End With

WrdDoc.ActiveWindow.View = wdPrintLayoutView

Set Wrksht = ActiveWorkbook

For Each Sht In Wrksht.Worksheets
Sht.Activate

If Range("E3") <> Empty Then
Sht.Range("A1:H65").Copy

With WrdApp.Selection
.ParagraphFormat.Alignment = wdAlignParagraphCenter
.MoveRight
.PasteSpecial Link:=False, _
DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, _
DisplayAsIcon:=False
.ParagraphFormat.Alignment = wdAlignParagraphCenter
.InsertBreak Type:=wdPageBreak
End With

End If

Next

End Sub

 

[fumei]

Could you describe your problem in more detail?

Not sure why you are declaring the constants.

Gerry

 

[PHV]

Not sure why you are declaring the constants
I suspect late binding ...

 

[fumei]

Doh. Tired. You are REALLY annoying PH. <grin>

Gerry

 

[JayMan25]

When the metafile is pasted into the word doc its size is to big to fit on the page properly. I just can't figure out how to change the height and width of the image to fit on the page using VBA. Also is there a way to compress the image file size as well?

 

[fumei]

You can get it using VBA. Get the count of the InlineShapes. After you paste it into Word, it will be the last InlineShape.

Dim oShape As Word.InlineShape
Dim i As Integer
i = ActiveDocument.InlineShapes.Count
[COLOR=red]' set oShape to the LAST inlineshape[/color red]
Set oShape = ActiveDocument.InlineShapes(i)
    With oShape
[COLOR=red]' examples only - scale to 90%[/color red]
        .ScaleHeight = 90
        .ScaleWidth = 90
        ' * * * etc etc etc * * * *
    End With
Set oShape = Nothing

Gerry

 

[JayMan25]

Thanks!