Thursday
How can I access Text in an Excel Autoshape with vba (see worksheet in attached file)?
Thursday
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")
Friday
Hallo Hans,
thank you very much. I have found this method at several places in the internet. But, why does not work?
Friday
Do you get an error message? If so, what does it say?
Friday
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
Friday
The code works in the workbook that you attached. I'd have to see an example of where it fails.
Friday
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
Friday
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.
Friday
Friday
Hi,
thank you for the notice. The error message is "Der
angegebene Wert ist außerhalb des zulässigen Bereichs."
Friday
I did see the screenshot (and I understand German), but that doesn't help. I'd need to see (a copy of) the workbook.
Friday
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.
Friday
Friday
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
yesterday
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
yesterday
Does that happen in your sample workbook? If so, where should I look?