2

As the title says, I am trying to get the Task Scheduler to do as follows:

  • Open an specific Excel file at an specific time every day and run the module 1 within said file.

The workbook name is Daily Invoiced, and it contains a Macro, therefore the file is an xlms. The code I need to run is in the Module 1, therefore this is the one that needs to be selected.

This file is stored in my folder C:\Users\MartiJor1\Documents\MACROS\Daily Invoiced.

What have I done so far? Followed the example listed in https://www.thespreadsheetguru.com/blog/how-to-automatically-run-excel-vba-macros-daily where the plan is to use a Script. Here is what mine ends looking like:

'Input Excel File's Full Path
  ExcelFilePath = "C:\Users\MartiJor1\Documents\MACROS\Daily Invoiced ZAMSOTC02 LAC TEAM.xlsm"

'Input Module/Macro name within the Excel File
  MacroPath = "Module1.LAC_Daily_Invoiced_Report"

'Create an instance of Excel
  Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
  ExcelApp.Visible = True  'or "False"

'Prevent any App Launch Alerts (ie Update External Links)
  ExcelApp.DisplayAlerts = False

'Open Excel File
  Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code
  ExcelApp.Run MacroPath

'Save Excel File (if applicable)
  wb.Save

'Reset Display Alerts Before Closing
  ExcelApp.DisplayAlerts = True

- Note: If I execute the script with the Windows Based Script Host, it runs smoothly.

I also used the rest of the configuration suggested in the article for the script to run, except I am using Windows 10 Pro. However, nothing happens at the scheduled hour, nor when I run it manually. On the Last Run Result I can see the code (0x41303) which is an error code related to a date in the past. Does not matter how many times I amend it for the near future, it does not run.

I can not see the error. I believe it must be somewhere in the Actions Tab, and I arrive to this conclusion by the fact that I can run the Script with the Windows Based Script Host as mentioned above. Any idea guys?

This is a message I receive everytime before saving, is this ok?

This is my actions configuration

Here is also the script from the Task when I export it:

<Task version="1.4" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
  <RegistrationInfo>
    <Date>2020-10-31T21:41:26.1715916</Date>
    <Author>GUWW\MartiJor1</Author>
    <Description>Test</Description>
    <URI>\DailyInvoiced</URI>
  </RegistrationInfo>
  <Triggers>
    <CalendarTrigger>
      <StartBoundary>2020-11-02T14:03:00</StartBoundary>
      <Enabled>true</Enabled>
      <ScheduleByDay>
        <DaysInterval>1</DaysInterval>
      </ScheduleByDay>
    </CalendarTrigger>
  </Triggers>
  <Principals>
    <Principal id="Author">
      <UserId>S-1-12-1-4092016482-1174842289-577708437-4023581491</UserId>
      <LogonType>Password</LogonType>
      <RunLevel>LeastPrivilege</RunLevel>
    </Principal>
  </Principals>
  <Settings>
    <MultipleInstancesPolicy>Parallel</MultipleInstancesPolicy>
    <DisallowStartIfOnBatteries>false</DisallowStartIfOnBatteries>
    <StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
    <AllowHardTerminate>false</AllowHardTerminate>
    <StartWhenAvailable>true</StartWhenAvailable>
    <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
    <IdleSettings>
      <StopOnIdleEnd>false</StopOnIdleEnd>
      <RestartOnIdle>false</RestartOnIdle>
    </IdleSettings>
    <AllowStartOnDemand>true</AllowStartOnDemand>
    <Enabled>true</Enabled>
    <Hidden>false</Hidden>
    <RunOnlyIfIdle>false</RunOnlyIfIdle>
    <DisallowStartOnRemoteAppSession>false</DisallowStartOnRemoteAppSession>
    <UseUnifiedSchedulingEngine>true</UseUnifiedSchedulingEngine>
    <WakeToRun>true</WakeToRun>
    <ExecutionTimeLimit>PT0S</ExecutionTimeLimit>
    <Priority>7</Priority>
    <RestartOnFailure>
      <Interval>PT1M</Interval>
      <Count>3</Count>
    </RestartOnFailure>
  </Settings>
  <Actions Context="Author">
    <Exec>
      <Command>"C:\Windows\System32\cscript.exe"</Command>
      <Arguments>"C:\Users\MartiJor1\Documents\MACROS\Daily.vbs"</Arguments>
    </Exec>
  </Actions>
</Task>

Thanks in advance.

Jorge Luis Martínez Nieto
  • 93
  • 7
  • Did you check "Run with highest privileges" in 'General Tab'? Otherwise, VBScript may not run because of some security Windows issues. Does **the task** (not the script) run when try it manually, but from the task scheduler? I mean, select the task in the Task Scheduler Library -> right click and choose 'Run'. – FaneDuru Oct 31 '20 at 21:41
  • Hi Fane, I checked the "Run with Highest Privileges" but no change. And no, the task does not run either when I try that. What I tried to say in my post is that the script works fine, the issue is within a step in the Task Scheduler. – Jorge Luis Martínez Nieto Oct 31 '20 at 21:54
  • What I do not understand is if you tried running **the task**, as I tried explaining above. If you tried and it does not work, this should mean that the path to CScript or to the VBScript are wrong, or wrongly written in the 'Action' tab. It should be `C:\Windows\System32\WScript.exe` in the 'Program/script' box and `"C:\Folder 1\your script.vbs"`. I mean, the path to be between double quotes. I mean, if there are spaces in the path, the double quotes are strictly necessary. Are they like this? – FaneDuru Nov 01 '20 at 09:46
  • Please, confirm that you tried manually running the task. Not the script... If yes, even if my above supposition is true, you would receive an error saying something connected to the string up to the space, inside the script path... I use such a task, starting a vbscript to restart a service if is stopped and works very well. If it still does not work, try exporting the task (right click on the task and choose 'Export'), open the created XML file in Notepad, copy the content, edit your question and copy the xml file text there. – FaneDuru Nov 01 '20 at 09:57
  • Hi Fane. I can confirm the task does not run manually. No error show at all. I added the code! Hope you can check it out. – Jorge Luis Martínez Nieto Nov 02 '20 at 13:07
  • I compared to the XML file with mine and I cannot see something which certainly will solve your problem... But, please try the next configuration suggestions: 1. Try setting in General tab 'Configure for:' Windows 10, if not so configured. 2. My task uses `Windows\System32\WScript.exe`, but it should work with 'cscript.exe', too... 3. `2020-11-02T14:03:00` would mean that the task should run today at 03 AM... 4. `LeastPrivilege` is wrong. It must be `HighestAvailable`. A script cannot be automatically run without 'Run with highest privileges', I think.... – FaneDuru Nov 02 '20 at 13:37
  • Hi Fane, thanks for the advices. I think I found the problems. First, everytime that I save the TS prompts me the message: This task requires that the user account specified has log on as batch job rights for more information about setting this policy. So i think I must fix this first. Secondly, when I save after ticking on Highest Level Available it prompts me the message: task scheduler cannot apply your changes user account is unknown, the password is incorrect, or the user account does not have permission to modify the task. So this is the other rock on the road – Jorge Luis Martínez Nieto Nov 02 '20 at 17:07

0 Answers0