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?
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 ?
Hi and thanks for the reply,
I want to do this with a macro. Create your own solution with same non-complex steps.
Quote from: FryGirl on March 19, 2021, 04:19:04 PMCan the stencils already within Visio work?
Yes!
Quote from: FryGirl on March 19, 2021, 04:19:04 PMDo 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)
Quote from: FryGirl on March 19, 2021, 04:19:04 PM
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 ?
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?
QuoteI 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?
Hello and thank you for the reply,
Visio Professional 2016
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.
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.
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.
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.
Okay. Thank you for your time.
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
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
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.
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
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?
Quote from: FryGirl on March 20, 2021, 10:16:16 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.
Quote from: FryGirl on March 20, 2021, 10:16:16 PM
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
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
Hi Surrogate,
That worked great by updating to the constants. Thanks.
FryGirl, If you insert the following lines at the end of the DropStringOfBoxes procedure, then Visio will make the flowchart beautiful :)
...
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
Wow, thanks Croc. More than I could have expected.
Appreciate all the time and help from everybody.