I have a problem with my VBA code.
My task is to create VBA script which will scan directory for Visio drawings, update an embedded Excel object, save it and close it.
So I developed a VBA sub:
Dim m_visioApp as Object
Set m_visioApp = CreateObject("visio.application")
Sub UpdateVsdFile(filename As String)
m_visioApp.Documents.Open (filename)
For Each pageObj In m_visioApp.ActiveDocument.Pages
For Each oleObj In pageObj.OLEObjects
If InStr(oleObj.progID, "Excel.Sheet") > 0 Then
oleObj.Object.Activate
oleObj.Object.Application.Worksheets(1).Cells(1, 1) = "Something"
Call oleObj.Object.RefreshAll
End If
Next
Next
m_visioApp.ActiveWindow.DeselectAll
m_visioApp.ActiveDocument.SaveAsEx filename, visSaveAsListInMRU
m_visioApp.ActiveDocument.Close
End Sub
What is not working: - saving Visio drawing after OLE object update - closing Visio drawing
I'm using Visio 2016 and Excel 2016.
Can any one help to check what I'm doing wrong? Or what I'm missing here?