copying a snapshot of excel range into word using excel vba

[MazAb]

Hi everyone,
I'm trying to write a code for my spreadsheet in excel vba.

this code takes a snapshot of a range of cells in my spreadsheet (say "B2:F10") and pastes it into a blank word doc as a picture. (see below my code).

however I would like to be able to select the insert position and change the final size of the pic in the word doc using my code in excel.

can anyone pls help me.

regards

Dim objWord, objDoc As Object
ActiveWindow.View = xlNormalView
Range("B2:F10").Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add
objWord.Visible = True
objWord.Selection.Paste

[mana]

Where is the position that you want to insert a picture?

[macropod]

See, for example: https://www.mrexcel.com/forum/genera...ml#post4788205

[MazAb]

Thanks Guys for your replies.

now I have a new problem. I have a word document template (report) and an excel spreadsheet (calcs). I designed the word doc template and allocated a few drawing canvases (they are basically similar to an empty box where you can fit a picture in them and you can find them in insert tab ---> shapes). these canvases are the fixed locations with fixed sizes in my report that I want to insert a snapshot of my excel ranges into them.

and certainly I want to do all these procedures using vba excel,
can anyone, please tell me how I can directly take a snapshot of excel ranges (say "B2:F10") and fit them into the canvas in the word doc.

I hope that my explanation is clear and doesn't make anyone confused.

cheers
Maz

[macropod]

can anyone, please tell me how I can directly take a snapshot of excel ranges (say "B2:F10") and fit them into the canvas in the word doc.

The normal way of constraining an image's size is to set its height & width using Word VBA code. The Word VBA code might look like:

Sub Demo()
Dim wdRng As Range
Set wdRng = ActiveDocument.Range
With wdRng
  .Collapse wdCollapseEnd
  .Paste
  With .InlineShapes(1)
    .LockAspectRatio = True
    .Width = InchesToPoints(4.5)
  End With
End With
End Sub

[MazAb]

thanks Paul,

your code gave me a few good clues. however, I'm still struggling.
to clarify my intent, I've attached a snapshot of a page in my word document.

Capture.JPG

the two red rectangles are drawing canvas (insert --> shapes --> new drawing canvas). I named them "BigTable" and "SmlTable".
I want to write a code in my Excel spreadsheet to take a snapshot of a range of cells (B159:O209) and fit them inside the "BigTable" canvas and take another snapshot from range (Q214:X218) and fit it inside the "SmlTable".
I've manually taken the snapshots and inserted them into the canvases to show you the end result that I want.
thanks in advance for your help.

Capture2.JPG

[macropod]

Without knowing what you're struggling with, it's a bit hard to say what you might try. That said, one of the issues you'll have to deal with is the drawing canvas resizing. To handle that, you'd need to capture its dimensions before pasting, then re-set them to that afterwards. A simpler method is to create a borderless single-cell table with a fixed row height and column width, then paste into that. The pasted content should automatically re-scale to fit the available space.

[MazAb]

the part I'm struggling is how to control the canvas properties from excel and paste the snapshot from clipboard to fit inside the canvas.

the idea of putting a borderless single cell table sounds good but I'll still struggle to paste the pic inside the table in word doc from excel.

I think my explanation is confusing. in a nutshell, I don't know how to write a code in excel to paste a snapshot from clipboard inside the single cell table in the word doc as you suggested.

cheers
Maz

[macropod]

the part I'm struggling is how to control the canvas properties from excel and paste the snapshot from clipboard to fit inside the canvas.

You don't. You use Word VBA to get the canvas dimensions, then do your pasting, after which you again use Word VBA to resize the canvas.

the idea of putting a borderless single cell table sounds good but I'll still struggle to paste the pic inside the table in word doc from excel

For that, the Word VBA would look like:

Sub Demo() 
    Dim wdRng As Range 
    Set wdRng = ActiveDocument.Tables(1).Cell(1,1).Range 
    With wdRng 
        .Collapse wdCollapseStart 
        .Paste 
    End With 
End Sub

where the 1 in Tables(1) tells Word which table to paste into.

[MazAb]

thanks a lot Paul. it worked perfect.
I also have another question which is related to your reply to one of the old posts I found. (how to delete the last page in word vba).

I think I have to close this post and start a new one with a different title. I would appreciate if you reply to my new post.

regards
Maz