Permission Denied When Trying To Open An Excel Object


I have a VB6 application that takes data from an access database and populates an excel spreadsheet. I am running into a problem with some users of the program that when they try to run the report that populates the excel spreadsheet they get an "Access Denied" error. As a workaround what I do is to go their machine, press Control-Alt-Delete and removing an instance of excel that is stuck in memory. This happens about 2 - 3 percent of the time for the users, but it does happen. I am not sure why. Here is some of the code in the program:

Set oExcel = CreateObject("Excel.Application")
Set oExcelWorkBook = oExcel.Workbooks.Open("C:\BlahBlah.XLS")
Set oExcelWorkSheet = oExcelWorkBook.ActiveSheet

oExcelWorkSheet.SaveAs "C:\Blah.XLS"
Set oExcel = Nothing
Set oExcelWorkBook = Nothing
Set oExcelWorkSheet = Nothing

I can't see anything wrong with the code. Is there a way to see if there is an instance of excel in memory and if there is to delete it? Has anyone experienced this problem before?



Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Patrick MatthewsCommented:
Before creating the Excel.Application object...

On Error Resume Next

Set oExcel = GetObject(, "Excel.Application")
If Err = 0 Then
    Set oExcel = Nothing
End If

On Error GoTo 0

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 trial
Mark1110Author Commented:

Will this work if the user just go into the application then runs a report? How about if the user has a couple of excel spreadsheets open? How will this solution distinguish between the excel instances and know which one to close? When you enter the command Set oExcel = GetObject(, "Excel.Application") and get the permission denied error, is oExcel still set? I still don't see how I can delete the bad instance of excel in memory that is causing the access denied error.

Patrick MatthewsCommented:

The code I supplied will grab *some* instance of Excel if there already is one running, and kill it.

You may need to run that in a loop to grab and kill all instances of Excel.


It'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
Visual Basic Classic

From novice to tech pro — start learning today.