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.
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.
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.
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.
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"
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?
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.
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.
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
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.
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.
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.
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!
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
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 😲
These videos were set "private" for some years, so the real value is like 0.0004 views per few minutes ;D
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.
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.