powershellsharepointsharepoint-2010

Opening an Excel document from SharePoint using PowerShell

I'm trying to open an Excel workbook from SharePoint using PowerShell. I'm not loading the SharePoint snap-in—I do not have it.

When PowerShell tries to launch the workbook, SharePoint prompts for credentials. The problem is that we're trying to schedule the script, and we would like the script to have an SSO-like experience.

Here's the MWE:

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open(http://site/file.xlsx)
$Worksheet = $Workbook.Sheets.Item($WorksheetName)
$Excel.Visible = $false

# some Excel manipulation

$Workbook.Close($false)
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
Remove-Variable Excel
Solution

  • First, download the SharePoint file locally, then open, manipulate, etc.

    The example below uses the default creds of the user running the script/PowerShell session.

    $fromfile = "http://site/file.xlsx"
$tofile   = "c:\somedirectory\file.xlsx"

$webclient = New-Object System.Net.WebClient
$webclient.UseDefaultCredentials = $true
$webclient.DownloadFile($fromfile, $tofile)

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($tofile)
$Worksheet = $Workbook.Sheets.Item($WorksheetName)
$Excel.Visible = $false

# some Excel manipulation

$Workbook.Close($false)
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
Remove-Variable Excel