Folks:
I have a Visio application that opens a spreadsheet and does a lot of stuff with it. I mentioned the basics of the method for accessing Excel from Visio in a recent post (http://visguy.com/vgforum/index.php?topic=9418.msg41970#msg41970).
It takes a long time for my Visio app to do its processing, so I tried using the spreadsheet's StatusBar to display percent complete (native Visio has no such capability). That worked fine, but after a while Excel gets impatient and says, "Microsoft Excel is waiting for another application to complete an OLE action." In general the popup can be ignored—except if you're writing to the StatusBar. If you do that while the OLE popup is being displayed, Visio crashes and says, "Run time error '50290'. Application-defined or object-defined error." Take out the line that updates StatusBar and it works fine, OLE popup or not. So much for my percent complete.
Don't ask me why it crashes; it just does. Think of it as a pothole in the VBA road that you need to steer around. I'm using Visio Professional 2016 and Excel Office 365 ProPlus.
- Ken
This doesn't seem like a Visio developer error.
It's just that the two apps are blocking each other.
Visio cannot access the Excel window due to an open message modal window. And Excel cannot complete its operation because Visio is holding onto it.
If you use a Word-Excel pair instead of a Visio-Excel pair, then most likely you will see a similar error.
Croc:
Excellent point. I'd agree.
But that leaves me with an unasked question: WHY is Excel telling me that? It doesn't need to; I already know it's taking a while to run the Visio code. I just wish it would do it silently. Looks like a question for an Excel forum.
Thanks for the thought,
- Ken
Do you use DoEvents in Visio when processing things (periodically)? If you don't, then you could try that. Calling this may get rid of the "OLE" popup.
I mean, instead of:
For shape in Page.Shapes
' do something with shape
Next
You could try this:
For shape in Page.Shapes
' do something with shape
DoEvents
Next
Hey Ken, how long after the start does the error appear?
I am using Visio 2019 and Excel 2019. I write data to an Excel cell and status bar within some minutes. I am unable to see the error.
Here is my test program
Sub AccessExcelFromVisio()
Dim oldT As Long
Dim newT As Long
Dim ExcelApplication As Variant
Dim YourWorkbook As Workbook
Const FileName = "C:\Work\Ken\Test.xlsx"
Set ExcelApplication = CreateObject("excel.application")
ExcelApplication.Visible = True
Set YourWorkbook = ExcelApplication.Workbooks.Open(FileName)
YourWorkbook.Sheets(1).Range("A1").Value = "Whatever"
oldT = 0
For i = 1 To 100
Do
newT = Timer()
YourWorkbook.Sheets(1).Range("A1").Offset(i, 1).Value = newT
ExcelApplication.StatusBar = "step " & i
Loop Until newT <> oldT
oldT = newT
Next
YourWorkbook.Close
Set YourWorkbook = Nothing
ExcelApplication.Quit
Set ExcelApplication = Nothing
End Sub
Croc:
Thanks for looking into this. It takes about a minute or so before the message appears.
My apologies, but I did leave out one important fact. Once Visio creates the Excel instance, the next step is for the Excel instance to asynchronously connect to Visio and execute a Visio Sub (via right-click menu choice). It's that Visio Sub that takes a long time to run, and Excel is waiting for Visio to finish what Excel initiated.
Here is the tested Excel VBA code for invoking a Visio Sub from the Excel instance that was created by the Visio Sub:
Sub TalkToVisio ()
Dim VisioApp As Object
Dim VisioAppDoc As Object
Set VisioApp = GetObject(, "Visio.Application") 'returns Visio app if it is running, error if not
If Err <> 0 Then
MsgBox " Visio is not available. ", vbInformation
Else
Set VisioAppDoc = VisioApp.ActiveDocument
VisioAppDoc.ExecuteLine "ThisDocument.LongRunningVisioSub"
End If
Set VisioApp = Nothing
End Sub
As an aside, I'd be happy to hear of a better way of connecting Visio and Excel and having Excel execute the Visio Sub. This approach has the problem that only one instance of Visio can be running, or else the Excel macro could possibly latch onto the wrong instance of Visio. But this approach works, and the user instructions tell them to first close all other instances of Visio. And if it does grab the wrong instance, Excel crashes with an "I told you so!" message.
Here's an extra credit question: Do the same connection, except have Excel create the Visio instance, and Visio runs an Excel Sub. But that has the same flaw: how does Visio know which instance of Excel created it?
Someday I will stop living on the extreme edges of Visio functionality. But not today. Besides, it's way too much fun!
Thanks again,
- Ken
Hey Ken,
You can try transferring Visio Document as an object to Excel.
Program in Visio:
Dim ExcelApplication As Variant
Dim YourWorkbook As Workbook
Dim oldT As Long
Dim newT As Long
Dim Flag As Boolean
Sub AccessExcelFromVisio()
Const FileName = "C:\Work\Ken\Test.xlsm"
Set ExcelApplication = CreateObject("excel.application")
ExcelApplication.Visible = True
Set YourWorkbook = ExcelApplication.Workbooks.Open(FileName)
Flag = False
YourWorkbook.Sheets(1).Range("A1").Value = "Whatever"
YourWorkbook.Application.Run "ThisWorkbook.ex", ActiveDocument
Do
Loop Until Flag
YourWorkbook.Close
Set YourWorkbook = Nothing
ExcelApplication.Quit
Set ExcelApplication = Nothing
End Sub
Sub LongRunningVisioSub()
oldT = 0
For i = 1 To 10
Do
newT = Timer()
YourWorkbook.Sheets(1).Range("A1").Offset(i, 1).Value = newT
ExcelApplication.StatusBar = "step " & i
Loop Until newT <> oldT
oldT = newT
Next
Flag = True
End Sub
Program in Excel:
Sub ex(arg1 As Visio.Document)
arg1.ExecuteLine "ThisDocument.LongRunningVisioSub"
End Sub
Visio will call the macro in Excel and pass the Visio document as a parameter.
Excel can then call macros from that document. Including LongRunningVisioSub.