Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,
    i have a spreadsheet where i have a lot of shapes; i want the text of these shapes put into cells;
    what i have done so far is a macro to possible get the names of all the shapes into the cells, but i donīt know
    how to put the correspondent text of these shapes into some cells (from O3 on) and, if possible get from P3
    some text into the correspondent shapes.

    Your help would be appreciated,
    stefan

    ps: see my attached workbook
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try these macros. They assume that you have already filled column N with the names of the shapes.

    Code:
    Sub GetShapeText()
      ' Fill column O with the text of the shapes named in column N
      Dim r As Long
      Dim m As Long
      Dim shp As Shape
      m = Range("N" & Rows.Count).End(xlUp).Row
      On Error Resume Next
      For r = 3 To m
        Set shp = ActiveSheet.Shapes(Range("N" & r))
        Range("O" & r) = shp.TextFrame.Characters.Text
      Next r
    End Sub
    
    Sub SetShapeText()
      ' Set the text of the shapes named in column N to the value of column P
      Dim r As Long
      Dim m As Long
      Dim shp As Shape
      m = Range("N" & Rows.Count).End(xlUp).Row
      On Error Resume Next
      For r = 3 To m
        Set shp = ActiveSheet.Shapes(Range("N" & r))
        shp.TextFrame.Characters.Text = Range("P" & r)
      Next r
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thank You Hans!!!
    wish You some nice Christmas days in advance,
    stefan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Stefan_Sand View Post
    wish You some nice Christmas days in advance,
    And the same to you!

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sorry for one last question:

    the macros seem not to work with Excel 2007 (see workbook).
    am i that blind??


    stefan
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    When I open the workbook in Excel 2003 (I don't have Excel 2007), all shapes on Tabelle1 (2) are named "Shape", so the code to get or set the text will fail. You can repair it as follows:

    - Enter a series of unique names in column Q (for example Shape1, Shape2, Shape3, ...)
    - Run the SetShapeNames macro to assign these names to the shapes.
    - Run the GetShapeNames macro to get the names into column N.

    The GetShapeText and SetShapeText macros should now work OK.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    what You told me works in Excel 2007; what I found out was that it is impossible to copy the shapes from Office 2007 to 2003, but Your workaround
    is ok (as always)....
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •