Link to home
Start Free TrialLog in
Avatar of Meghan Hobson
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!
Avatar of Meghan Hobson
Meghan Hobson

ASKER

Or am I looking at this the wrong way? Is there a way to automate a shape report to execute the same task for all files in a designated folder?
ASKER CERTIFIED SOLUTION
Avatar of Scott Helmers
Scott Helmers
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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?
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.
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!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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