Visio Guy

Visio Discussions => Programming & Code => Topic started by: ovi on October 01, 2022, 09:36:18 AM

Title: Edit Shape Data with Excel using Visio Standard
Post by: ovi on October 01, 2022, 09:36:18 AM
Hello everyone,
I would like to edit Shape Data from Excel. At the moment I am able to open a new excel file from VBA and fill it with the current Shape Data values of a shape (I started from the code in this thread (http://visguy.com/vgforum/index.php?topic=7043.0)). The problem with this approch is that I don't know how to detect when the user is done editing...

The ideal workflow would be to detect when the excel file is closed/saved and update the values in visio by reading workbook's cells again.

Do you have any idea how to do something like this?

P.s. I need to make this work on Visio Standard, so I don't have the "Data" tab that would probably make this a lot easier.


EDIT: Title changed from "Listen to Excel events from Visio" to "Edit Shape Data with Excel using Visio Standard" to better reflect the content of the thread.
Title: Re: Listen to Excel events from Visio
Post by: Nikolay on October 01, 2022, 10:12:17 AM
You can use Visio Plan 2 subscription, it's something like $15 per month.
If you are an employee, your employer may spend many times more than that on your salary, while you will be coding that, you could use this as an argument.
Buying a solution is almost always much cheaper than coding it.
Title: Re: Listen to Excel events from Visio
Post by: ovi on October 01, 2022, 10:51:09 AM
The situation is a little bit more complex then this... but i get what you mean. I don't rule out that changing licenses might be better, but in order to make the best decision I would like to understand the feasibility of implementing it on my own.

Title: Re: Listen to Excel events from Visio
Post by: Yacine on October 01, 2022, 11:11:49 AM
The way the question is formulated Plan2 or Pro would not help here.
The easiest and safest would be to split the operations:
1) Open the excel file for editing
2) Check for excel file closed, then open for reading, do your stuff and close.
Easy and straightforward.

If you still need to stick with your primary idea of listening to event close then you need to instantiate the excel obejct to open with a "whitevents". That's the keyword to google.
Title: Re: Listen to Excel events from Visio
Post by: Nikolay on October 01, 2022, 11:20:31 AM
I meant the solution to the original question of connecting Excel data to Visio shape data (linked topic)
This question looks to me already as "XY problem"
Title: Re: Listen to Excel events from Visio
Post by: ovi on October 01, 2022, 12:13:37 PM
Quote from: Yacine on October 01, 2022, 11:11:49 AM
The way the question is formulated Plan2 or Pro would not help here.
The easiest and safest would be to split the operations:
1) Open the excel file for editing
2) Check for excel file closed, then open for reading, do your stuff and close.
Easy and straightforward.

If you still need to stick with your primary idea of listening to event close then you need to instantiate the excel obejct to open with a "whitevents". That's the keyword to google.

The approch you are suggesting is basically what I was trying to do. The problem is how to check if excel file was closed. By looking at the docs of Excel Application object  (https://learn.microsoft.com/en-us/office/vba/api/excel.application(object)) and Workbook object (https://learn.microsoft.com/en-us/office/vba/api/excel.workbook) I don't see how I could get this information without intercepting events (such as Workbook.BeforeClose event). Am I missing something? 
Title: Re: Listen to Excel events from Visio
Post by: Yacine on October 02, 2022, 09:13:40 AM
Quote from: Nikolay on October 01, 2022, 11:20:31 AM
I meant the solution to the original question of connecting Excel data to Visio shape data (linked topic)
This question looks to me already as "XY problem"
You're right.
Personally, I nevertheless prefer the VBA solution. for "independence" ;) as far as that would mean something with a proprietary software.
Title: Re: Listen to Excel events from Visio
Post by: Yacine on October 02, 2022, 09:14:17 AM
Quote from: iamatempuser on October 01, 2022, 12:13:37 PM
how to check if excel file was closed 

https://learn.microsoft.com/en-us/office/troubleshoot/office-suite-issues/getobject-createobject-behavior (https://learn.microsoft.com/en-us/office/troubleshoot/office-suite-issues/getobject-createobject-behavior)

Option Explicit

Function isXlOpen(fullFileName) As Boolean
    Dim xlApp As Object
    Dim errNo As Long

    On Error Resume Next
    Set xlApp = GetObject(fullFileName).Application
    errNo = Err
    On Error GoTo 0
   
    If errNo = 432 Then
        isXlOpen = False
    Else
        isXlOpen = True
       
    End If

    Set xlApp = Nothing
End Function

Sub test()
    Debug.Print isXlOpen("C:Temp\Sample.xlsx")
End Sub


Quote from: iamatempuser on October 01, 2022, 12:13:37 PM
I don't see how I could get this information without intercepting events (such as Workbook.BeforeClose event). Am I missing something? 


That second part has nothing to with previous one.
The first (recommended) checks independently if the file is open.


The second method requires a control over the life cycle of the excel object.
You would create the object, attach an eventlistener, and handle the event beforeclosed accordingly.
More complicated and prone to unforeseen exceptions.






To make it short.


In your UI (for example a form), you would display
- a hint like "Press button OPEN to open the excel file. Do so and so, then save and close the file".
- the according button with the code to open the excel file.


- a second hint "When editing finished, press the button ''Post Processing'' ".
- and the button "Post Processing". Here you'll check if the file is closed (cf code above), then open and read it for your actual post processing. When finished reading close the file and kill the excel app.
Title: Re: Listen to Excel events from Visio
Post by: Croc on October 03, 2022, 08:16:53 AM
You can also store in Visio when the data source was last modified.
If you want to check if the source has been modified, you can check the .DateLastModified property and compare it to the stored value.
For example, so
Sub ShowFileAccessInfo()
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile("Test.xls")
    s = "Last Modified: " & f.DateLastModified
    MsgBox s
End Sub
Title: Re: Listen to Excel events from Visio
Post by: Croc on October 03, 2022, 11:15:07 AM
QuoteThe ideal workflow would be to detect when the excel file is closed/saved and update the values in visio by reading workbook's cells again.
Do you have any idea how to do something like this?
A little more food for thought...
The namespace System.IO contains the FileSystemWatcher class.
(See https://learn.microsoft.com/en-us/dotnet/api/system.io.filesystemwatcher?view=net-6.0)
This class can be customized to a given file. Then it will be possible to use the OnChanged handler.
This would allow us to avoid periodically check the file.
But unfortunately FileSystemWatcher doesn't work with VBA. So to use it, we will have to develop an application or Addon in C#. This is a more complicated way and most likely it is not suitable in this case. But we must remember that such a possibility exists.
Title: Re: Listen to Excel events from Visio
Post by: wapperdude on October 03, 2022, 03:54:00 PM
A couple of logistic issues occur to me. 
>  Who, how is the Excel file created/edited?  Only thru Visio?  Where is it located?  How is the file name established? 
>  When/how often is the Excel file checked?  This can have impact upon system resources/Visio functionality.
>  Can the Excel file be changed by anyone other than whomever is currently editing the Visio file while it is open for editing? 

These are corroborative/database related issues. 
Title: Re: Listen to Excel events from Visio
Post by: Croc on October 03, 2022, 07:52:44 PM
Checked how FileSystemWatcher works. I had to wrap it in an Add-on and put it between Visio and Excel.
The construction turned out clumsy, but the idea is workable.
Title: Re: Listen to Excel events from Visio
Post by: ovi on October 03, 2022, 11:17:36 PM
I decided to follow Yacine's suggestion and avoid intercepting Excel events.

This is a demo of my final solution:
https://youtu.be/RdJ3sjfq1uo

The file I used is in the attachments. I also post the code here.


Public Sub OnExcelEdit(vsoShape As Visio.IVShape)
    Dim excelApp As Object
    Dim excelBook As Object
    Dim fullFilePath As String
    Dim i As Integer
   
    ' ask user to save in case of a new document
    If ActiveDocument.path = vbNullString Then
        MsgBox Prompt:="Save the file before using this function", Buttons:=vbCritical, title:="Action required"
        Exit Sub
    End If
   
    ' create and open an excel file
    Set excelApp = CreateObject("Excel.Application")
    Set excelBook = excelApp.Workbooks.Add ' TODO: tell to override if same name
    excelApp.Application.Visible = True
   
    ' fill excel cells with current values in Shape data
    With excelBook.ActiveSheet
        For i = 0 To vsoShape.Section(visSectionProp).Count - 1
               
            ' on column A display property's label
            .Cells(i + 1, 1).value = vsoShape.CellsSRC(visSectionProp, i, visCustPropsLabel).ResultStr(0)
               
            ' on column B display property's current value
            .Cells(i + 1, 2).value = vsoShape.CellsSRC(visSectionProp, i, visCustPropsValue).ResultStr(0)
        Next i
    End With
   
    ' save the file (with name ShapeData_MasterName_ShapeId.xlsx) in the same folder as the visio file
    fullFilePath = ActiveDocument.path + "ShapeData_" + vsoShape.Master.Name + "_" + CStr(vsoShape.ID) + ".xlsx"
    If FileExists(fullFilePath) Then
        Kill fullFilePath ' deleate previous version if present
    End If
    excelBook.SaveAs fullFilePath
   
End Sub

Public Sub OnExcelRefresh(vsoShape As Visio.IVShape)
    Dim excelApp As Object
    Dim excelBook As Object
    Dim path As String
    Dim fileName As String
   
    ' ask user to save in case of a new document
    If ActiveDocument.path = vbNullString Then
        MsgBox Prompt:="Save the file before using this function", Buttons:=vbCritical, title:="Action required"
        Exit Sub
    End If
   
    fileName = "ShapeData_" + vsoShape.Master.Name + "_" + CStr(vsoShape.ID) + ".xlsx"
    path = ActiveDocument.path + fileName
   
    ' abort if file not found
    If Not FileExists(path) Then
        MsgBox Prompt:="No file called """ + fileName + """ was found in the same folder as this Visio drawing." & vbNewLine & "Make sure to run ""Edit On Excel"" first in order to generate it.", Buttons:=vbCritical, title:="Action required"
        Exit Sub
    End If
   
    ' abort if excel file is still open
    If IsFileOpen(path) Then
        MsgBox Prompt:="The file """ + fileName + """ is currently open in Excel." & vbNewLine & "To update the data close the Excel file and click ""Refresh Excel data"" again", Buttons:=vbCritical, title:="Action required"
        Exit Sub
    End If
   
    ' open Excel file
    Set excelApp = CreateObject("Excel.Application")
    Set excelBook = excelApp.Workbooks.Open(path)
    excelApp.Application.Visible = False
   
    ' update Shape Data with values from column B of excel file"
    For i = 0 To vsoShape.Section(visSectionProp).Count - 1
        vsoShape.CellsSRC(visSectionProp, i, visCustPropsValue).FormulaU = """" + excelBook.ActiveSheet.Cells(i + 1, 2).value + """"
    Next i
   
    ' close file and quit excel
    excelBook.Close
    excelApp.Quit
End Sub

Function IsFileOpen(fileName As String)
    Dim fileNum As Integer
    Dim errNum As Integer
   
    'Allow all errors to happen
    On Error Resume Next
    fileNum = FreeFile()
   
    'Try to open and close the file for input.
    'Errors mean the file is already open
    Open fileName For Input Lock Read As #fileNum
    Close fileNum
   
    'Get the error number
    errNum = Err
   
    'Do not allow errors to happen
    On Error GoTo 0
   
    'Check the Error Number
    Select Case errNum
   
        'errNum = 0 means no errors, therefore file closed
        Case 0
        IsFileOpen = False
     
        'errNum = 70 means the file is already open
        Case 70
        IsFileOpen = True
   
        'Something else went wrong
        Case Else
        IsFileOpen = errNum
   
    End Select
   
End Function

Function FileExists(fullFilePath As String) As Boolean
    Dim onlyName As String
   
    onlyName = Dir(fullFilePath)
   
    If onlyName = "" Then
        FileExists = False
    Else: FileExists = True
    End If
   
End Function






I've used a sligly different method (https://exceloffthegrid.com/vba-find-file-already-open/) to detect if the file is closed because Yacine's way was not working correctly for me (errNo always stays to default value of 0).

Any ideas on how to improve the code are welcome!
Title: Re: Edit Shape Data with Excel using Visio Standard
Post by: Thomas Winkel on October 05, 2022, 07:12:19 AM
nice  8)

I have a similar tool with events:
https://youtu.be/LQpllJpzsU4
http://visguy.com/vgforum/index.php?topic=7581.0

Unfortunately fetching Excel events from Visio only works with early binding which is a problem when deploying the solution.

Edit:
Maybe it is more like that tool:
https://youtu.be/OYpENi7Pg4U
http://visguy.com/vgforum/index.php?topic=7572.0
Title: Re: Edit Shape Data with Excel using Visio Standard
Post by: Surrogate on October 05, 2022, 07:45:36 AM
Quote from: Thomas Winkel on October 05, 2022, 07:12:19 AM
Maybe it is more like that tool:
https://youtu.be/OYpENi7Pg4U
Great video!
(https://i.imgur.com/7oCQeGu.png)
650 views per few minutes? WOW 😲
Title: Re: Edit Shape Data with Excel using Visio Standard
Post by: Thomas Winkel on October 05, 2022, 04:48:07 PM
These videos were set "private" for some years, so the real value is like 0.0004 views per few minutes ;D
Title: Re: Edit Shape Data with Excel using Visio Standard
Post by: ovi on October 07, 2022, 05:22:31 PM
Thanks Thomas! I wish I had discovered these posts before developing my solution from scratch  ::)

Especially the one with events is awesome and more close to what I initially wanted to do. Anyway since I am a newbie (as the label below my nickname kindly reminds me) and since trying to intercept events looks more complicated/error prone, I think that also a 2-step solution is not so bad after all.
Title: Re: Edit Shape Data with Excel using Visio Standard
Post by: Yacine on October 28, 2022, 06:44:42 AM
Quote from: Thomas Winkel on October 05, 2022, 07:12:19 AM
Unfortunately fetching Excel events from Visio only works with early binding which is a problem when deploying the solution.

Hello Thomas,
did you actually manage to get events with late binding? I'm facing the same problem.