Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automating edit/paste special/format object (2000)

    Can someone give me some tips to get me started on writing a macro to the following task. I'm doing the same task quite often and it's getting tedious. The 'record macro' feature doesn't seem to work for this.

    I copy an Excel range to the clipboard. I want a macro (in Word) which does the following:
    edit/paste special/paste link/paste as excel worksheet object/, then format object/layout=in line with text/size = 100%h 100%w.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating edit/paste special/format object (2000)

    Try this:

    Sub PasteOLELink()
    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, _
    Placement:=wdInLine
    Selection.MoveLeft Extend:=wdExtend
    Selection.InlineShapes(1).Reset
    End Sub

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating edit/paste special/format object (2000)

    Thanks, this is very useful. However, the reset to 100% X 100% size scaling doesn't work. When doing this manually, I need to turn off the lock aspect ratio control. So I tried the following slight modification. However, I still need to do a manual reset after running this.

    Sub PasteOLELink()
    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, _
    Placement:=wdInLine
    Selection.MoveLeft Extend:=wdExtend
    With Selection.InlineShapes(1)
    .LockAspectRatio = False
    .Reset
    End With
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating edit/paste special/format object (2000)

    Does the following work?

    With Selection.InlineShapes(1)
    .Height = .Height / .ScaleHeight
    .Width = .Width / .ScaleWidth
    End With

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating edit/paste special/format object (2000)

    No. I've tried a few variants, but nothing seems to reset the height and width to 100%

    With Selection.InlineShapes(1)
    .Height = 100 * .Height / .ScaleHeight
    .Width = 100 * .Width / .ScaleWidth
    End With
    With Selection.InlineShapes(1)
    .ScaleHeight = 100
    .ScaleWidth = 100
    End With

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating edit/paste special/format object (2000)

    It seems then that VBA doesn't "know" what the original size of the object was. I'm afraid I don't have other suggestions. Sorry.

Posting Permissions

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