MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need to Capture Shapes

  Asked By: Maddison    Date: Aug 10    Category: MS Office    Views: 1526

I have many Excel workbooks where someone created a flow-chart type layout using arrows and text boxes.

Does someone have a hint on how I can get the Shapes object to work so that I can select all the shapes and copy them? If I copy the shapes collection into Word, then I think I can get to each of the text boxes, and perform Find and Replace functions within the individual text boxes.

Any other thoughts?



3 Answers Found

Answer #1    Answered By: Burk Martin     Answered On: Aug 10

Here is some very simple code that will go through all the shapes  on
the activesheet trying to update the text.

Sub ReplaceShapeText()
' Find and Replace text  in shapes
Dim shpTemp As Shape
Dim strFindText As String
Dim strReplaceText As String
Dim intPos As Integer

strFindText = "abc"
strReplaceText = "xyz"

On Error Resume Next

For Each shpTemp In ActiveSheet.Shapes
intPos = 0
' case sensitive
intPos = InStr(1, shpTemp.TextFrame.Characters.Text,
If intPos > 0 Then
shpTemp.TextFrame.Characters.Text =
(shpTemp.TextFrame.Characters.Text, strFindText, strReplaceText, 1)
End If
End Sub

Answer #2    Answered By: Hubert Taylor     Answered On: Aug 10

Thanks for the code, but intPos is always 0. Any thoughts?

Answer #3    Answered By: Lurlene Fischer     Answered On: Aug 10

Did you check the content of shpTemp.TextFrame.Characters.Text?

Didn't find what you were looking for? Find more on Need to Capture Shapes Or get search suggestion and latest updates.