Author Topic: Export shapedata to excel in VBA  (Read 1954 times)

0 Members and 1 Guest are viewing this topic.

jfvert

  • Jr. Member
  • **
  • Posts: 35
Export shapedata to excel in VBA
« 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

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3481
  • Ideas Visio-lized into solutions
Re: Export shapedata to excel in VBA
« Reply #1 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

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
« Last Edit: June 24, 2016, 12:38:04 PM by wapperdude »
Visio 2019 Pro

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3481
  • Ideas Visio-lized into solutions
Re: Export shapedata to excel in VBA
« Reply #2 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
Visio 2019 Pro

jfvert

  • Jr. Member
  • **
  • Posts: 35
Re: Export shapedata to excel in VBA
« Reply #3 on: June 28, 2016, 07:36:34 AM »
Thanks! it helped me a lot! I finaly got what i want!

jfvert