Import Excel data into Visio and autogenerate drawing

Started by solagracia, September 10, 2014, 08:17:04 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

solagracia

Hi experts,

I'm using Office 2013 and Visio 2010. My data format is in .csv or .xlsx.
I need to import some simple matrix data into a Visio 2010 VBA macro and then autoconnect the shapes together, and use the excel information to put labels on all the shapes and connectors. The matrix will be 15x15 or greater, but for simplicity I have reduced it to 7x7.

   BSL   A   B   C   D   E   F
BSL   -   .   .   .   .   8   .
A   .   -   131   131   .   21   .
B   .   .   -   131   .   .   .
C   .   .   .   -   .   .   .
D   .   .   .   .   -   109   263
E   .   .   .   .   .   -   108
F   .   .   .   .   .   .   -



I have attached the Visio format that I'd like to autogenerate. I can code, I just have no experience with VBA in these applications, and need some quick pointers to get me going, after which I can probably hack my way through things.

Any help offered would be greatly appreciated!

Yacine

I presume that you are able to open your data in VBA.

Now you are probably missing the appropriate drawing commands.

I would suggest, that you record a macro in which you do exactly what you expect your macro to do, then adjust the code to your needs.
You'll mainly need to replace the specific IDs by generic shape IDs.

That's a topic that has been repeated over and over in the forum. Here's one of the latest discussions.
http://visguy.com/vgforum/index.php?topic=5802.msg23269#msg23269

Placing the shapes at the right place could be done by a lot of clever code, or by just relayouting the drawing.

HTH for a start,
Yacine
Yacine

solagracia

Hi Yacine,

Actually, I do not know how to open my data in VBA.  I have never coded VBA in these applications and was hoping for some simple example code on how to do it, as a first step...as I said I can use either CSV or XLSX format, though CSV is the native one.

Thanks!

Yacine

#3
Hi Solagracia,

Here's a link to a snippet, that I use also in my projects.
http://freesourcecode.net/vbprojects/44123/sourcecode/SaveArrayAsCSVFile.bas#.VBPbTmOwuCB

You could modify "helper" routine and use it in a dialog form.


Sub LoadCSV(sFileName As String, sDelimiter As String) ' sFileName being the file that you need to load
Dim TempCollection As Collection
Dim splitarray() As String

On Error GoTo ErrHandler_LoadCSV
overwrite = MsgBox("Do you want to overwrite the current drawing?", vbYesNo)
If overwrite = 7 Then Exit Sub
Set States = New Collection
Set Circuits = New Collection

If Dir(sFileName) <> "" Then
  Open sFileName For Input As #7
  While Not (EOF(7))
    Line Input #7, sLine
    ' Perform the necessary tests on sLine to check for validity
    If Len(sLine) > 0 Then

       ' ******* perform the actual drawing operations here ********

    End If
  Wend
  Close #7
End If
'subsequent commands
Exit Sub

ErrHandler_LoadCSV:
Close #7
Debug.Print "Error in LoadCSV: " & Err.Description
End Sub
Yacine

Yacine

#4
Hi Solagracia,
This is definitely not good for you, but I had some time to play.
So here's the finished solution.

Run "LoadCSV" to test the macro.
Yacine

solagracia

Hi Yacine,

Thanks!  This was very helpful to get me started.  I made quite a lot of progress, and I am now able to completely connect my nodes together on multiple visio pages according to the csv data.  I also applied some more complex layering to my connectors and coloring also.

I did some things different than you.  There are probably better ways for me to do some things. 

Right now I am stuck at not knowing how to calculate the coordinates of the connector endpoint, so that I can create a text box there so that I can include some metadata about the connectors themselves.   

Once I get the code cleaned up a little, I will post -- maybe you can take a little time to critique my code and offer suggestions.

SG

Yacine

#6
Hi SG,
the coordinate of the end of the connector is easy to find out.
Set the "controls.textposition " x to width and y to height.
Either put the data in the shape's text, or if really necessary bind the position of the box to the text control.
(You'll need to remove that hiding formula in the text control row.
Yacine