Permission Denied When Trying To Open An Excel Object

HI,

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"
oExcel.Quit
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?

Thanks,

Mark


Mark1110Asked:
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
    oExcel.Quit
    Set oExcel = Nothing
Else
    Err.Clear
End If

On Error GoTo 0
0
 
Mark1110Author Commented:
HI,

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.

Mark
0
 
Patrick MatthewsCommented:
Mark,

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.

Regards,

Patrick
0
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.