Forum Discussion
How to open an Excel workbook from Sharepoint folder, with credentials
I've got a Powershell script that successfully opens and reads an Excel workbook which is stored in a Sharepoint folder, BUT when I set it up to run under Task Scheduler it fails. Note that when I run this script from a Powershell 7 command prompt, it works properly. Note: when running from command prompt the script DID prompt me for authentication the first time I ran it, but not since.
My code is, essentially:
$username = "email address removed for privacy reasons"
$password = "what3ver" | ConvertTo-SecureString -asPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($username, $password)
[void](Connect-PowerBIServiceAccount -Credential $credential);
[void](Connect-PowerBIServiceAccount -Credential $credential);
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($TargetFile )
When launched from the Task Scheduler, the script hangs indefinitely on the .Open() call in the last line. I assume that to mean it lacks credentials, and can't prompt for them??
Can I specify credentials on the .Open() call? (If so, the Office documentation doesn't indicate so.)
Can I specify credentials on the .Open() call? (If so, the Office documentation doesn't indicate so.)
Note that the email address removed for privacy reasons/what3ver credentials that I use in the Connect-PowerBIServiceAccount() call are valid for the workbook, as well.
Any help would be appreciated.
Notes:
- Running on a Windows Server (2016 standard)
- Office Professional Plus 2016
- Powershell version 7.3.7
Thank you in advance.
8 Replies
- Is the account not blocked/restricted by a Conditional Access policy? MFA?
- Not to my knowledge. And if it were, wouldn't that prohibit the script from successfully accessing the workbook when I run it from the PS command prompt?
Thanks for any insight--conditional access policies and MFA are not something I'm familiar with.- To see what's going on, you could use "Start-Transcript c:\temp\log.txt" as the first line in your script and Stop-Transcript as the last line. You will see all the script output in the log, and perhaps it will show you more information when running it as a Scheduled Task.
