Visio Guy

Visio Discussions => Programming & Code => Topic started by: jfvert on June 23, 2016, 01:06:29 PM

Title: Export shapedata to excel in VBA
Post by: jfvert on June 23, 2016, 01:06:29 PM
Hi guys,

Is there a way to take datas from a shapes and send them to an excel file in VBA?



Thanks,

Jean-Francois
Title: Re: Export shapedata to excel in VBA
Post by: wapperdude on June 24, 2016, 09:54:50 AM
See the code listed here:  http://stackoverflow.com/questions/17554466/transfering-shape-data-from-visio-2010-to-excel-2010-for-further-manipulation-us (http://stackoverflow.com/questions/17554466/transfering-shape-data-from-visio-2010-to-excel-2010-for-further-manipulation-us)

You will need to adjust the code to your specific needs.
Oh.  You also need to reference  excel object library...
    <alt>+F11 to open the VBA edit window
    In V2007, menu bar > Tools > References
    Scroll to Microsoft Excel Object lib (or something like that), and select it
 
Now the code will be ready to run.

Wapperdude
Title: Re: Export shapedata to excel in VBA
Post by: wapperdude on June 25, 2016, 12:38:10 AM
The attached file provides the code to pull shape data from all shapes on the active page.  It is broken down into 3 modules.  The main module passes the shapes collection for the active page to the shape searching module.  This 2nd module examines each shape on the page, rejects 1D shapes, and ignores shapes with no data.  Names of acceptable shapes along with the shape data is stored into a dynamic array.  Once all shapes have been interrogated, the third and final subroutine is called to export the data into excel and then reformat the occupied cells on the worksheet.

The file contains three shapes.  The 3rd shape has no shapedata.  The 2nd page shows the excel file results of the above process for these 3 shapes.  The code has a lot of comments.  Hopefully that will promote understanding of the subs.

Enjoy
Wapperdude
Title: Re: Export shapedata to excel in VBA
Post by: jfvert on June 28, 2016, 07:36:34 AM
Thanks! it helped me a lot! I finaly got what i want!

jfvert