Author Topic: Create a Visio flowchart from Excel table  (Read 12285 times)

0 Members and 1 Guest are viewing this topic.

FryGirl

  • Jr. Member
  • **
  • Posts: 13
Create a Visio flowchart from Excel table
« on: March 19, 2021, 11:19:04 AM »
I found this link which I thought looked promising, but I can't get it to work.  When I copied out the sample sample table and then the code, the code debugged to Documents.OpenEx looking for a stencil.  Can the stencils already within Visio work?  Do you need to open a stencil from a file on your computer?

https://www.mrexcel.com/board/threads/excel-macro-to-create-visio-flow.996104/

My setup is basically like what is found in this link, but unfortunately, I can't get the Visio Data Visualizer.  I'm really looking at the first five columns.

https://techcommunity.microsoft.com/t5/visio-blog/bi-directional-process-modeling-with-visio-and-excel/ba-p/240555

Could I get some guidance here?


Surrogate

  • Hero Member
  • *****
  • Posts: 1355
    • ShapeSheet™ Knowledge Base
Re: Create a Visio flowchart from Excel table
« Reply #1 on: March 19, 2021, 11:40:47 AM »
I am sorry, I am Russian !
I dont undestand you want start Data Visualizer via macro or want create your own solution with same non-complex steps ?

FryGirl

  • Jr. Member
  • **
  • Posts: 13
Re: Create a Visio flowchart from Excel table
« Reply #2 on: March 19, 2021, 11:56:21 AM »
Hi and thanks for the reply,

I want to do this with a macro. Create your own solution with same non-complex steps.

Surrogate

  • Hero Member
  • *****
  • Posts: 1355
    • ShapeSheet™ Knowledge Base
Re: Create a Visio flowchart from Excel table
« Reply #3 on: March 20, 2021, 12:48:18 AM »
Can the stencils already within Visio work?
Yes!
Do you need to open a stencil from a file on your computer?
You also can open stencils via MS Visio interface without code, before you start write own macro. In this case each stencil are document, and you must identify it like Documents(2) and so on. In this case you dont need load these stencils via OpenEx method
When I copied out the sample sample table and then the code, the code debugged to Documents.OpenEx looking for a stencil
Are you sure that use coppect path to stencils document location ?
« Last Edit: March 20, 2021, 12:52:00 AM by Surrogate »

FryGirl

  • Jr. Member
  • **
  • Posts: 13
Re: Create a Visio flowchart from Excel table
« Reply #4 on: March 20, 2021, 08:26:12 AM »
What I am trying to say, I don't have any stencils on my computer or I did not put any there.  The code I referenced above had a path for stencils, but that was the path the OP used.  I can't change that part as I don't have any .vss files on my computer to associate too.

The Visio Data Visualizer would be a great option, but am prohibited from putting it on my work computer.  Can this be done with VBA with the attachment supplied?  I would just like to get the table of data from the attachment drawn into Visio as a Flowchart.  Is that possible?

Croc

  • Sr. Member
  • ****
  • Posts: 280
    • Visioport
Re: Create a Visio flowchart from Excel table
« Reply #5 on: March 20, 2021, 09:53:08 AM »
Quote
I can't change that part as I don't have any .vss files on my computer to associate too
If you have Visio installed on your computer, then you also have .vss files (although you may not know about this).
Which Visio version are you using?

FryGirl

  • Jr. Member
  • **
  • Posts: 13
Re: Create a Visio flowchart from Excel table
« Reply #6 on: March 20, 2021, 10:09:56 AM »
Hello and thank you for the reply,

Visio Professional 2016

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4055
  • Ideas Visio-lized into solutions
Re: Create a Visio flowchart from Excel table
« Reply #7 on: March 20, 2021, 10:39:28 AM »
Adding to what Croc said, the default location is Program Files\Microsoft Office\Office<version>\language.  This is the default working folder for templates, stencils, and Help files included with Visio, according to M$.  For example, you ought to have a directory path location something like:  Program Files\Microsoft Office\Office14\1033

Without default stencils, (the Pro version has all for the specific product) Visio is pretty lame and useless.
Visio 2019 Pro

Croc

  • Sr. Member
  • ****
  • Posts: 280
    • Visioport
Re: Create a Visio flowchart from Excel table
« Reply #8 on: March 20, 2021, 10:51:08 AM »
You can also make your own stencil and place it in the "My Shapes" folder.
For example, by copying the desired master shapes from Basic Fowchart Shapes.vss into a new stencil. So did the author of the above article. Therefore, it refers to the address "C: \ Users \ amitp \ Documents \ My Shapes \ amit.vss"
amit.vss is a homemade stencil.

FryGirl

  • Jr. Member
  • **
  • Posts: 13
Re: Create a Visio flowchart from Excel table
« Reply #9 on: March 20, 2021, 11:26:12 AM »
Okay, thank you for the additional push.  I created a stencil and saved it to the proper path.  Using the sample from the link above, I ensured all of the stencils match exactly the names in column B of the spreadsheet.  The macro runs completely thru grabbing the stencil from the path and then shows me the complete msgbox at the end; however, there isn't a flowchart created.

Croc

  • Sr. Member
  • ****
  • Posts: 280
    • Visioport
Re: Create a Visio flowchart from Excel table
« Reply #10 on: March 20, 2021, 11:39:54 AM »
It can be assumed that the format of your table is different from what the macro expects, and the macro simply does not find the information it needs. To verify or disprove this, you need to monitor the execution of the macro in debug mode. But it takes time.
In short, the problem can be solved for sure, but I'm not sure that you will find enthusiasts who are ready to do it.

FryGirl

  • Jr. Member
  • **
  • Posts: 13
Re: Create a Visio flowchart from Excel table
« Reply #11 on: March 20, 2021, 11:51:53 AM »
Okay.  Thank you for your time.

Croc

  • Sr. Member
  • ****
  • Posts: 280
    • Visioport
Re: Create a Visio flowchart from Excel table
« Reply #12 on: March 20, 2021, 12:57:58 PM »
FryGirl, I looked a little at the text of the macro. It looks a bit like a working macro, but it definitely needs some work.
Please note the following:
1. Your stencil will most likely have a .vssx extension instead of .vss
2. In newer versions, a combined Start/End master can be used instead of Start and End. If you copy and rename it, you may need to use Masters.Item instead of Masters.ItemU
3. The line "Set vsoConnectorShape = Visio.ActivePage.Shapes (" line2 ")" is garbage
4. The macro expects the name of the master shape in column 2, not in column 5
5. Macro does not use headers in the first row
6. It looks like there is some confusion with indices
7. Error handling is done incorrectly and skips program after the first error

FryGirl

  • Jr. Member
  • **
  • Posts: 13
Re: Create a Visio flowchart from Excel table
« Reply #13 on: March 20, 2021, 04:53:50 PM »
Thanks Croc.  The direction so far has helped tremendously.  As for your comments, I've cleaned up as much as I could based on what I understand with VBA in Visio.

Excel file with table and code attached if you have any other ideas.

1. Your stencil will most likely have a .vssx extension instead of .vss
  -- Yes, I found the new extension as .vssx

2. In newer versions, a combined Start/End master can be used instead of Start and End. If you copy and rename it, you may need to use Masters.Item instead of Masters.ItemU
  -- This is working, so not sure whether a change is needed here

3. The line "Set vsoConnectorShape = Visio.ActivePage.Shapes (" line2 ")" is garbage
  -- Deleted this line

4. The macro expects the name of the master shape in column 2, not in column 5
  -- Couldn't find where this was an error

5. Macro does not use headers in the first row
   -- Not sure what to do here

6. It looks like there is some confusion with indices
  -- I stepped thru the code with F8, and it does seem to bounce around which I don't understand, but the code did create a flowchart

7. Error handling is done incorrectly and skips program after the first error
  -- I did a Google search, but wasn't quite sure why or what to fix here

Surrogate

  • Hero Member
  • *****
  • Posts: 1355
    • ShapeSheet™ Knowledge Base
Re: Create a Visio flowchart from Excel table
« Reply #14 on: March 20, 2021, 05:11:08 PM »
FryGirl,

just check your xlsm-example
Do you use early (add reference to Visio library) or late binding ?
I cant see reference to Visio in your project.
Code
    AppVisio.Documents.AddEx "", visMSDefault, 0
    AppVisio.Documents.OpenEx "C:\Users\1212C\Documents\My Shapes\Fry.vssx", visOpenRO + visOpenDocked  'Add Basic Stencil
If you use late binding MS Excel dont know MS Visio constants, like visMSDefault, visOpenRO or visOpenDocked