"this action cannot be completed because the other application" in VBA when opening Excel app object

Posted on 2008-11-04
Last Modified: 2013-11-27
I have looked through existing questions and although some are related, they do not specifically apply to my issue.  I am opening the Excel Application object to create an excel spreadsheet from Access.  All is fine and I can open new ones even when there are some open EXCEPT if the excel application has a workseet in Print Preview.

If Excel is in this state I cannot manually open or switch to view another Excel spreadsheet, and when I attempt this in code, I get the error message noted in the title of my question.  If I manually close the print preview screen, badabing, my new excel sheet pops up and there is no issue.

I thought of trying to test the status of the excel app object, but the message occurs on the line that is just setting the object  "Set xlApp = GetObject(, "excel.application")".

What I am getting is that message flashing quickly, and though I am able to go to excel and change the status to close print preview, it's not what I want my users to have to do, I would like to be able to test if that status were true (B4 the message occurs), close the print preview and have my new excel sheet open up and be the active worksheet.

Any ideas out there?

Question by:L-aura
    LVL 8

    Expert Comment

    you might try error trapping and popping up a message box to inform the user to close print preview before continuing.

    Author Comment

    I don't get an error number, just the crazy blicking message box telling me that the app is busy. Arghhhh.

    Author Comment

    FYI, it's pretty easy to replicate this issue....  you just open an excel spreadsheet, go to print preview, then attempt to run this code:

    You never get to the problemo msgbox.
    Public Sub TestExcelStatus()
    Dim xlApp As Object
    On Error GoTo problemo
        Set xlApp = GetObject(, "excel.application")
       Debug.Print xlApp.WindowState
      If xlApp.ActiveWorkbook.PrintPreview = True Then
        MsgBox "Print preview"
      End If
     MsgBox "there is a problem"
    End Sub

    Open in new window

    LVL 8

    Accepted Solution

    Wow, that crashed my PC.
    I would say you found quite the bug.  I'm thinking the only way around it would be to find some kind of API function that looked at OLE or Hwnd properties before trying to grab hold of Excel (if you want to grab an already open excel session).
    Another option would be to instantiate a new excel object with CreateObject, open or create the workbooks, and then close the object.
    I changed your Set xlApp = GetObject(, "excel.application") to
    Set xlApp = CreateObject("excel.application")

    and added
    xlApp.visible = true

    (so that I could see the new session)

    and it circumvented the issue.  The ActiveWorkbook line will error because there is no open workbook yet, but you get the picture.  I think CreateObject is what you should do.

    I'm sorry I can't help much more than that!

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now