0

I have a powershell script that opens an Excel file every day in the morning, runs a macro and closes. This was working fine until recently that we moved our legacy network drives to Sharepoint Online.

The script, if I run it manually, starts and does what it has to do. However, when I place it on Task Scheduler it doesn't open the file.

I have tried to:

  • Placing the script outside my OneDrive folder: The script runs but the file won't open.
  • Placing both script and file outside OneDrive and Sharepoint: The script runs and the file opens.
  • With both in OD/SP, run a task scheduler to open a Powershell window and running the script from there: The first window opens but Powershell closes when running the second script.

The user that runs the Task, myself, has full control over the folders where both script and file are located and, as mentioned, in all these cases the script works if it's ran manually. I don't seem to be able to find anything on the internet, nor on the logs (Unless I#m blind there aren't any logs created after all the previous tasks ran), and I'm starting to run out of hairs to pull.

Does anyone have any idea/advise?

Thanks!

Arehandoro
  • 121
  • 9
  • You could add `Start-Transcript` & `Stop-Transcript` to your script to get some detailed txt file logging of what your script is doing in the scheduler, and where it might be failing. My first thought is the notorious double hop issue, but without any code/error examples it's hard to pinpoint where it's failing. – doenoe Sep 27 '19 at 13:20
  • What do you mean the double hop? Opening a Powershell window to make this one opening the script? That was only for testing, though. I'll give it a go with your suggestions, thanks! – Arehandoro Sep 27 '19 at 14:51
  • @PimpJuiceIT Thanks for your reply, here is the .xml file :) https://pastebin.com/HxMDjFBh – Arehandoro Sep 30 '19 at 08:31
  • Thanks so much! having a look now :) – Arehandoro Sep 30 '19 at 12:21

1 Answers1

3

Task Scheduler PowerShell script not running

Based on what you describe and what I see in the XML file per your configuration where you have. . .

<Exec>
  <Command>"C:\Path\To\OneDrive - Company Ltd\Scripts\pwsh\DonwloadOpen.ps1"</Command>
  <Arguments>-ExecutionPolicy Bypass</Arguments>
<Exec>

I think that needs to be configured like this instead to execute the PowerShell from Task Scheduler. . .

<Exec>
  <Command>Powershell</Command>
  <Arguments>-ExecutionPolicy Bypass -File "C:\Path\To\OneDrive - Company Ltd\Scripts\pwsh\DonwloadOpen.ps1"</Arguments>
</Exec>

So from the Action tab you will create an Action defined as:

  • Action: Start a program
  • Program/script: Powershell
  • Add arguments (optional): -ExecutionPolicy Bypass -File "C:\Path\To\OneDrive - Company Ltd\Scripts\pwsh\DonwloadOpen.ps1"
  • Start in (optional): C:\Windows\System32\WindowsPowerShell\v1.0 enter image description here

Furthermore, some of these options in your case may help but based on the security and your configuration, I'm not 100% they are needed but easy enough to test. . .

From the General tab of the scheduled task, be sure the Run whether user is logged on or not and the Run with highest privileges options are both selected.

enter image description here

Additional Resources

Community
  • 1
Vomit IT - Chunky Mess Style
  • 40,038
  • 27
  • 84
  • 117
  • Thanks for your detailed answer. Unfortunately, the same situation happens :( – Arehandoro Sep 30 '19 at 12:47
  • @Karasu-O-Matic Can I get another pastebin to the logic of the Powershell masking out anything sensitive, etc.? Also, can you ensure the spelling of the full path as per the scheduled task configuration of `"C:\Path\To\OneDrive - Company Ltd\Scripts\pwsh\DonwloadOpen.ps1"` is accurate with no typos, etc.? Can you also tell me if putting it in another shorter path or folder path without spaces of hyphens [`-`] make any difference? – Vomit IT - Chunky Mess Style Sep 30 '19 at 12:53
  • @Karasu-O-Matic Furthermore, test with `-ExecutionPolicy Bypass -Command "& 'C:\Path\To\OneDrive - Company Ltd\Scripts\pwsh\DonwloadOpen.ps1'"` syntax in the add arguments field and see if that does anything different. I'm not certain it'll make much difference but just in case. – Vomit IT - Chunky Mess Style Sep 30 '19 at 13:05
  • Hello, this is the pastebin for the script https://pastebin.com/ufrWSBeQ very simple, as you can see. I have tried with your second suggestion and same result, it won't work (manually still does). However, after the trigger, an Excel process is created with my user (the one I'm running the Task Scheduler from) with the column "UAC Virtualization" on Task Manager set as "Not Allowed". Excel must run but fail at some point before opening the file. – Arehandoro Sep 30 '19 at 14:07
  • @Karasu-O-Matic I assume you already tried with the "**Run with highest privileges**" both check and not check and with both "**Run only when user is logged on**" and "**Run whether user is logged on or not**" and all variations with the "**Run with highest privileges**" checked but it not, try all variations just in case. Furthermore, if none of that helps, see if adding this PS script logic https://pastebin.com/6Dc2cjt8 helps resolve the problem but with all the variations of those settings as indicated too though. – Vomit IT - Chunky Mess Style Sep 30 '19 at 15:37
  • Thanks for your help and advises, but unfortunately still doesn't work. Same situation with all the attempts, the task creates a process in the system but Excel won't open. Maybe it is a Sharepoint thing, I should also ask on that community. – Arehandoro Oct 01 '19 at 12:54
  • @Karasu-O-Matic I just found another post that suggests to make these two folders so from admin elevated command prompt on the machine that this is an issue, run `md "C:\Windows\System32\config\systemprofile\Desktop"` and then run `md "C:\Windows\SysWOW64\config\systemprofile\Desktop"` and now try to run the process and see if that changes it. Let me know if that helps resolve. – Vomit IT - Chunky Mess Style Oct 01 '19 at 13:38
  • We are unlucky, still the same issue. I have tried to simplify the action as part of troubleshooting and I have discovered that the powershell script runs to fetch a file from sharepoint online (for example) no matter where the script resides. However, task scheduler WON'T open the excel file for as long as the file resides on one drive or a synced sharepoint site. – Arehandoro Oct 02 '19 at 15:29
  • @Karasu-O-Matic .... I just did some testing and it appears to work as expected as long as I have the Excel file to open in a location that the account executing it can access. I tested local and UNC paths. Can you check and confirm that you have the the account specified in the "**when running the task, use the following user account**" field set as the same account you use when you run from PowerShell and it works.... see this screen shot for example of how it works for me.... https://i.imgur.com/Zw3IsWQ.png. The location the Excel file resides, this account has access to open and save? – Vomit IT - Chunky Mess Style Oct 02 '19 at 16:56
  • I can't select the entire directory, only local computer. The company I work for is Azure Joined only, so in the task schedule in the Author box appears: AzureAD\MyName but in the "When running the task, use the following user account" it says only MyName. If I click on "Change User or Group" I can select any local group/user, but nothing else. Could that be the issue? – Arehandoro Oct 07 '19 at 08:41
  • @Karasu-O-Matic Possibly but I think you can probably just export the task to an XML file, then open that XML file with a text editor, and then update the userid field to the value of the domain account's SID, delete the task, and then import from task scheduler the modified XML to recreaste the job with the new settings.... Scree shot --> https://i.imgur.com/ukKscaX.png ... So you should be able to test to see if that is the problem. – Vomit IT - Chunky Mess Style Oct 07 '19 at 11:25