locked
Scripts will Run Manually but not from Scheduled Task RRS feed

  • Question

  • I have two scripts, a bat file and a vbs where the bat file calls the vbs and the vbs file opens Excel.  When I run the bat file manually it works perfectly fine and all items execute.  When I schedule the bat file it hangs at Running.  I wanted to see if there are any items in my scripts that could cause this?  I have checked everything from the Scheduled Task side from permissions all the way down and everything seems in order.  SYSTEM is running and SYSTEM has full rights to the file locations.

    Scripts are below.  Any input would be appreciated.

    bat file

    cls
    @ECHO OFF
    wscript.exe "C:\ReportSubscriptions\SalesDashboard\SalesDashboardRun.vbs"
    exit

    vbs file



    Dim objExcel, objWorkbook 

    Set objExcel = CreateObject("Excel.Application")
    ObjExcel.DisplayAlerts = False
    Set objWorkbook = objExcel.Workbooks.Open("C:\ReportSubscriptions\SalesDashboard\Sales Dashboard 80s M3 Auto Email TEST SMTP.xlsm")
    'objworkbook.Saved = True
    'objWorkbook.Close False
    ObjExcel.DisplayAlerts = False
    objExcel.Quit

    Set objWorkbook = Nothing
    Set objExcel = Nothing

    WScript.Quit

    • Moved by Bill_Stewart Friday, July 7, 2017 4:40 PM This is not "help me figure out VBA automation from task scheduler" forum
    Tuesday, May 9, 2017 2:04 PM

All replies

  • Heya,

    yeah, I can totally see where it's happening:
    You are using Excel.

    Excel requires a desktop and UI, running as task doesn't have that.

    You cannot use Excel in that manner, rebuild the functionality you need without using Excel (or other office applications) and it should work alright. Also consider moving to PowerShell, it'll make your life easier after the initial investemnt of effort.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, May 9, 2017 2:26 PM
  • Thank you very much for the reply.  So there is no way to launch Excel application via this method?  My requirement is to automate and email a pivot table Excel doc as an attachment.  The VB inside the workbook handles all of that but I need a way to simply open and then close the workbook on a schedule.  Any other ideas how this may be possible?  Powershell perhaps?  I am beginning to learn powershell and could research that as an alternative if it would be possible.
    Tuesday, May 9, 2017 2:37 PM
  • Heya,

    well ... you could use PowerShell either way (and I strongly recommend learning it. Converting the script above would be fairly straightforward).

    There's an OLEDB adapter that allows you to directly manipulate Excel content. This would be standalone capable and more resilient.

    That said, if you need to do it quick-and-dirty (the "avoid-Excel" version requires quite a learning curve, which would be to your long-term benefit), set the task to run under an account that has the necessary privileges and is currently logged on. Set it to run interactively (that is: Only while the user is logged on), and it should have access to Excel and a desktop.

    Given all the potential to fail, this is not recommended, but it would be quick.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, May 9, 2017 3:20 PM