Visio Guy

Solution-specific Visio Discussions => Flowcharting => Topic started by: FryGirl on March 19, 2021, 11:19:04 AM

Title: Create a Visio flowchart from Excel table
Post by: FryGirl 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?

Title: Re: Create a Visio flowchart from Excel table
Post by: Surrogate 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 ?
Title: Re: Create a Visio flowchart from Excel table
Post by: FryGirl 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.
Title: Re: Create a Visio flowchart from Excel table
Post by: Surrogate 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 (https://docs.microsoft.com/en-us/office/vba/api/visio.documents.openex)
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 ?
Title: Re: Create a Visio flowchart from Excel table
Post by: FryGirl 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?
Title: Re: Create a Visio flowchart from Excel table
Post by: Croc 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?
Title: Re: Create a Visio flowchart from Excel table
Post by: FryGirl on March 20, 2021, 10:09:56 AM
Hello and thank you for the reply,

Visio Professional 2016
Title: Re: Create a Visio flowchart from Excel table
Post by: wapperdude 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.
Title: Re: Create a Visio flowchart from Excel table
Post by: Croc 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.
Title: Re: Create a Visio flowchart from Excel table
Post by: FryGirl 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.
Title: Re: Create a Visio flowchart from Excel table
Post by: Croc 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.
Title: Re: Create a Visio flowchart from Excel table
Post by: FryGirl on March 20, 2021, 11:51:53 AM
Okay.  Thank you for your time.
Title: Re: Create a Visio flowchart from Excel table
Post by: Croc 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
Title: Re: Create a Visio flowchart from Excel table
Post by: FryGirl 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
Title: Re: Create a Visio flowchart from Excel table
Post by: Surrogate 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
Title: Re: Create a Visio flowchart from Excel table
Post by: FryGirl on March 20, 2021, 05:16:16 PM
Hi Surrogate,

Yes, I added the reference to the library in the VBE.  Wasn't sure which to use, so I checked all three.  Type Library, Save As Web Type Library, and Visio Viewer 16.0 Type Library.

How can the code be changed to late binding?
Title: Re: Create a Visio flowchart from Excel table
Post by: Surrogate on March 20, 2021, 05:32:31 PM
Wasn't sure which to use, so I checked all three.  Type Library, Save As Web Type Library, and Visio Viewer 16.0 Type Library.
Just cant find all these libraries as loaded (checked)
(https://i.ibb.co/hZ9Yhp0/Early-Binding.png)
I use Visio 2019 Professional ie Visio 16 Type Library, like as MS Visio 2016.
How can the code be changed to late binding?
if you use late binding, you dont need add references, but you need use values instead Visio's application system constants
Code
    AppVisio.Documents.AddEx "", 0, 0
    AppVisio.Documents.OpenEx "C:\Users\1212C\Documents\My Shapes\Fry.vssx", 0 + 4  'Add Basic Stencil
https://docs.microsoft.com/en-us/office/vba/api/visio.vismeasurementsystem
https://docs.microsoft.com/en-us/office/vba/api/visio.documents.openex
Title: Re: Create a Visio flowchart from Excel table
Post by: FryGirl on March 20, 2021, 06:04:09 PM
Hi Surrogate,

That worked great by updating to the constants.  Thanks.
Title: Re: Create a Visio flowchart from Excel table
Post by: Croc on March 21, 2021, 02:15:00 AM
FryGirl, If you insert the following lines at the end of the DropStringOfBoxes procedure, then Visio will make the flowchart beautiful :)
Code
   ...
    AppVisio.ActiveWindow.DeselectAll
    AppVisio.ActivePage.PageSheet.Cells("PlaceStyle").FormulaForceU = "1"
    AppVisio.ActivePage.PageSheet.Cells("RouteStyle").FormulaForceU = "5"
    AppVisio.ActivePage.Layout
   
    Set AppVisio = Nothing
End Sub
Title: Re: Create a Visio flowchart from Excel table
Post by: FryGirl on March 21, 2021, 07:50:56 AM
Wow, thanks Croc.  More than I could have expected.

Appreciate all the time and help from everybody.