Copying/Pasting Excel Range into Microsoft Word Template

[SimonGeoghegan]

Hi All,

I have some code which I found from "spreadsheetguru" - and amended for my own specification. This copies an excel range into a word template as a picture/image. This works well, but I'm trying to understand if there is any snippets of code that I can add in order to:

Resize the width and height of the image once in Word
Paste the image onto a particular page within the template.

At the moment, it pastes the image fine but at the very beginning of the word document.

Any help greatly appreciated!

Thanks,
Simon

Sub CGReport()

'PURPOSE: Copy/Paste An Excel Table Into a New Word Document
'NOTE: Must have Word Object Library Active in Order to Run _
  (VBE > Tools > References > Microsoft Word 12.0 Object Library)
'SOURCE: www.TheSpreadsheetGuru.com

Dim tbl As Excel.Range
Dim WordApp As Word.Application
    Set WordApp = CreateObject("word.Application")
Dim WordTable As Word.Table
Dim strFile As String
    strFile = "c:\Users\sgeoghegan\Desktop\Clinical Governance Reporting\Hospital Clinical Governance Report TEMPLATE V2.docm"
Dim myDoc As Word.Document


Dim HospitalName As String
    HospitalName = ThisWorkbook.Worksheets("Brain").Range("L2")
Dim ReportingMonth As String
    ReportingMonth = ThisWorkbook.Worksheets("Brain").Range("AV6")
Dim RiskManPopulationDate As String
    ReportingMonth = ThisWorkbook.Worksheets("Brain").Range("L3")

'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False
 
'Open Word Document
WordApp.Documents.Open strFile
WordApp.Visible = True

'Copy Range from Excel
  Set tbl = ThisWorkbook.Worksheets("Summary Page").Range("B5:AX50")
 
'Create a New Document
  Set myDoc = WordApp.Documents(strFile)

'Copy Excel Table Range
    tbl.CopyPicture

'Paste Table into MS Word
  myDoc.Paragraphs(1).Range.PasteSpecial
 
EndRoutine:
'Optimize Code
  Application.ScreenUpdating = True
  Application.EnableEvents = True

'Clear The Clipboard
  Application.CutCopyMode = False
 
End Sub

 

[rlv01]

I'd recommend

Resize the width and height of the image once in Word
Paste the image onto a particular page within the template.

I only occasionally have to dabble in Word VBA, so when I have to do things like this and I'm not sure of the needed syntax (or objects), I use the Word macro recorder to record a macro while I do the things I want the macro to do manually. Then I inspect the code generated by the macro recorder to learn what code I need to add to my excel macro.

 

[SimonGeoghegan]

Thanks for the reply rlv01. I've tried this - the VB code itself is actually in Excel rather than Word but the issue I face is the recorder assigns the image and so once added to my code and re-tested, the image name is different.

    ActiveSheet.Shapes.Range(Array("Picture 10")).Select
    Selection.ShapeRange.Height = 708.6614173228
    Selection.ShapeRange.Width = 1417.3228346457

 

[rlv01]

That's just a matter of finding the new shape you've just pasted.

    'Create a New Document
    Set myDoc = WordApp.Documents(strFile)
    
    'Copy Excel Table Range
    tbl.CopyPicture
    
    'Paste Table into MS Word
    myDoc.Paragraphs(1).Range.PasteSpecial
    
    'Assign shape
    Dim Sh As Word.Shape
    With myDoc
        Set Sh = .Shapes(.Shapes.count)
    End With
    
    'Size Shape
    Sh.LockAspectRatio = True
    Sh.Width = Sh.Width * 1.5
    
    'Position Shape
    Sh.Top = Sh.Top + 25
    Sh.Left = Sh.Left + 25

 

[SimonGeoghegan]

Thanks rlv01 - thats excellent, works perfectly - just need to adjust the positions to suit!

 

[Mescoman]

Hi there, maybe I should open a new thread for this question but it seems very related to me so Ill explain: for some reason when I try to type myDoc.Shape, it is not suggested to me (a bad sign, here I am using template_word instead of myDoc).

Ill put my full code down here as well as the screenshot of it not showing up. The code gives me the error:

Here is the code

Sub mr_excel_pasting_image()


Dim word_template_name As String
Dim template_word As Word.Application

word_template_name = "C:\Marco\work\Excel stuff\Learning excel\Example template.dotm"

'Open up the word document to paste the image into
Set template_word = CreateObject("Word.Application")
template_word.Visible = True
template_word.Documents.Add Template:=word_template_name, NewTemplate:=False, DocumentType:=0

'Copy the selection of cells (this could also be just a regular image). This part shouldnt be super important
ActiveWorkbook.Worksheets("Informes").Range("C40:G43").CopyPicture _
        Appearance:=xlScreen, Format:=xlPicture

'Paste the selection of cells as an image. Up to here the code works perfectly.
With template_word.Selection
.Goto what:=wdGoToBookmark, Name:="Bookmark_name"
.PasteSpecial
End With

'Here I try and implement your code, with some name substitution of course. 
Dim Sh As Word.Shape
    With template_word.Shap
'        Set Sh = .Shapes(.Shapes.Count)
'    End With

Sh.LockAspectRatio = True
Sh.Width = Sh.Width * 1.5
    
'Position Shape
Sh.Top = Sh.Top + 25
Sh.Left = Sh.Left + 25

End Sub