Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formating a linked Object with a macro (Word 97)

    I wrote a macro that uses the Paste Special and links an Excel object to my Word file. I want to change the size to fit better but cannot access the Format Object button while using the macro recorder. Here is my code to paste the link. What I need to do is change the size (locking the aspect ratio and checking Relative to Picture Size) so that the width is 6.19 inches. This gives me an object, linked to Excel that fits the page.
    Selection.GoTo What:=wdGoToBookmark, Name:="Text1"
    Selection.Find.ClearFormatting
    With Selection.Find
    .Text = ""
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindAsk
    .format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
    wdInLine, DisplayAsIcon:=False
    I intend to add code at the beginning that will protect and code at the end that will unprotect.

    Thanks.

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

    Re: Formating a linked Object with a macro (Word 97)

    If you select the object after inserting it, you can use

    With Selection.ShapeRange
    ' Use one or more instructions like this:
    .Height = InchesToPoints(3)
    .Width = InchesToPoints(4)
    .ScaleHeight = 100
    .ScaleWidth = 100
    End With

    Instead of Selection.ShapeRange, you can also use something like ActiveDocument.InlineShapes(1) (if your object is the first shape in the document)

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating a linked Object with a macro (Word 97)

    At .ScaleHeight = I substituted 97 for 100 and received the error message "Argument not optional". Same using 100.

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Formating a linked Object with a macro (Word 97)

    The required second parameter is True or False, depending on whether you want to scale relative to the current size or the original size.

    StuartR
    Attached Images Attached Images
    • File Type: gif x.gif (8.2 KB, 0 views)

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating a linked Object with a macro (Word 97)

    I can't see your example. When you say True or False could you give me the syntax?

    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
    wdInLine, DisplayAsIcon:=False
    With Selection.ShapeRange
    ' Use one or more instructions like this:
    .Height = InchesToPoints(3.16)
    .Width = InchesToPoints(6.19)
    .ScaleHeight = 100
    .ScaleWidth = 100

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Formating a linked Object with a macro (Word 97)

    If you put the VBE Cursor over the word ScaleHeight and type F1 then it will take you to the help screen I showed you, clicking on the <font color=blue> Example </font color=blue> would show you some examples.

    In this case I think you want.

    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
    wdInLine, DisplayAsIcon:=False
    With Selection.ShapeRange
    ' Use one or more instructions like this:
    .Height = InchesToPoints(3.16)
    .Width = InchesToPoints(6.19)
    .ScaleHeight = 100, True
    .ScaleWidth = 100, False

    StuartR

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating a linked Object with a macro (Word 97)

    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
    wdInLine, DisplayAsIcon:=False
    With Selection.ShapeRange
    ' Use one or more instructions like this:
    .Height = InchesToPoints(3.16)
    .Width = InchesToPoints(6.19)
    .ScaleHeight = 100, True
    .ScaleWidth = 100, False

    Still get compile error after last two lines "Expected end of statement".

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Formating a linked Object with a macro (Word 97)

    Sorry, I just copied the code without checking the syntax. You don't need the = sign. Try

    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
    wdInLine, DisplayAsIcon:=False
    With Selection.ShapeRange
    .ScaleWidth 100, False
    End With

    StuartR

    And here is an example that I have actually tested and found to work...

    Sub TestIt()
    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
    wdFloatOverText, DisplayAsIcon:=False
    With Selection.ShapeRange(1)
    .ScaleWidth 0.7, True
    End With

  9. #9
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating a linked Object with a macro (Word 97)

    It works without errors but it now is left-aligned whereas when I paste it manually into a centered aligned text field its center aligned. It should be centered. Here is my code. Can I do something about its position? Thanks for your time.
    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:= _
    wdFloatOverText, DisplayAsIcon:=False
    With Selection.ShapeRange(1)
    .Height = InchesToPoints(3.09)
    .Width = InchesToPoints(6#)
    .ScaleHeight 0.94, True
    .ScaleWidth 0.95, True

  10. #10
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Formating a linked Object with a macro (Word 97)

    If you want to control it with centre alignment then don't float the graphic. Place it as an inline picture and then the graphic will use the paragraph formatting you have chosen for it.

    <pre>Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, _
    Placement:=wdInLine, DisplayAsIcon:=False
    Selection.MoveLeft Unit:=wdCharacter, count:=1, Extend:=wdExtend
    With Selection.InlineShapes(1)
    .Height = InchesToPoints(3.09)
    .Width = InchesToPoints(6#)
    End With</pre>

    Andrew Lockton, Chrysalis Design, Melbourne Australia

  11. #11
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating a linked Object with a macro (Word 97)

    That worked. Thanks.

    I have posted several questions about spellcheck and downloaded the spell check file from the MVP web site. It worked after I saved it in my Crogram filesmicrosoft OfficeOfficeStartup folder but when I turn my computer off and on, there is no macro anywhere. The file is still where I saved it but the macro is gone. Do you have any ideas why this is happening and how I can save this spell checker so it will come up when I need it?

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating a linked Object with a macro (Word 97)

    If you have saved a template containing a module with the spell check macro in the startup folder, then it should be there. Is the template still in the startup folder? What makes you think the macro isn't there.

    If you press Alt+F8, you should be able to see the macro listed in the dialog box. However, you can't edit a global template (other than normal.dot) unless you actually OPEN the template (either using Windows Explorer & right clicking or by going through File/Open.

  13. #13
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating a linked Object with a macro (Word 97)

    Its there now and it seems to work. I saved it as a doc file because whenever I tried to save it as a dot file, the macro vanished. Am I alright now or is there something more I have to do. Also, when we go to Office 2000, will I need to do something else?

    Thanks.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating a linked Object with a macro (Word 97)

    You should save it as a template. When you have a macro in a document, it's available for that document only, & that's not what you want. After you save the template, put the template in the startup folder. Then:

    1. Open the template (not a document based on the template).
    2. Right click any toolbar & choose Customize.../Commands tab
    3. Under categories, scroll to macros
    4. You should see your macro on the right.
    5. Do one or more of the following:
    a. Drag it to a toolbar of your choosing
    b. Drag it to a menu.
    c. or click the keyboard button & assign a keyboard shortcut.

    Cheers,

  15. #15
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating a linked Object with a macro (Word 97)

    I'll try it. At home, I have Office 2000. The spell check macro checks only a portion of the documents. I'll do what you suggest tonight. Maybe that will help.

    At work, we are migrating to Office 2000 and I need this for about a dozen people who will be using a template.

    Thanks for your help. I'll let you know whether your suggestion works.

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
  •