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


New Member
Jun 15, 2018
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:

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

Set objFSO = Nothing

End Sub

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.


New Member
Jun 15, 2018
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
Dec 5, 2002
Office Version
  1. 2016
  1. Windows
See if you can adapt this :
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back