Endless loop with read/update database record from and to excel by using vba?

Started by it_consultant, December 12, 2014, 04:37:42 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

it_consultant

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

aledlund

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

it_consultant

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

aledlund

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



it_consultant

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


it_consultant

Hi aledlund,

i've tried something with the following code:



and



The sheet of the regarding shape looks like this:



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 ]


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? ]


After that, the app crashes down  :'( :( :(

[ English: Microsoft Visio doesn't work anymore. It searches for a solution to the problem ]


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!

aledlund

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

it_consultant

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

it_consultant