Using Excel VBA to create shape in Word
 

I am writing code in Excel that will create a report in Word. In one section,
I want to add a shape and send it to the back of a paragraph. The code works
fine when I run it in Word, but it doesn't work from my sub routine in Excel.
I obviously need to replace With "ActiveDocument" to something else but other
variables created earlier such as WdApp or WdDoc do not seem to work.

With ActiveDocument
.Shapes.AddShape Type:=msoShapeRectangle, _
Left:=60, Top:=120, Width:=500, Height:=150
.Shapes(1).Fill.ForeColor _
.RGB = RGB(Red:=205, Green:=216, Blue:=255)
.Shapes(1).Line.Visible = msoFalse
.Shapes(1).WrapFormat.AllowOverlap = True
.Shapes(1).WrapFormat.Side = wdWrapBoth
.Shapes(1).WrapFormat.Type = 3
.Shapes(1).ZOrder 5

End With

Using Excel VBA to create shape in Word
 

Ariel,
Yes, you need the Word application object to precede Word's objects. e.g.
With wdApp.ActiveDocument
or if appropriated declared
With WdDoc

Also though, you have the Word constants (wdWrapBoth, etc), those that begin
with "wd". You need to qualify those also, otherwise Excel will not which
library they are from. Or change them to use their numeric value instead.
The other constant that start with "mso" should be OK as these are from the
Office library that Excel already knows about.

NickHK

"Ariel" wrote in message
...
I am writing code in Excel that will create a report in Word. In one
section,
I want to add a shape and send it to the back of a paragraph. The code
works
fine when I run it in Word, but it doesn't work from my sub routine in
Excel.
I obviously need to replace With "ActiveDocument" to something else but
other
variables created earlier such as WdApp or WdDoc do not seem to work.

With ActiveDocument
.Shapes.AddShape Type:=msoShapeRectangle, _
Left:=60, Top:=120, Width:=500, Height:=150
.Shapes(1).Fill.ForeColor _
.RGB = RGB(Red:=205, Green:=216, Blue:=255)
.Shapes(1).Line.Visible = msoFalse
.Shapes(1).WrapFormat.AllowOverlap = True
.Shapes(1).WrapFormat.Side = wdWrapBoth
.Shapes(1).WrapFormat.Type = 3
.Shapes(1).ZOrder 5

End With

Using Excel VBA to create shape in Word
 

Perhaps

With WdApp.ActiveDocument
.Shapes.AddShape Type:=msoShapeRectangle, _
Left:=60, Top:=120, Width:=500, Height:=150
.Shapes(1).Fill.ForeColor _
.RGB = RGB(Red:=205, Green:=216, Blue:=255)
.Shapes(1).Line.Visible = msoFalse
.Shapes(1).WrapFormat.AllowOverlap = True
.Shapes(1).WrapFormat.Side = wdWrapBoth
.Shapes(1).WrapFormat.Type = 3
.Shapes(1).ZOrder 5

End With

or

With WdApp.ActiveDocument.Shapes _
.AddShape( Type:=msoShapeRectangle, _
Left:=60, Top:=120, Width:=500, Height:=150)
.Fill.ForeColor.RGB = _
RGB(Red:=205, Green:=216, Blue:=255)
.Line.Visible = msoFalse
.WrapFormat.AllowOverlap = True
.WrapFormat.Side = wdWrapBoth
.WrapFormat.Type = 3
.ZOrder 5

End With

--

Regards,
Tom Ogilvy


"Ariel" wrote in message
...
I am writing code in Excel that will create a report in Word. In one
section,
I want to add a shape and send it to the back of a paragraph. The code
works
fine when I run it in Word, but it doesn't work from my sub routine in
Excel.
I obviously need to replace With "ActiveDocument" to something else but
other
variables created earlier such as WdApp or WdDoc do not seem to work.

With ActiveDocument
.Shapes.AddShape Type:=msoShapeRectangle, _
Left:=60, Top:=120, Width:=500, Height:=150
.Shapes(1).Fill.ForeColor _
.RGB = RGB(Red:=205, Green:=216, Blue:=255)
.Shapes(1).Line.Visible = msoFalse
.Shapes(1).WrapFormat.AllowOverlap = True
.Shapes(1).WrapFormat.Side = wdWrapBoth
.Shapes(1).WrapFormat.Type = 3
.Shapes(1).ZOrder 5

End With

Using Excel VBA to create shape in Word
 

This worked perfectly! Thanks Nick.

"NickHK" wrote:

Ariel,
Yes, you need the Word application object to precede Word's objects. e.g.
With wdApp.ActiveDocument
or if appropriated declared
With WdDoc

Also though, you have the Word constants (wdWrapBoth, etc), those that begin
with "wd". You need to qualify those also, otherwise Excel will not which
library they are from. Or change them to use their numeric value instead.
The other constant that start with "mso" should be OK as these are from the
Office library that Excel already knows about.

NickHK

"Ariel" wrote in message
...
I am writing code in Excel that will create a report in Word. In one
section,
I want to add a shape and send it to the back of a paragraph. The code
works
fine when I run it in Word, but it doesn't work from my sub routine in
Excel.
I obviously need to replace With "ActiveDocument" to something else but
other
variables created earlier such as WdApp or WdDoc do not seem to work.

With ActiveDocument
.Shapes.AddShape Type:=msoShapeRectangle, _
Left:=60, Top:=120, Width:=500, Height:=150
.Shapes(1).Fill.ForeColor _
.RGB = RGB(Red:=205, Green:=216, Blue:=255)
.Shapes(1).Line.Visible = msoFalse
.Shapes(1).WrapFormat.AllowOverlap = True
.Shapes(1).WrapFormat.Side = wdWrapBoth
.Shapes(1).WrapFormat.Type = 3
.Shapes(1).ZOrder 5

End With

Using Excel VBA to create shape in Word
 

I missed the wdWrapBoth constant even though I looked for constants unique
to the Word Object model

If you have created a reference to the word object model, then you should
not need to qualify the constant. If you have not, then I would replace it
with its numeric value myself.

In this particular case, the value is zero, so it should work as written
(but this is coincidental).

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
Perhaps

With WdApp.ActiveDocument
.Shapes.AddShape Type:=msoShapeRectangle, _
Left:=60, Top:=120, Width:=500, Height:=150
.Shapes(1).Fill.ForeColor _
.RGB = RGB(Red:=205, Green:=216, Blue:=255)
.Shapes(1).Line.Visible = msoFalse
.Shapes(1).WrapFormat.AllowOverlap = True
.Shapes(1).WrapFormat.Side = wdWrapBoth
.Shapes(1).WrapFormat.Type = 3
.Shapes(1).ZOrder 5

End With

or

With WdApp.ActiveDocument.Shapes _
.AddShape( Type:=msoShapeRectangle, _
Left:=60, Top:=120, Width:=500, Height:=150)
.Fill.ForeColor.RGB = _
RGB(Red:=205, Green:=216, Blue:=255)
.Line.Visible = msoFalse
.WrapFormat.AllowOverlap = True
.WrapFormat.Side = wdWrapBoth
.WrapFormat.Type = 3
.ZOrder 5

End With

--

Regards,
Tom Ogilvy


"Ariel" wrote in message
...
I am writing code in Excel that will create a report in Word. In one
section,
I want to add a shape and send it to the back of a paragraph. The code
works
fine when I run it in Word, but it doesn't work from my sub routine in
Excel.
I obviously need to replace With "ActiveDocument" to something else but
other
variables created earlier such as WdApp or WdDoc do not seem to work.

With ActiveDocument
.Shapes.AddShape Type:=msoShapeRectangle, _
Left:=60, Top:=120, Width:=500, Height:=150
.Shapes(1).Fill.ForeColor _
.RGB = RGB(Red:=205, Green:=216, Blue:=255)
.Shapes(1).Line.Visible = msoFalse
.Shapes(1).WrapFormat.AllowOverlap = True
.Shapes(1).WrapFormat.Side = wdWrapBoth
.Shapes(1).WrapFormat.Type = 3
.Shapes(1).ZOrder 5

End With