Visio crashes if Excel says "Microsoft Excel is waiting for another application"

Started by Hey Ken, January 13, 2021, 04:22:34 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Hey Ken

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

   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

Ken V. Krawchuk
Author
No Dogs on Mars - A Starship Story
http://astarshipstory.com

Croc

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.

Hey Ken

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

Ken V. Krawchuk
Author
No Dogs on Mars - A Starship Story
http://astarshipstory.com

Nikolay

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

Croc

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

Hey Ken

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

Ken V. Krawchuk
Author
No Dogs on Mars - A Starship Story
http://astarshipstory.com

Croc

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.