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

[mikey38654]

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:

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

 

[mikey38654]

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]

See if you can adapt this :

Get list of all Properties and Methods for an object with VBA code alone

Hi dear forum, Workbook Sample I am posting here a self-contained vba approach for getting members of an object without the need for an external dll such as the well known TLBNINF32.DLL . The GetObjectFunctions function takes two arguments : (1) The object being browsed and (2) an optional...

www.mrexcel.com