Hello dear visioguy users,
i'm currently create a tool with Excel and Visio to gather all the data about our it infrastructure that our it-department have a big picture of our it-assets. It's pretty cool to collect all data with excel and draw a network map of our assets with visio shapes. But one thing is really important for us in case of this tool, we want to be able to do changes in excel (and visio recognizes) and we want to change shape-data in visio (and write it down to excel). In visio you can manually update the excel-file by clicking update record in database and load data from excel by choosing update shape-data in the context-menu of a shape. In the shape-sheet of a shape i have a row e.g. User. StartMakro_IP in the section user-defined Cells. In this field i have a formula =CALLTHIS("update_item")+DEPENDSON(Prop.IP). This formula calls the update_item method (located in the modul ,,mdl_admin") to write down changes to my excel-file:
Public Sub update_item(shp As Visio.Shape)
'http://visguy.com/vgforum/index.php?topic=4276.0
If shp.CellExists("User.ODBCConnection", Visio.visExistsAnywhere) <> 0 Then
'Loop thru to check if shape has refresh action
If shp.SectionExists(Visio.visSectionAction, Visio.visExistsAnywhere) <> 0 Then
For iRow = 0 To shp.RowCount(Visio.visSectionAction) - 1
If shp.CellsSRC(Visio.visSectionAction, iRow, Visio.visActionAction).Formula = "RUNADDON(""DBU"")" Then
'Force the cell formula to run
shp.CellsSRC(Visio.visSectionAction, iRow, Visio.visActionAction).Trigger
Exit For
End If
Next iRow
End If
End If
End Sub
To be able to load latest changes out of excel we call the method selectionchanged everytime a shape is selected. This method is located in ThisDocument and implemented as follows:
Dim WithEvents MyWindow As Visio.Window
Private Sub Document_RunModeEntered(ByVal doc As IVDocument)
Set MyWindow = ActiveWindow
End Sub
Private Sub MyWindow_SelectionChanged(ByVal Window As IVWindow)
If Window.Selection.Count = 1 Then
If Window.Selection(1).CellExists("User.ODBCConnection", Visio.visExistsAnywhere) <> 0 Then
'Loop thru to check if shape has refresh action
If Window.Selection(1).SectionExists(Visio.visSectionAction, Visio.visExistsAnywhere) <> 0 Then
For iRow = 0 To Window.Selection(1).RowCount(Visio.visSectionAction) - 1
If Window.Selection(1).CellsSRC(Visio.visSectionAction, iRow, Visio.visActionAction).Formula = "RUNADDON(""DBR"")" Then
'Force the cell formula to run
Window.Selection(1).CellsSRC(Visio.visSectionAction, iRow, Visio.visActionAction).Trigger
'MsgBox Window.Selection(1).Name
Exit For
End If
Next iRow
End If
End If
End If
End Sub
I think, the matter is that the currently implemented methods triggers themselves (database update / read data from database) and so an endless loop will appear. In fact Visio crashes at this time and the application will unfortunately closes down.
Does anyone know another method to write changed shape-data from current visio-shape to excel and simultaneously read new data from excel and update shape-data of the current selected visio-shape?
Any help is highly appreciated.
Best regards from Germany
Joerg
as a suggestion from the side, these types of scenarios (change in shape triggers data update, which then triggers another change in shape) and can be a real pain. Visio has an internal mechanism that can be very helpful here, it is ScopeId. We can assign a scopeid to an operation (our code doing an update as an example) and then test for it in other routines to ensure that we are not the trigger for the event being called. When our update is complete we cancel the scopeid and allow the event handlers to work with the normal user and system events. You can see this happening if you take advantage of the EventViewer which is in developer tools, watch for the scope entered and exit events.
hth,
al
hi aledlund,
thanks for your reply! I've done much programming with excel vba and there is so much stuff out there where you can learn from. But the community on visio vba unfortunately isn't that big, and the Microsoft sites aren't helpful most of the times.
I'm not really experienced in using events within visio or vba at all. So, can you please provide a simple code example that does what you describe?
That would be awesome :)
Best regards
Joerg
joerg,
I'd start by getting the Visio SDK (at least the v2010 version) because it has examples in vba and the dotNet languages. There are numerous routines illustrating how to handle events.
https://social.technet.microsoft.com/Forums/office/en-US/4f289bc0-2ff3-4c03-a0d3-bf9352911b9e/visio-2010-sdk?forum=visiogeneral
al
Hi aledlund,
today i'm back in office. I will now try out what you suggested.
Thanks very much for your help so far.
Best regards,
Joerg
Hi aledlund,
i've tried something with the following code:
(http://picload.org/image/clllwid/1.png)
and
(http://picload.org/image/clllwii/2.png)
The sheet of the regarding shape looks like this:
(http://picload.org/image/cllirgp/shape_sheet.png)
If i open up the document and click on a shape (selection_changed), i get the following dialog:
[ English: Visio wants to access the odbc data source "DatenInventarisierung". Click OK to continue ]
(http://picload.org/image/clllwiw/3.png)
If i click ok, the following dialog appears:
[ English: The record with the associated shape "Sheet.245" was updated in the database. Do you want to update the record in the database with the values from the shape? ]
(http://picload.org/image/clllwpg/4.png)
After that, the app crashes down :'( :( :(
[ English: Microsoft Visio doesn't work anymore. It searches for a solution to the problem ]
(http://picload.org/image/clllwpo/5.png)
Do i understand your idea correctly?
Please let me know what do you think?
Help is reaaally appreciated, if you like with a little donation, because it's very important to fix that issue.
Best regards,
Joerg
P.S.: Merry christmas to all of you!
Joerg,
I am an illiterate American and only speak English.Perhaps one of our European friends can help since I cannot read the messages.
sorry,
al
Hi aledlund,
i've asked you to please assess whether the idea within my code is properly or not.
The messages itself aren't important for that, but i've now added the translations to them.
Please again have a look at my visio vba scenario. I'm stuck with that and that's really annoying...
Best regards,
Joerg
The BackSync addin from here (http://visguy.com/vgforum/index.php?topic=6086.msg24731#msg24731) solve all the problems and reduces a lot of pain!
Thanks al for your help!