Access of Text in an Excel Autoshape with vba

[ber999]

How can I access Text in an Excel Autoshape with vba (see worksheet in attached file)?

[#2]

@ber999 

For example:

    Dim strText As String
    strText = ActiveSheet.Shapes(1).TextFrame2.TextRange.Text

If you know the name of the shape, you can use that: change Shapes(1) to Shapes("Flussdiagramm: Prozess 1")

[#3]

Hallo Hans,

thank you very much. I have found this method at several places in the internet. But, why does not work?

@Hans Vogelaar 

[#4]

@ber999 

Do you get an error message? If so, what does it say?

[#5]

Hi Hans,

thank you for the answer. It seems that some shapetypes have a different object model. I am looking for an object model for the Autoshapes.  @Hans Vogelaar 

[#6]

@ber999 

The code works in the workbook that you attached. I'd have to see an example of where it fails.

[#7]

Hi  Hans,

thank you very much. I have copied the Autoshape at the attached sheet from a worksheet with hundreds of different shapes. My vba code did not work there with the Autoshapes only.

I have found code for Powerpoint Autoshapes. The access method is different there (see below).

Dim shpTextArray() As Variant

Dim numShapes, numAutoShapes, i As Long



Set myDocument = ActivePresentation.Slides(1)

With myDocument.Shapes
    numShapes = .Count
    If numShapes > 1 Then
        numTextShapes = 0
        ReDim shpTextArray(1 To 2, 1 To numShapes)
        For i = 1 To numShapes
            If .Item(i).HasTextFrame Then
                numTextShapes = numTextShapes + 1
                shpTextArray(numTextShapes, 1) = .Item(i).Name
                shpTextArray(numTextShapes, 2) = .Item(i) _
                    .TextFrame.TextRange.Text
            End If
        Next
        ReDim Preserve shpTextArray(1 To 2, 1 To numTextShapes)
    End If
End With

 @Hans Vogelaar 

[#8]

Hi Hans,

this is the interesting part of the code I have used:

.

Dim exlShpOuter As Excel.Shape

.

.

Set exlWssrc=ActiveWorkbook.Worksheets.Item("AS")

.

For Each exlShpOuter In exlWsSrc.Shapes

Select Case exlShpOuter.Type
   Case Office.msoLine
   Case Office.msoGroup
 .

 .

  Case Else
.

vbaText = exlShpOuter.TextFrame2.TextRange.Text

.

.

The error messagebox  is attached.

@Hans Vogelaar 

[#9]

@ber999 

I don't see "the attached sheet"...

A screenshot won't help.

[#10]

Hi, 

thank you for the notice. The error message is "Der
angegebene Wert ist außerhalb des zulässigen Bereichs."

@ber999 

[#11]

@ber999 

I did see the screenshot (and I understand German), but that doesn't help. I'd need to see (a copy of) the workbook.

[#12]

Hi Hans,

I have tested the my code with the worksheet and the single Autoshape. And, it works. Consequently, the problem should be the structure of the worksheet. However, the worksheet in question is not for public use. So, I will try to find a solution.

@Hans Vogelaar 

[#13]

Hi Hans,

the attached file should reproduce the error.@ber999 

[#14]

@ber999 

You can test whether a shape has text: change

        vbaText = exlShpOuter.TextFrame2.TextRange.Text

to

        If exlShpOuter.TextFrame2.HasText Then
            vbaText = exlShpOuter.TextFrame2.TextRange.Text
        End If

[#15]

Hi Hans,

it works good. But, there must at least one shape without text exist which is not visible to me and not handled by the select cases instructions above else.
@Hans Vogelaar 

[#16]

@ber999 

Does that happen in your sample workbook? If so, where should I look?