Is it possible: Collect all properties of a Shape into a text file?

mikey38654

New Member
Joined
Jun 15, 2018
Messages
8
Good day everyone,

I have found so much help "at just the right time" on this forum, and have been coding VBA for a little while. I'd call myself a Novice or better, but far from a master! I think I have a unique question/challenge, though; I couldn't seem to find the answer even on Google, or at least nothing I could massage gently together to make something that works in the time that I have.

My question is: Is there a way to iterate through all of the properties of a Shape - recursively? I know I can write a script to go through the collection of Shapes, and in each iteration of the loop (each shape) I can do code like:
.Name
.Locked
...etc.

But, some of the properties of my Shapes return objects with their own hierarchy of values, which I also need.

The project comes from the fact that I've deeply formatted a text box Shape for a GUI I'm making, and now I want to grab ALL of the properties of the shape (at least those containing values, but all would be OK to simplify) and dump to a CSV file for reference/looping later to recreate the formatting in other shapes. I have done so much as making the text box have a shadow, for example, and of course there is a fill color and border. The text formatting is not important as it is manually entered and formatted anyway. In the end, the GUI element I am making will provide help on demand as users are using the spreadsheet for data entry in a new project and may need reference as to where to find a piece of data.

Obviously I'm aware that if I export ALL of the properties into a CSV, I will have to remove some things to avoid several issues when applying the formats (by the way I don't want to Shape.PickUp and Shape.Apply), but that's easier after the fact of getting the massive amount of data into a CSV with a recursive script.

Any suggestions? I guess I need to know primarily if each of these sets of data (i.e., objects under the Shape object Properties) belong to collections or if there is some other way to make this easier. Due to time constrictions in this project, I'd like to avoid making a huge script that would probably take me a good hour or two if things go right just to format some text boxes. The idea is to save time now and also that I will have record of the formats to use later or in other projects as well. ;)

Thanks in advance for any advice. Also thanks for your patience.

Mike R

P.S. Here is the code I've started before I realized this was gonna be rather tedious. Yes I literally left the error there in order to investigate my options more quickly, so please excuse that:

VBA Code:
Sub GetShapeFormattingInfo()

Dim ws As Worksheet
Dim objFSO As Object
Dim objFile As Object
Dim Sh As Shape
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("ShapeFormattingInfo.csv")

objFile.writeline "Object Name, Other Header Row Items" 'Obviously not the final list, I just haven't put what I need here. Probably will be part of the loop iterations if I can do them.

    For Each Sh In sht_ResearchMain.Shapes 'sht_ResearchMain is the sheet containing the target Shape
        If Sh.Name = "Rounded Rectangle 89" Then 'Rectangle 89 is the target Shape with the formatting information I want to dump to CSV
            With Sh
                objFile.writeline .Name & "," & _
                                    ' This line will error due to underscore above - I already know. ;-)
                                    ' More properties and property objects with properties here.
                                    ' May need to find out how to access the collections, if possible.

            End With
        End If
    Next Sh


objFile.Close
Set objFSO = Nothing

End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

mikey38654

New Member
Joined
Jun 15, 2018
Messages
8
Sorry guys - In the comment about the Shape, I should have written "Rounded Rectangle 89" - that is the actual name as specified in the code. Could not find an edit button on my post, I hope it wasn't right in front of my face! 🤦‍♂️
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,012
Office Version
  1. 2016
Platform
  1. Windows
See if you can adapt this :
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,951
Members
410,713
Latest member
TaremyLunsil
Top