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.

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