how to move from Visio VBA to C# ?

[sunnyimran]

Hi there,

Long ago I have done a little project in Visio 2016 using VBA and this forum had been really helpful. Project got evolved and many modification needed time to time. I stuck with VBA within Visio.

Now I want to take my project from in-built VBA to a stand alone App. My current workflow has different phases starting from Visio project then some connection to MS access and later on Excel etc.

I am trying to combine all these phases in one desktop app. This app would have a nice user form and sub-forms with selection on which part I want to work then relevant phase can be executed and after completion return back to the starting point.

I don't know where to start to develop such app. I got hints for visual studio C# for MS office automation tasks. Then VSTO (visual studio tools for office) is also something. Then SDK for Visio 2016 would be of any use?

I am at starting level in C# and developing such app will help me learn a lot. I will be the one mostly who will use the final developed app. so no need to worry for distribution related challenges like compatibility and installer creation etc. at this stage.

I could not find a book for this matter and need some helping pointers where to start.
Would someone guide?

regards

[Nikolay]

I think the answer could depend on what your app will be doing with Visio, i.e. what is the use of Visio in your app.

For example, do you want your app to become part of Visio, or Visio to become part of your app, or neither (e.g. just generate Visio files)?
This could clarify at least basic approach... i.e. what type of application you are after.

Also, please be aware that if you switch to from the built-in VBA to anything else, all operations related to Visio may become slower.
E.g. if building a diagram with VBA takes 10 seconds, building it with C# (external code), using the same API, may take 10 minutes.
Of course this is an extreme example and most probably this will not be the case though. But C# will definitely be slower if you make it with calling Visio API just like VBA

[sunnyimran]

Well at the moment, I am working with built-in VBA in Visio, then moving to second phase working with MS Access and then Excel manually.

Phase: Visio-VBA
I have a main visio document with eight A3 size sheets. Visio document contains all the passive shapes and texts etc on all pages that will remain same on all generated visio files. My VBA code resides in a stencil and does the job. Using user-forms in visio vba it takes input from user about various aspects and selections including the total number of visio documents to generate. Additional info is obtained by reading INI files into my script. This sets up certain parameters for visio shapes and text-boxes. these shapes and text boxes are inserted in currently open visio doc. Finally the file is saved with my script generated filename and location. In next iteration, shape/text boxes parameters get change and they are then inserted into currently open Visio doc. This document is also saved as before. Logic continues till user selected count of visio docs have reached. Finally I have to close Main visio App because it contain the last generated visio file open in it.

I realize that some part of my visio docs is static and some part is dynamic. dynamic part is based on my business logic, various inputs from user and INI files contents. How about generating all visio files with static content. Then open each visio file and insert the dynamic content in to the file. Perhaps that would be faster.


Phase: MS-Access
during the Visio phase, same vba script appends certain values as MS-Access records on each loop iteration. After all doc generation is done I have to open that MS access files and do some query work and report generation work in MS access.

Phase: Printer spooling
After various Visio files have been generated (thousands or so) I have to spool them get them printed. I searched many utilities to spool this many visio docs but none of them worked. so far I am using command line parameters of visio in a batch file and running it from command line. Loop iterated to opens each file in visio, send it to decided printer, close this file and next iteration. this is also a lengthy and time consuming phase but I have no better solution yet.

Phase: MS-Excel
based on MS-Access results. I have to populate excel sheets in which I have to analyze data for validation like duplication, inventory, certain checking etc.

This is mostly the workflow that I have to go through routinely.

[Nikolay]

I'm just wondering if you also edit diagrams manually at some point, or just generate them to print?

If everything is automatic, and you basically use Visio to build diagrams and then print them out, i.e. the user (you?) is not supposed to edit them, then your application looks like a standalone application. Maybe you can do the stuff just by writing an external script? There is a project for example to provide Visio functionality to PowerShell:

https://saveenr.gitbook.io/visiopowershell/

Would something like that be an option for you?

I think scripting like Jupyter Notebook would be ideal for this task. I am not aware if you can run Visio directly from a Jupyter notebook though (using C# or maybe python)
I believe @Yacine was investigating this area. Not sure if it works the same way as powershell or not.
For example, if auto-complete is available like in VBA/PowerShell, if debugging is available, like in VBA/PowerShell, etc.

Another script-like solution could be a linqpad., I believe David Parker mentioned it being usable for similar tasks. It is c#, but in a form of a script.

Well to think of it all these look like there is nothing better than VB6 ide, blessed be it on heavens 

You can also always build a normal console application in C# for example

Regarding form input.
If you ask me, I think this is nightmare. It's so much easier to enter data in something like Excel and then grab it from there.
Anyways in c#/.net you also have all options to build the forms as well.

[sunnyimran]

thanks Nikolay
I (or any other user) will not edit my Visio diagrams manually. All insertions/changes will be through my business logic which looks for user input parameters, INI file at that point and iteration count etc. So everything is automatic.

I am not well accustomed in PowerShell.

I wish to port at least Visio generation phase in c#. Need to know what path to take. VSTO add-in versus Visio 2016 SDK? or are there more ways?

I am feeling a bit limited in VBA. To generate visio files, I have to first start Visio App, then run my VBA script till several hundreds of files of one batch are done and visio closes after last file of batch generates because last file is actually the last generated file remaining open in Visio app even after my script completes. If not closed, it may be accidentally edited. Visio needs to be restarted again for next batch of few hundreds files generation.

Is it possible to integrate a desktop App with an office app like visio where visio runs in background and does not appear front on screen? something like using a visio engine to perform generation, spooling/printing tasks without Visio App iteself at front?

I am sure there is some solution for this. only I am asking pointers. the track that would lead to my solution.

[Nikolay]

VSTO is basically targeted on creating Visio extension (i.e. if you want to make your application to be part of Visio, which you don't)
VisioSDK provides some developer tools and code samples, how to build VSTO extensions. I don't think you need that either.
BTW, you don't need Visual Studio as well. You can go with any IDE where you can write C# code, starting with notepad, if you wish to develop in C#

There are some lightweight options now:
- The mentioned LinqPad (https://www.linqpad.net/). The autocomplete & debugging is not free though.
- Visual Studio Code (https://code.visualstudio.com/) - you can do Visio automation with .net core 3.x and C# (.net core 3.x is a bit of uncharted territory now though)
- ScriptCS (http://scriptcs.net/, http://scottksmith.com/blog/2013/05/08/getting-started-with-scriptcs/), there is autocomplete extension for VS Code

Visual Studio is the classic option (https://visualstudio.microsoft.com/)
- you create a console application for example, add reference Visio, and you are basically done and can start hacking.
To reference Visio you don't really need to install anything, just add a reference to Microsoft.Office.Interop.Visio (which is installed by Visio)

To not show Visio, just use "Visio.InvisibleApp" instead of "Visio.Application".

For example, to start hacking with LinqPad, install it and then try pasting the following code in the text editor, and then execute..

Code Select Expand


using Visio = Microsoft.Office.Interop.Visio;

void Main()
{
    var app = new Visio.Application(); // can replace with Visio.InvisibleApp
    var doc = app.Documents.Add("");

    var shape = doc.Pages[1].DrawRectangle(1, 1, 2, 2);
    shape.Text = "Hello world";

    doc.SaveAs(@"C:\test\myfile.vsd"); // fix here
    app.Quit();
}


With all other cases it will be something like that, just the settings may be different and the way how you add a reference to visio may be different.
The code will stay the same

[sunnyimran]

Thanks for the hints.

I have just got visual studio 2019 and I suppose being familiar with console applications using c# will be better. 

ok, will such console app be slower as compared to built-in VBA?
and can I interact with other office apps like excel and access in same console app?

[Nikolay]

Yes, sure you can interact with other office applications as well.

I just thought that maybe Visual Studio is a bit of overkill.
But definitely it will give everything in on pack.

With Excel and Access in principle you can interact using the applications themselves, or just libraries (they are called nuget packages in c# world)
I.e. in principle you can read/write Excel and Access files without Excel or Access even installed.
For excel EPPlus or just plain .NET framework (OleDb) which can go for Access as well.

But the easiest I think you can start with office automation.
This basically means your VBA code that works with office should translate more or less 1:1 to C# code then.

[sunnyimran]

ok thanks,

I am going to start from my Visio VBA project to make it work from outside of visio using c#.

Any hints about efficiency/performance penalty ?

Will post my progress as I go through.

Thanks

[Nikolay]

Well I'd say it is not the best option, if you mean you want to execute vba code in your visio document from outside.. but it is technically possible.

I am just concerned that in such a solution you may end up having two applications, in two different languages to support. So what is the benefit then, it will be just messy..

Anyways. There is execute line for example:
https://docs.microsoft.com/en-us/office/vba/api/visio.document.executeline

Regarding the performance penalty. You can't really do anything about it. What I mean is, code that executes as vba, and doing basically only Visio interactions (calling Visio method) will be faster compared to the same code that executes outside of Visio (c# console application for example). But the difference may be as well 5ms and 10ms, that is, it depends on the application operations, if the difference matters.

[sunnyimran]

okay I will try my project from c# console app. let's see. I will update later

Thanks for ideas