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?
Patrick MatthewsConnect With a Mentor Commented:
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
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.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.