• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7844
  • Last Modified:

Visio Export to Excel

Is there a quick and easy way to export data to Excel from Visio from a basic drawing?
Below is a screen clip of something we would consider exporting to Excel. The # of rows would vary each time. And the Excel doc would be an accumulation of exported data from the template page in Visio from each process map.  The info in Visio is on a background template page – the info is always aligned in this format in multiple text blocks.  
0
karaflanagan
Asked:
karaflanagan
  • 3
  • 2
  • 2
  • +1
1 Solution
 
NorieCommented:
Isn't there a Visio Add-on you can use for that?

Think it might be something like an export to database wizard where Excel is one of the options to export to.

Not sure what's exported or in what format.
0
 
khairilCommented:
Hi,

Cannot see any screen caputer here.
0
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Kara,

The answer is a definite maybe... the "maybe" is because your data consists of text in multiple text boxes. If your diagram contained multiple Visio shapes and each shape  

rather than multiple data fields inside a collection of Visio shapes

.


The built-in Visio report wizard has a very nice feature that lets you send output to Excel. Consequently, if you have a set of shapes that contain data, it's very easy to create a report in Excel format that contains the shape data.

However, from the looks of your screen shot, you need to collect and export the text on each row in your table. The problem that arises is that this isn't really a table -- it's just a collection of lines with text and shapes in the various row-column intersections. What I mean by this is that Visio doesn't have any way to determine that there is a relationship between the shape containing "2" and the text that begins "Agenda and Workbook are all in the..." -- they just happen to be next to each other.

I can think of two potential solutions:
1) The first involves writing custom VBA code to extract and then export the data. The code could determine which objects are in each "row" using the coordinates of each object on the page.
2) You could add a shape data field to each shape and text box to identify the row. Then you could use the report wizard to create the Excel file.

What version of Visio are you using? There are several features in Visio 2010 that might make #2 easier.

Please let me know if you'd like more details about either option.

Scott

flowchart.jpg
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
NorieCommented:
Scott

Is there a screenshot?
0
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
Kara -- IGNORE PREVIOUS POST (I submitted it by accident before finishing).

The answer is a definite maybe... the "maybe" is because I'm not yet sure exactly what you need and also because your data consists of text in multiple text boxes.

If all you need is to have the text dumped out to Excel and there's no need to maintain the relationship between different text boxes, i.e., you don't need to know that one bit of text was in the same row as another bit of text, the Visio report wizard can do the job for you.

However, I suspect you do need to know the relationships -- to know what text was in each row of your table. And so my answer gets a bit longer...

If your diagram contained multiple Visio shapes and each shape contained one or more data fields, the you could use the built-in Visio report wizard; it has a very nice feature that lets you send output to Excel.

For example, here's a very simple flowchart:
simple flowchart
and here's a Visio shape report written to Excel:
simple flowchart report
However, from the looks of the screen shot you accidentally posted elsewhere, you need to collect and export the text that exists in multiple text boxes on multiple rows of a table. The problem that arises is that this isn't really a table -- it's just a collection of lines with text and shapes in the various row-column intersections. Consequently, the Visio report wizard doesn't have any way to know that there is a relationship between one text box containing "ABCDE" and the text box next to it that contains "WXYZ".

I can think of two potential solutions:

1) Add a shape data field to each text box to identify its row location. You could do this manually, or if you need to create these diagrams regularly, you could create a couple of custom shapes and text boxes that already contain this field and use a macro to add the row numbers.

Regardless how the "row number" field gets into each shape and obtains its row number value, you could then use the report wizard to create the Excel file.

2) The second solution involves writing custom VBA code to extract and then export the data. The code could determine which objects are in each "row" using the coordinates of each object on the page. This is very doable but does require reasonably detailed knowledge of Visio and Visio programming.

Please let me know if you'd like more details about either option.

Also, what version of Visio are you using?

Scott

1
 
karaflanaganAuthor Commented:
Visio Screen CaptureScott -
Visio 2007 Standard.
0
 
karaflanaganAuthor Commented:
I am checking on a few things and will get back to you as soon as possible on the which of the options I would be interested in using.  Thanks!
0
 
karaflanaganAuthor Commented:
Scott,

We are thinking that the wizard will work for us.
If I need further help on the issue I will definitely be asking you.  Thanks for your help!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now