Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA: Insert pic into document (Word 2000)

    Hello,
    I am new to this forum and I apologize for cross-post. I should have posted it here, didn't notice there's a special forum for VBA...

    I am having a nightmare with inserting pics from Excel into Word.
    I use copy-create text box - paste as picture method (using Automation) because I do not need to work with embedded Excel object.

    When I record the macro the picture adjusts its size according to the size of the former created textbox. But when I run the macro, the text box grows one-and-a-half page with the picture.

    I am asking this because I am struggling to get my pictures into Word how I want and where I want.
    I know that Word is not a DTP App but anyway, if I manage to make things look OK manually, VBA must do it as well.
    I'd be grateful for any hints.

    best regards
    Martin
    Regards,
    Martin

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA: Insert pic into document (Word 2000)

    I should be able to help you, but I'm having trouble understanding exactly what you want to do. Is this the correct scenario:
    1. <LI>You have an Excel worksheet open
      <LI>You have a Word document open
      <LI>You have a textbox in your Word document with the insertion point in the textbox
      <LI>You want to select a portion of your worksheet and then run an excel macro
      <LI>The macro copies the selection from Excel
      <LI>The macro pastes the selection into the Word textbox as a picture
      <LI>The textbox does not grow to fit the selection (the norm), rather the spreadsheet picture shrinks to fit in the textbox.
    Is this correct? Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #3
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA: Insert pic into document (Word 2000)

    Hello Sam,

    1), 2) 3) yes, through a code run from Excel, using Automation
    4) Yes, I insert a textbox trough this code, can be also another object that is appropriate
    5) Yes, Range(xxx).Copy
    6) Yes,
    With wrdApp
    .ActiveDocument.Shapes.AddTextbox(msoTextOrientati onHorizontal, 515.7, _
    137.5, 261#, 162#).Select
    .Selection.ShapeRange.TextFrame.TextRange.Select
    .Selection.Collapse
    .Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
    Placement:=wdInLine, DisplayAsIcon:=False
    End With

    (recorded, I am not very familiar with Word Object model)

    7) opposite, the textbox grows after applying the paste method, I'd like not to let it grow, but to adjust the picture to its original size;

    Just to understand - my code copies text, tables and charts from excel into word. I have managed everything, but the pasted tables/charts (as pictures) are giving me a hard time.
    The sequence is run from excel; it adds a new page, heading, some text and a picture on a determined place. That is why I insert a frame (TextBox) first and then insert text.

    Thank you

    Martin
    Regards,
    Martin

  5. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA: Insert pic into document (Word 2000)

    Well, I almost understand. More questions:
    1) How does the macro know what to copy from Excel?
    2) Unless the textbox and the Excel stuff happen to be the same size, either the textbox has to grow to fit the Excel picture or the Excel picture has to shrink to fit into the textbox. Which do you want?
    3) Is the Excel picture the only thing that goes in the textbox?

    Thanks! Got to go to sleep now, I'll work on it in the morning. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA: Insert pic into document (Word 2000)

    Hello again,
    sorry to be off for today.
    1) I use copy of a range in Excel (I mean copy method in VBA)
    2) I would like to insert the textbox on an exact place and size it exactly and then SHRINK the picture to fit it and format the textbox so that the text of other paragraphs (in the document, not in the textbox) is Wrapped-Square

    3) Yes, always, I guess

    How do I determine the size of the textbox? When I record a macro, it writes something like this (cleaned a bit):
    I would like that the textbox is aligned with the RIGHT page border, on the left then come the paragraphs. (This works for me already, only the inserted pictures are giving me hell...)

    Thank you so much

    G'night
    Martin
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 14.1.2004 by Martin
    '
    ActiveDocument.Shapes.AddTextbox(msoTextOrientatio nHorizontal, 551.7, _
    101.5, 234#, 234#).Select
    Selection.ShapeRange.TextFrame.TextRange.Select
    Selection.Collapse
    Selection.ShapeRange.Select
    Selection.ShapeRange.WrapFormat.Type = wdWrapSquare


    End Sub
    Regards,
    Martin

  7. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA: Insert pic into document (Word 2000)

    I don't think that you want to use the textbox approach. On my XP machine, I can pastespecial a range into the textbox, but not a chart. In order not to have two cases, I just dispensed with the textbox. Unfortunately, I do not have an Office 2K system that I know of, so this code may not work correctly. If not, reply back and someone will fix it for Office 2000. Also, you probably know, but for others sake, you need to use the Tools | References menu to add the Microsoft Word Object model to your XL VBA project. Finally, you will need to tweak the numbers for your margins. Thanks for the fun challange! --Sam
    <pre>Option Explicit

    Sub cpToWord()
    Dim isSelectionRange As Boolean
    Dim wrdApp As Word.Application
    ' Copy the Excel selection
    With Excel.Application
    If .Selection Is Nothing Then
    MsgBox "Exiting, nothing is selected"
    Exit Sub
    End If
    If TypeOf .Selection Is Range Then
    isSelectionRange = True
    ElseIf TypeOf .Selection Is ChartArea Then
    isSelectionRange = False
    Else
    MsgBox "Exiting, unsupported selection"
    Exit Sub
    End If
    .Selection.Copy
    End With
    ' Switch to Word
    Set wrdApp = GetObject(, "Word.Application")
    If wrdApp Is Nothing Then
    MsgBox "Exiting, the Word document is not open"
    Exit Sub
    End If
    With wrdApp
    .Activate
    ' Add the header & text to the end of the document
    .Selection.EndKey unit:=wdStory
    .Selection.InsertBreak Type:=wdPageBreak
    .Selection.TypeText Text:="Test Excel Picture"
    .Selection.HomeKey unit:=wdLine, Extend:=wdExtend
    .Selection.Style = .ActiveDocument.Styles("Heading 1")
    .Selection.EndKey unit:=wdStory
    .Selection.TypeParagraph
    .Selection.TypeParagraph
    .Selection.TypeText Text:= _
    "This is some text that would describe the Excel " & _
    "picture. Now, it will show that the picture is " & _
    "square wrapped and on the left side."
    ' Add the Excel selection as an Enhanced Metafile Picture
    .Selection.PasteSpecial link:=False, _
    DataType:=wdPasteEnhancedMetafile, _
    Placement:=wdInLine, DisplayAsIcon:=False
    ' Make the picture a reasonable size and location
    With .Selection.ShapeRange
    .Line.Visible = isSelectionRange
    .LockAnchor = True
    .Width = 200#
    If .Height > 130# Then .Height = 130#
    .WrapFormat.Type = wdWrapSquare
    .Left = 450# - .Width
    .Top = 20#
    End With
    ' Cleanup
    .Selection.EndKey unit:=wdStory
    End With
    Excel.Application.CutCopyMode = False
    Set wrdApp = Nothing
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #7
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA: Insert pic into document (Word 2000)

    Edited by HansV - inserted <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to show indented code.

    Sam,
    thank you so much. It's this little piece of code, does does the work!:

    Make the picture a reasonable size and location<pre> With .Selection.ShapeRange
    .Line.Visible = isSelectionRange
    .LockAnchor = True
    .Width = 100#
    If .Height > 130# Then .Height = 130#
    .WrapFormat.Type = wdWrapSquare
    .Left = 450# - .Width
    .Top = 0#
    End With</pre>

    I've noticed that the picture is then aligned to the right border of the document, exactly what I want. I can't find the trick how the picture knows where's the border. Or is it by default when its size does not exceed the size of the page?

    Alas, I have to import charts also. Is the only problem in different type of selection? If yes, then it should be no problem for me.

    You helped me a lot already, thank you a milion!

    By the way, I am using late binding, so no need for references (in your example obligatory, though).

    Best regards

    Martin
    P.S. How do I paste the code into the post properly in Woody's? Do I have to convert it to HTML first and then paste, or is there some icon that didn't spot here?
    Regards,
    Martin

  9. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: VBA: Insert pic into document (Word 2000)

    <hr>P.S. How do I paste the code into the post properly in Woody's? Do I have to convert it to HTML first and then paste, or is there some icon that didn't spot here? <hr>
    The 1-Click TagPanel contains two tags you can insert:
    1. <LI>The PRE button inserts a <!t>[pre]<!/t> ... <!t>[/pre]<!/t> pair. Text between these tags will be displayed "as is", including spaces, and without automatic line-wrapping.
      <LI>The TAB button inserts a <!t>[tab]<!/t> tag. This inserts 4 spaces in the displayed text. For example,

      Sub Test()
      <!t>[tab]<!/t>Beep
      End Sub

      will be displayed as

      Sub Test()
      Beep
      End Sub
    HTH

  10. #9
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA: Insert pic into document (Word 2000)

    <pre>Sub Thanks()
    MsgBox ("Thank you, Hans" & vbLf & vbLf & "Martin")
    End Sub</pre>

    Regards,
    Martin

  11. #10
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA: Insert pic into document (Word 2000)

    Hello again Sam,
    first of all I must once more to express my thanks, as your help has really kicked me forward.

    I have actually found out how you determine the position of the picture (# is for points?). My favorite method is InchesToPoints though as I do not have a good feeling how many points I need.
    My sizing is still a little bit trial-and-error as I still can't find out very well where the page borders are, but it'll be fine.

    Now, to my problem:
    I have noticed that when inserting a larger range from Excel as picture, the range gets truncated. Then I discovered that when I paste it as Bitmap, it does not get truncated, but the code can't set all the other properties as by Metafile.

    So now I paste as bitmap, copy, paste as metafile again, erase bitmap.

    This is not very efficient way, is it?

    Thanks
    Martin

    I post the code that chokes when I try to set Ptoperties of the picture. I should add that I have LandScape Page orientation.
    <pre> With wrdApp
    .Selection.PasteSpecial Link:=False, _
    DataType:=wdPasteBitmap, _
    Placement:=wdFloatOverText, _
    DisplayAsIcon:=False

    ' Make the picture a reasonable size and location
    With .Selection.ShapeRange
    .Line.Visible = True
    .LockAnchor = True
    .Left = InchesToPoints(0)
    .Width = InchesToPoints(10.3) - .Left
    If .Height > InchesToPoints(5.5) Then .Height = InchesToPoints(5.5)
    .WrapFormat.Type = wdWrapTopBottom
    .WrapFormat.Side = wdWrapBoth
    .Top = 10#
    End With
    ' Cleanup
    .Selection.EndKey unit:=wdStory
    End With</pre>

    Regards,
    Martin

  12. #11
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA: Insert pic into document (Word 2000)

    Ok, now this seems to work:
    <pre>.ActiveDocument.Shapes("Picture 2").Select
    With .Selection.ShapeRange
    .Line.Visible = True
    .LockAnchor = True
    .Left = InchesToPoints(0)
    .Width = InchesToPoints(10.3) - .Left
    If .Height > InchesToPoints(5.5) Then .Height = InchesToPoints(5.5)
    .WrapFormat.Type = wdWrapTopBottom
    .WrapFormat.Side = wdWrapBoth
    .Top = 10#
    End With</pre>


    I do not like the hard-coded name, though.
    Martin
    Regards,
    Martin

  13. #12
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: VBA: Insert pic into document (Word 2000)

    If I follow the code (a major caveat), the last picture added will be the one you want to operate on, so something like:

    With ActiveDocument..Shapes(ActiveDocument..Shapes.Coun t).ShapeRange
    .Line.Visible = True
    'etc. ...

    should work.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  14. #13
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA: Insert pic into document (Word 2000)

    <hr>If I follow the code (a major caveat), the last picture added will be the one you want to operate on<hr>

    Yes John, that's exactly what I do. I have a XL sheet with sequence which file to open, what to copy and what is the paragraph formatting. The doc starts from the scratch, only based on my custom template. each line in my control sheet actually represents one paragraph in the on-the-fly created document.

    That's a SUPER-TIP of yours!
    Thanks
    Martin
    Regards,
    Martin

  15. #14
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA: Insert pic into document (Word 2000)

    Just checked, it works a treat, I just had to reduce it to <pre>With .ActiveDocument.Shapes(ActiveDocument.Shapes.Count )</pre>


    Thanks
    Martin
    Regards,
    Martin

  16. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA: Insert pic into document (Word 2000)

    Actually should be
    <pre>With .ActiveDocument.Shapes(.ActiveDocument.Shapes.Coun t)</pre>

    Notice that I added a dot before the second ActiveDocument. Your code works OK because there is no ActiveDocument method in the Excel model, but the teacher in me makes me want to be consistent.

    Other answers/coments:
    > Alas, I have to import charts also. Is the only problem in different type of selection? If yes, then it should be no problem for me
    My code also worked for charts, both charts that were embedded in a worksheet and chart sheets. The only reason that I kept a flag was to keep from drawing the extra box around a chart:
    <pre> .Line.Visible = isSelectionRange </pre>


    My code will whine "unsupported selection" however, if you have just a portion of the chart selected, such as the axis. You can deal with these on a case-by-case basis using .parent or .parent.parent to get back to the chart, but I didn't think that it was worth the trouble.

    > I can't find the trick how the picture knows where's the border
    It dosn't, I just twiddled with the Left & Top parameters until it looked good to me.

    > (# is for points?)
    No, it's just a stupid leftover from Basic. The compiler changes 1.0 to 1#. The # is a type-declaration character for Double.

    <hr>I have noticed that when inserting a larger range from Excel as picture, the range gets truncated. Then I discovered that when I paste it as Bitmap, it does not get truncated, but the code can't set all the other properties as by Metafile.

    So now I paste as bitmap, copy, paste as metafile again, erase bitmap.

    This is not very efficient way, is it?
    <hr>
    It may be the only way. If it works, rejoice and document the crazyness in your code so that when you come back to it, you'll remember.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •