Jan 14 2021 06:57 AM
How can I access Text in an Excel Autoshape with vba (see worksheet in attached file)?
Jan 14 2021 07:44 AM
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")
Jan 15 2021 02:35 AM
Hallo Hans,
thank you very much. I have found this method at several places in the internet. But, why does not work?
Jan 15 2021 06:02 AM
Do you get an error message? If so, what does it say?
Jan 15 2021 06:32 AM
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
Jan 15 2021 06:45 AM
The code works in the workbook that you attached. I'd have to see an example of where it fails.
Jan 15 2021 07:10 AM
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
Jan 15 2021 08:16 AM
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.
Jan 15 2021 08:36 AM
Jan 15 2021 09:31 AM
Hi,
thank you for the notice. The error message is "Der
angegebene Wert ist außerhalb des zulässigen Bereichs."
Jan 15 2021 11:56 AM
I did see the screenshot (and I understand German), but that doesn't help. I'd need to see (a copy of) the workbook.
Jan 15 2021 12:45 PM
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.
Jan 15 2021 01:35 PM
Jan 15 2021 02:04 PM
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
Jan 16 2021 12:20 AM
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
Jan 16 2021 02:57 AM
Does that happen in your sample workbook? If so, where should I look?