"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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 8
you might try error trapping and popping up a message box to inform the user to close print preview before continuing.
I don't get an error number, just the crazy blicking message box telling me that the app is busy. Arghhhh.
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.
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
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).
HOWEVER:
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.applic
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!
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).
HOWEVER:
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.applic
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!
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.