Formatting Excel object in Word 2010 VBA

ArnolddG · #1 03-04-2014, 06:18 AM

With little knowledge of Excel VBA, I am trying to format the size of an Excel object in Word 2010. The macro recorder does not allow me to record right mouse options in Word and I cannot find the object properties in Word VBA.

I would like to change the size of the Excel object to 60% of the inserted one via VBA code.

Can anyone point me in the right direction.

Thanks in advance...

Arnold

macropod · #2 03-04-2014, 03:02 PM

Since your Excel object could have been inserted either in-line or floating, the following macro caters for both:

Code:

Sub ReSize()
With Selection
  If .InlineShapes.Count <> 0 Then
    With .InlineShapes(1)
      .LockAspectRatio = True
      .Height = .Height * 0.6
    End With
  End If
  If .ShapeRange.Count <> 0 Then
    With .ShapeRange(1)
      .LockAspectRatio = True
      .Height = .Height * 0.6
    End With
  End If
End With
End Sub

As coded, the macro works with a selected object (it doesn't have to be an Excel one). If you're inserting the object via code, there would be no need to select it; instead you could set a reference to it and use that.

ArnolddG · #3 03-05-2014, 01:45 AM

Thanks for your help Paul,

It works for me.

I had to add the 'width' also, to keep the LockAspectRatio, but that is little extra code.
However it fails to run the line with:

Code:

If .ShapeRange.Count <> 0 Then

There is an Run-time error '4198' : Command failed,
In the immediate window the code

Code:

? Selection.ShapeRange.Count

gives the same error, but as far as I can see now I seem to be able to skip this part of the code completely. Am I right ?
Arnold

macropod · #4 03-05-2014, 03:58 AM

I don't know why you'd get that error but, since it's apparent you're working with an in-line object, you can indeed delete that portion of the code.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
formatting text in a word TextBox Object	PeterSent	Word	3	08-31-2013 09:30 AM
Linking an Excel object into Word	cncolom	Word	1	03-29-2013 03:01 PM
Excel worksheet as OBJECT in WORD not displaying	mohsin	Word	1	03-27-2012 10:43 PM
Excel VBA: How to add a reference to Microsoft Word Object Library?	tinfanide	Excel Programming	7	12-12-2011 05:21 AM
Excel File as Linked Object in Word	jfitch	Word	0	03-18-2010 11:44 AM