none
Windows 7 Task Scheduler .vbs CreateObject("Excel.Application") Error:70 Permission Denied

    Question


  • I have a .vbs script that I'm trying to move from a Windows XP box to a Windows 7 box

    The script runs correctly manually, but fails when run from the Task Scheduler.


    Similarly, the script has always run correctly from the Windows XP Task Scheduler but fails in the Windows 7 Task Scheduler.


    I have isolated the Error to the following line of code:

    • Set fileobj=CreateObject("Excel.Application")

    I output the err.number and err.description to a Log file and I get:

    • Error:70    Permission denied

    Interestingly, other calls to the CreateObject function inside the same script work fine, including:

    • Set fso = CreateObject("Scripting.FileSystemObject")
    • Set app = CreateObject("SASEGObjectModel.Application.4.3")

    It's only the call to create the Excel object that fails.

    Does anyone know why this would fail in this way:
    Fails when run from the scheduler, but succeeds manually? 
    Works on Windows XP but does not work in Windows 7?

    I found a similar issue here:
    http://social.msdn.microsoft.com/Forums/is/exceldev/thread/ac2549f5-fc1e-494d-9015-70cb31d3aeb2

    However, the solution looked like it was specific to the operating system Windows Server 2008 Rw 64-bit.
    Does anyone know a resolution that works on Windows 7?

    Note: The Windows 7 box has Office 2010 while the Windows XP box has Office 2007



    Sub OpenDRAInvRunMac (WorkBookStr)
    
       Dim fileobj
       Dim XLfile
       Dim appXL
       Dim strWorkBook
       Dim oAddin 
    
    
       strWorkBook = WorkBookStr
    
       Set fileobj=CreateObject("Excel.Application")
       fileobj.Visible = False
       fileobj.WorkBooks.Open(strWorkBook)
    
       If (fileobj.addins("PI-Datalink").installed = True) Then
         fileobj.addins("PI-Datalink").installed = False
       End If
         fileobj.addins("PI-Datalink").installed = True 
    
       fileobj.Run("DRAInvCopy")
    
       fileobj.DisplayAlerts = False
       fileobj.WorkBooks.close
       fileobj.DisplayAlerts = True
       fileobj.quit
    
    
    End Sub
    
    
    
    






    Friday, April 20, 2012 9:13 PM

Answers

  • Not supported.  The fact that it worked on XP is simply an accident, really.  You should review the post here.

    Basically, what it boils down to is the simple fact that Excel is a client application and is intended to be used by a human being with an interactive desktop.  Running Excel from a scheduled task or a service denies it of this simple requirement.  If someone is logged onto a desktop, you can automate most Excel functions, but you will continue to beat your head against a wall, trying to automate it from a non-interactive desktop.


    Saturday, April 21, 2012 3:32 AM

All replies

  • Not supported.  The fact that it worked on XP is simply an accident, really.  You should review the post here.

    Basically, what it boils down to is the simple fact that Excel is a client application and is intended to be used by a human being with an interactive desktop.  Running Excel from a scheduled task or a service denies it of this simple requirement.  If someone is logged onto a desktop, you can automate most Excel functions, but you will continue to beat your head against a wall, trying to automate it from a non-interactive desktop.


    Saturday, April 21, 2012 3:32 AM
  • I have identical problem with Task Scheduler on Windows 7 64 bits with Office 2007

    I run Task that executes batch file that:

    Step 1. opens Access file and executes some updating of internal tables

    Step 2. runs VBA code that creates Excel application object, opens existing XLS file and refreshes pivot table, closes it

    The script works as expected when

    - manualy executed from command line

    - executed task with "Run only when user is logged on" set

    But when I switch "Run whether when user is logged on or not" it makes updates to tables but locks and does not update the XLS file.

    First of all setting "Run whether when user is logged on or not" is somehow missleading. If I'm logged in I should see execution of the Task. Instead the task is hidden even if "Hidden" option is unchecked.

    Secondly Chris wrote that Excel is client application. My question is Access any differrent from Excel?

    If any one knows how to refresh Excel data with task scheduler will be welcome.

    Friday, May 18, 2012 1:29 PM
  • sabado is right it's not supported, but there is a workaround. 

    it worked for me so give it a try.

    Resolution

    ************

    • A “Desktop” folder seems to be necessary in the “systemprofile” folder in the location C:\Windows\SysWOW64\config\ to open an Excel file
    • Create the “Desktop” folder for Windows 2008 Server (x64) under the location C:\Windows\SysWOW64\config\systemprofile
    • And for a 32 bit Windows 2008 Server create the “Desktop” folder under the location C:\Windows\System32\config\systemprofile
    • After creating the folder the SQL Server jobs should execute successfully

    source

    blogs.msdn.com/b/sqlserverfaq/archive/2010/04/30/unable-to-open-excel-files-using-a-cscript-in-sql-server-jobs.aspx

    Saturday, August 03, 2013 3:59 PM