Permission Denied When Trying To Open An Excel Object

Posted on 2008-11-06
Last Modified: 2013-11-26

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?



Question by:Mark1110
    LVL 92

    Accepted Solution

    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

    Author Comment


    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.

    LVL 92

    Expert Comment

    by:Patrick Matthews

    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.



    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now