Extracting text from shape RRS feed

  • Question

  • Hi,

    I am trying to return text from excel shape that was earlier created using below code. The only thing I've been able to do so far is to copy the shape.

    Hope someone can help.


    Dim myTextbox As Excel.Shape = ws.Shapes.AddOLEObject(ClassType:="Forms.TextBox.1", _
                                                                               Link:=False, _
                                                                                DisplayAsIcon:=False, _
                                                                                Left:=targetRange.Left, _
                                                                                Top:=targetRange.Top, _
                                                                                Width:=200, _
    With ws.OLEObjects(myTextbox.Name).Object
       .Multiline = True
       .Value = sCaptionResult
       .Font.Bold = False
       .FontSize = 10
       .ScrollBars = 3
       .ScrollBars = 2
    End With


    • Moved by Carl Cai Wednesday, September 17, 2014 2:43 AM (from related
    Tuesday, September 16, 2014 3:32 PM

All replies

  • Hi wes.r,

    I have helped you move this thread to Excel for Developers forum to get supports, since this issue is mainly related to using Excel module.

    Thanks for your understanding.



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 17, 2014 2:46 AM
  • Sub Macro1()
    ' Macro1 Macro


        Dim ws As Worksheet
        Set ws = Worksheets("Sheet1")
        MsgBox (ws.Shapes(1).TextFrame2.TextRange.Characters.Text)
    End Sub

    If I understand what you are trying to do.  Then the above should work though you may need to make some adjustments for the Worksheet name.   Also if you have multiple Shapes on the page, you might want to use the shape Name.   (If you select the Shape you'll see the name in the upper left hand side right under the ribbon and before the formula bar)   

    Using the shape name the code would look like


        Dim ws As Worksheet
        Set ws = Worksheets("Sheet1")
        MsgBox (ws.Shapes("Rounded Rectangle 1").TextFrame2.TextRange.Characters.Text)


    Wednesday, September 17, 2014 3:07 AM
  • Hi,

    1. I've tried what you suggested and here are the results.
    Console.WriteLine(ws.Shapes("Pictue 1").TextFrame2.TextRange.Characters.Text())
    I get an error, 'Conversion from string "Pictue 1" to type 'Integer' is not valid.'

    2. When I pass an index example below:
    I get en error, 'Object variable or With block variable not set.'

    3. I also tried this.
    Dim s As Excel.Shape = oXL.Sheets(myChart.SheetName).Shapes(1)  

    I get followint error, 'The specified value is out of range.'


    Thursday, September 18, 2014 4:44 PM