3

I have a VBS script that takes an Excel file and saves it as a PDF. I call this vbs as within an RScript that is automated through Task Scheduler. The RScript runs just fine when I run it in R manually. However, when I have it scheduled in Task Scheduler, the task says it completes successfully, but the PDF file is not saved. What is different with Task Scheduler running the RScript to cause this to not actually complete?

Here is the RScript, which I have saved as HourlyR.R in the same C:\test folder.

shell.exec("C:/test/HourlyNew.vbs")

Here is the code for the HourlyNew.vbs

Option Explicit

ExcelMacro

Sub ExcelMacro()

  Dim xlApp
  Dim xlBook
  Dim xlSheet

  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Open("C:\\test\\Hourly.xlsm", 0, False)
  Set xlSheet = xlBook.Worksheets("HourlyDashboard")

  xlApp.DisplayAlerts = False
  xlApp.Visible = False


  xlSheet.ExportAsFixedFormat 0, "c:\\test\\HourlyDashboard.pdf", 0, 1, 0, , , 0
  xlBook.Close
  xlApp.Quit

  Set xlBook = Nothing
  Set xlApp = Nothing

End Sub

Here is how I have my Scheduled task set up:

General Tab:
Run whether user is logged in or not selected
Run with highest privileges checked

Actions Tab:
Program/Script: C:\R_Program_Files\R-3.2.0\bin\x64\R.exe
Add arguments (optional): CMD BATCH C:\test\HourlyR.R

GreenboyCJ
  • 33
  • 4
  • [http://blogs.technet.com/b/askperf/archive/2015/02/18/help-my-scheduled-task-does-not-run.aspx](http://blogs.technet.com/b/askperf/archive/2015/02/18/help-my-scheduled-task-does-not-run.aspx) – Bond Jul 14 '15 at 12:38

1 Answers1

0

The problem is probably in Excel, but your best bet is debugging it step by step. First, specify working directory in your task's "Start in" option. Then run task, navigate to that directory and check if .Rout file is created when task is executed. It can contain errors and messages. If R doesn't generate any errors, remove 

xlApp.DisplayAlerts = False
xlApp.Visible = False
xlBook.Close
xlApp.Quit

run task again, and check whether new EXCEL.exe process appears in the task manager. If so, switch on "Run only when user is logged on" option in task settings, that way you should see Excel UI and any messages it displays.

cyberj0g
  • 3,707
  • 1
  • 19
  • 34
  • Specified working directory in "Start in", .Rout was created (whether I specify or not) with no errors. I removed the lines you suggested, still no errors. I changed task to "Run only when user is logged on" option and the the task completes as it should. So the question now is how do I get it to work with the option of "Run when user is logged on or not?" – GreenboyCJ Jul 14 '15 at 11:54
  • See second answer [here](http://stackoverflow.com/questions/1177974/word-interop-not-working-in-a-scheduled-task) – cyberj0g Jul 14 '15 at 12:16
  • Thank you. It seems to have worked. Crazy how just adding one folder (C:\Windows\SysWOW64\config\systemprofile\Desktop) fixes this issue. – GreenboyCJ Jul 14 '15 at 18:51