Macro to have equal size charts and picture in MS Word

U

ucanalways

I am trying to write a macro that will find all excel charts and
pictures in a word document and resize it uniformly (equal size and
aligned in the center of the document.

To test, I have one excel chart and a picture in page1. Command button
is also in page1.

Iused F8 to check what's happening in my code and I found that the
variable shp takes the value "Nothing". So, the for loop is not being
executed. Any idea what am I doing wrong? Please let me know.

Currently, I have the following code. Please note that the following
subroutine is in MS WORD and not in excel.


Sub test()
Dim shp As Shape
Dim wsactive As Document

Set wsactive = ActiveDocument

For Each shp In wsactive.Shapes

If shp.Type = msoChart Then
shp.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
shp.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
End If

If shp.Type = msoDiagram Then
shp.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
shp.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
End If
Next shp

End Sub
 
U

ucanalways

I am trying to write a macro that will find all excel charts and
pictures in a word document and resize it uniformly (equal size and
aligned in the center of the document.

To test, I have one excel chart and a picture in page1. Command button
is also in page1.

Iused F8 to check what's happening in my code and I found that the
variable shp takes the value "Nothing". So, the for loop is not being
executed. Any idea what am I doing wrong? Please let me know.

Currently, I have the following code. Please note that the following
subroutine is in MS WORD and not in excel.

Sub test()
Dim shp As Shape
Dim wsactive As Document

Set wsactive = ActiveDocument

For Each shp In wsactive.Shapes

If shp.Type = msoChart Then
shp.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
shp.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
End If

If shp.Type = msoDiagram Then
shp.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
shp.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
End If
Next shp

End Sub

Ok! Got the macro to accomplish my requirement.

I am giving the macro below. Hopefully it is useful for others who
come across a similar situation in future. But it took solid 1 hour to
figure out though!

Sub test()

ActiveDocument.Shapes.SelectAll
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 2000
Selection.ShapeRange.Width = 300
Selection.ShapeRange.Left = wdShapeCenter

End Sub
 
U

ucanalways

Ok! Got the macro to accomplish my requirement.

I am giving the macro below. Hopefully it is useful for others who
come across a similar situation in future. But it took solid 1 hour to
figure out though!

Sub test()

ActiveDocument.Shapes.SelectAll
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 2000
Selection.ShapeRange.Width = 300
Selection.ShapeRange.Left = wdShapeCenter

End Sub- Hide quoted text -

- Show quoted text -

Oops. The above code works only when I have iamges in the document. I
get an error (Cannot select mulitple canvas at the same time) when I
have canvas (powerpoint slide) in the document. How to select only the
pictures in a document and align it in the middle?

I am looking for something like

if activedocument.shapes.type = msoimage ???????

This is driving me nuts for almost an hour. Anyone have any idea on
this? Please help. Thanks
 
T

Thulasiram

Oops. The above code works only when I have iamges in the document. I
get an error (Cannot select mulitple canvas at the same time) when I
have canvas (powerpoint slide) in the document. How to select only the
pictures in a document and align it in the middle?

I am looking for something like

if activedocument.shapes.type = msoimage ???????

This is driving me nuts for almost an hour. Anyone have any idea on
this? Please help. Thanks

Guys, can anyone please help me solve this problem? I am stuck with
this problem from Friday..
 
U

ucanalways

Oops. The above code works only when I have iamges in the document. I
get an error (Cannot select mulitple canvas at the same time) when I
have canvas (powerpoint slide) in the document. How to select only the
pictures in a document and align it in the middle?

I am looking for something like

if activedocument.shapes.type = msoimage ???????

This is driving me nuts for almost an hour. Anyone have any idea on
this? Please help. Thanks- Hide quoted text -

- Show quoted text -

Guys, can anyone please help me solve this problem? I am stuck with
this problem from Friday..
 
P

Peter T

You would probably have got an answer by now if you had asked in a Word
newsgroup.

Following worked from me with an Excel chart inserted from Insert/Object and
a Picture from Insert/Picture/File:

Sub Test() ' code in in Word
Dim dc As Document
Dim ilShp As InlineShape
Dim ngNewWidth As Single
Dim ndFactor As Double

ngNewWidth = 300

Set dc = ActiveDocument
For Each ilShp In dc.InlineShapes
With ilShp
ndFactor = ngNewWidth / .Width
..Height = .Height * ndFactor
..Width = ngNewWidth
End With
Next

End Sub

As written width is set to a pre-determined value and aspect ratio is
maintained, but adapt as required.

Regards,
Peter T
 
U

ucanalways

You would probably have got an answer by now if you had asked in a Word
newsgroup.

Following worked from me with an Excel chart inserted from Insert/Object and
a Picture from Insert/Picture/File:

Sub Test() ' code in in Word
Dim dc As Document
Dim ilShp As InlineShape
Dim ngNewWidth As Single
Dim ndFactor As Double

ngNewWidth = 300

Set dc = ActiveDocument
For Each ilShp In dc.InlineShapes
With ilShp
ndFactor = ngNewWidth / .Width
.Height = .Height * ndFactor
.Width = ngNewWidth
End With
Next

End Sub

As written width is set to a pre-determined value and aspect ratio is
maintained, but adapt as required.

Regards,
Peter T

Peter, really appreciate your reply. I posted the same question to the
following Word group as I was able to find only that WORD forum.
Unfortunately, I did not get any reply.

http://groups.google.com/group/microsoft.public.word.docmanagement/topics?hl=en&lnk=gschg

Do you know any other WORD forum? If yes, please let me know

As your code resizes the command button I have in the page, I
currently have something like this

If ilShp.Type = wdInlineShapePicture Then
With.......

but .Type = wdInlineShapePicture does resize the excel charts. So, can
you please let me know the macro to resize the pictures and charts but
not the command buttons or option buttons in the document?

How to center align these inlineshapes? Please let me know this too.

Thanks and looking forward to hear from you.
 
U

ucanalways

Peter, really appreciate your reply. I posted the same question to the
following Word group as I was able to find only that WORD forum.
Unfortunately, I did not get any reply.

http://groups.google.com/group/microsoft.public.word.docmanagement/to...

Do you know any other WORD forum? If yes, please let me know

As your code resizes the command button I have in the page, I
currently have something like this

If ilShp.Type = wdInlineShapePicture Then
With.......

but .Type = wdInlineShapePicture does resize the excel charts. So, can
you please let me know the macro to resize the pictures and charts but
not the command buttons or option buttons in the document?

How to center align these inlineshapes? Please let me know this too.

Thanks and looking forward to hear from you.- Hide quoted text -

- Show quoted text -

Peter, I was able to filter the command button selection by having
something like this

If Not ilShp.Type = wdInlineShapeOLEControlObject Then
With....

So, I am now left with center aligning the shapes. Please let me know
the macro for this.. Thanks
 
P

Peter T

Peter, I was able to filter the command button selection by having
something like this

If Not ilShp.Type = wdInlineShapeOLEControlObject Then
With....

So, I am now left with center aligning the shapes. Please let me know
the macro for this.. Thanks

To cater for even more .Types you could use this approach -

Select case ilShp.Type
Case wdInlineShapeOLEControlObject ' add any more to skip here separated
with commas
Case wdInlineShapeEmbeddedOLEObject, wdInlineShapePicture
'process these
Case Else
' do any others here perhaps a different way
End Select

I can't find the Left property either, I can only assume it will mean
adjusting the left margin for these inline shapes then resetting. But I
might be completely wrong, not sure if this is the best group but try this
one -

microsoft.word.vba.general

Regards,
Peter T
 
U

ucanalways

Peter, I was able to filter the command button selection by having
something like this

If Not ilShp.Type = wdInlineShapeOLEControlObject Then
With....

So, I am now left with center aligning the shapes. Please let me know
the macro for this.. Thanks- Hide quoted text -

- Show quoted text -

Peter, the following code does the center align. Hopefully, it works
consistently

For Each ilShp In dc.InlineShapes

If Not ilShp.Type = wdInlineShapeOLEControlObject Then
ilShp.Select
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
End If

Next
 
P

Peter T

Peter, the following code does the center align. Hopefully, it works
consistently

For Each ilShp In dc.InlineShapes

If Not ilShp.Type = wdInlineShapeOLEControlObject Then
ilShp.Select
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
End If

Next

Good sleuthing!

To avoid the .Select's try this

ilShp.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter

Regards,
Peter T
 
U

ucanalways

Good sleuthing!

To avoid the .Select's try this

ilShp.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter

Regards,
Peter T- Hide quoted text -

- Show quoted text -

Nice! Thanks again for your assistance to solve my problem
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top