locked
Power shell scripting | Schedule job | Excel VBA RRS feed

  • Question

  • Hi,

    I am trying to schedule a job using windows power shell scripting to run an excel vba macro automatically. 

    Power shell code

    $excel = New-Object -comobject excel.application

    $workbook = $excel.workbooks.open("C:\Users\Chaitu\Desktop\Workbook.xlsm")

    $worksheet = $workbook.worksheets.item(1)

    $excel.run("Charts")

    $workbook.save()

    $workbook.close()

    $excel.quit()

    when I run the "Start-Job" cmdlet, the macro is running fine.

    Start-Job -FilePath C:\Users\Chaitu\SkyDrive\Documents\MyScripts\ExcelAutomation.ps1

    But when I use the scheduled jobs, the excel VBA macro is not running. 

    Register-ScheduledJob -Name Chart -FilePath C:\Users\Chaitu\SkyDrive\Documents\MyScripts\ExcelAutomation.ps1 -Trigger (New-JobTrigger -Once -At 10PM)

    Id     Name            PSJobTypeName   State         HasMoreData     Location             Command                  
    --     ----            -------------   -----         -----------     --------             -------                  
    31     Job31           BackgroundJob   Completed     True            localhost            $excel = New-Object -c...

    The Get-job command shows the background job as completed. But when i check the worksheet, the chart is not being created. Please advise.


    • Moved by Bill_Stewart Monday, April 20, 2015 7:23 PM Off-topic
    Sunday, December 14, 2014 7:15 PM

Answers

  • Scripts that make use of MS Office applications will only run as scheduled jobs when the job runs under an account that is currently logged on.
    • Proposed as answer by Frederik Long Tuesday, December 23, 2014 3:45 PM
    • Marked as answer by Just Karl Thursday, April 30, 2015 4:46 PM
    Sunday, December 14, 2014 7:33 PM
  • Just create a task that seelcts to run only when the user account is logged in.

    It is easiier to do this with the task scheduler than with PowerShell.


    ¯\_(ツ)_/¯

    • Marked as answer by Just Karl Thursday, April 30, 2015 4:46 PM
    Sunday, December 14, 2014 8:08 PM

All replies

  • Scripts that make use of MS Office applications will only run as scheduled jobs when the job runs under an account that is currently logged on.
    • Proposed as answer by Frederik Long Tuesday, December 23, 2014 3:45 PM
    • Marked as answer by Just Karl Thursday, April 30, 2015 4:46 PM
    Sunday, December 14, 2014 7:33 PM
  • Aside from Frederik's comment the use of any Graphical rendering tools such as Chart will almost never work without a full visible session.  There are companies that make tools that can chart from Excel in a schedultled task but they do not use Excel to do it.


    ¯\_(ツ)_/¯

    Sunday, December 14, 2014 7:50 PM
  • Could you please explain how I can run a scheduled job under an account that is currently logged in? I have checked all the possible cmdlets, but have not found any way to do this.
    Sunday, December 14, 2014 8:00 PM
  • Just create a task that seelcts to run only when the user account is logged in.

    It is easiier to do this with the task scheduler than with PowerShell.


    ¯\_(ツ)_/¯

    • Marked as answer by Just Karl Thursday, April 30, 2015 4:46 PM
    Sunday, December 14, 2014 8:08 PM