Meghan Hobson
asked on
Export Texboxes To Excel Using Macro
Hello All!
I am new to Visio/VBA and am looking for code that will export all textboxes on a page to excel. I do not want to use a shape report because I'd like to be able to loop this macro to export all textboxes, from all files in a designated folder.
Your help is MUCH appreciated, so thank you in advance!
I am new to Visio/VBA and am looking for code that will export all textboxes on a page to excel. I do not want to use a shape report because I'd like to be able to loop this macro to export all textboxes, from all files in a designated folder.
Your help is MUCH appreciated, so thank you in advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much scott!! The looping code on the linked forum is incredibly helpful as well! One quick question: Is there any way to take the code above and have it export only a single shape? I have several different shapes on a page and am curious if I can run this and have it only export the text in rectangles specifically.
Thanks again!
Thanks again!
Absolutely. The only thing you need is a way to differentiate the shape(s) you want from the one(s) you don't. For example, you might select based on shape name, or the master from which a shape was created (or whether the shape was created from a master or created by using the drawing tools), or shape size, or position on page...
Once you know how to select the shape you want, you'll just need to insert an IF statement inside the "For each shp in pg. shapes" loop.
You mentioned that it's the text on rectangles that you want to capture. Are they simply text boxes that you created using the Text Box tool? Or were they created by dragging a master onto the page? Do they have a unique characteristic that you can use to identify them?
Once you know how to select the shape you want, you'll just need to insert an IF statement inside the "For each shp in pg. shapes" loop.
You mentioned that it's the text on rectangles that you want to capture. Are they simply text boxes that you created using the Text Box tool? Or were they created by dragging a master onto the page? Do they have a unique characteristic that you can use to identify them?
ASKER
Hi Scott, my plan has changed a bit since my original post! I am now looking to extract text in rectangle shapes (CRTL+8) and not text boxes. The goal is to export a certain type of annotation denoted by the rectangle for each file. Is using a rectangle enough to uniquely identify them? What would the syntax look like for that within the if statement?
The challenge increases a bit when the desired shapes were drawn with one of the tools, regardless whether it's the Text Box tool, the Rectangle tool, or another one. Tool-drawn shapes don't have masters so we can't test for a shapes derived from a particular master. Consequently, we need some other way to identify the desired shapes. Are they all a unique color? Width? Height? At a specific location on the page? Are these the only shapes that display text on them? Is there anything else that might be unique?
As an aside, we can't use the shape name because shapes created with the tools have generic names like Sheet.1 or Sheet. 237.
If you can't identify a unique attribute, it's possible to create one, although it may take a bit of work if you have a large number of drawings. However, it will be a one-time thing. A few of the options: you could assign a unique name; you could assign a value to the shape's hidden data properties; you could create a master... there are other options too but let me know whether there is currently something unique before we go farther down this path.
As an aside, we can't use the shape name because shapes created with the tools have generic names like Sheet.1 or Sheet. 237.
If you can't identify a unique attribute, it's possible to create one, although it may take a bit of work if you have a large number of drawings. However, it will be a one-time thing. A few of the options: you could assign a unique name; you could assign a value to the shape's hidden data properties; you could create a master... there are other options too but let me know whether there is currently something unique before we go farther down this path.
ASKER
Hi Scott. I am actually able to distinguish between the rectangles and other shapes. The output looks something like this (with the text attached to each shape):
Sheet.152 text
Sheet.153 text
Sheet.154 text
Rectangle.156 text
Rectangle.157 text
Rectangle.158 text
Rectangle.159 text
Rectangle.160 text
And the rectangles contain the text I was looking to export. The only issue I'm having is with looping. I believe I have the correct libraries, but I can't seem to get this export-to-excel code to merge with the looping macro. Do you know why I would get "variable not defined" errors despite having the correct libraries? I don't really understand the typical conventions/syntax for loops in VBA.
Thanks again! Microsoft is lucky to have an expert like you!
Sheet.152 text
Sheet.153 text
Sheet.154 text
Rectangle.156 text
Rectangle.157 text
Rectangle.158 text
Rectangle.159 text
Rectangle.160 text
And the rectangles contain the text I was looking to export. The only issue I'm having is with looping. I believe I have the correct libraries, but I can't seem to get this export-to-excel code to merge with the looping macro. Do you know why I would get "variable not defined" errors despite having the correct libraries? I don't really understand the typical conventions/syntax for loops in VBA.
Thanks again! Microsoft is lucky to have an expert like you!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Meghan -- have you had a chance to try the code? If it does what you need, please accept one or more of the answers above. If you still need help, please let me know.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Split:
-- Scott Helmers (https:#a42240446)
-- Scott Helmers (https:#a42247972)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Split:
-- Scott Helmers (https:#a42240446)
-- Scott Helmers (https:#a42247972)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
ASKER