Solved

Capture Text in Word Art; Excel 2003

Posted on 2011-03-15
4
331 Views
Last Modified: 2012-05-11
I have a spreadsheet with more than 100 tabs.  The data I need to identify is located in a Word Art graphic at the top of each tab.  I plan to write a loop to go through each tab and look at each word art object, but I can't figure out the code to capture the text in the word art.  Help would be much appreciated.
0
Comment
Question by:SueJStevens
  • 2
  • 2
4 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35142990
This will get you started:

Public Sub ExamineWordArt()

    Dim Worksheet As Worksheet
    Dim Shape As Shape
   
    For Each Worksheet In ThisWorkbook.Worksheets
        For Each Shape In Worksheet.Shapes
            MsgBox Shape.OLEFormat.Object.ShapeRange.TextEffect.Text
        Next Shape
    Next Worksheet

End Sub

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35143004
A refinement to only look at WordArt shapes:

Public Sub ExamineWordArt()

    Dim Worksheet As Worksheet
    Dim Shape As Shape
   
    For Each Worksheet In ThisWorkbook.Worksheets
        For Each Shape In Worksheet.Shapes
            If Shape.Type = msoTextEffect Then
                MsgBox Shape.OLEFormat.Object.ShapeRange.TextEffect.Text
            End If
        Next Shape
    Next Worksheet

End Sub

Kevin
0
 

Author Comment

by:SueJStevens
ID: 35149058
Thank you.  Later on today I'll be able to have time to test this and get you feedback.
0
 

Author Closing Comment

by:SueJStevens
ID: 35166508
Thank you.  This is exactly what I needed!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question