Link to home
Create AccountLog in
Avatar of L-aura
L-aura

asked on

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

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?

Laura
Avatar of tony_angelopoulos
tony_angelopoulos
Flag of United States of America image

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

ASKER

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

ASKER

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"
  Else
  
  End If
  
problemo:
 MsgBox "there is a problem"
 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of tony_angelopoulos
tony_angelopoulos
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer