WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Tuesday, August 6, 2013

Saving an Excel workbook using Excel Services and Powershell on Sharepoint 2013

Welcome, I can't imagine too many scenarios where someone would want to open and "save as" an Excel file in Sharepoint 2013 with Excel Services, but if you do, here is how I implemented this via Powershell.  This was strictly a trial attempt at work, so its all pretty straight hard coding.

The long and short of it, SSRS was dropping an Excel file on Sharepoint and the Power[Pivot or whatever new name is] didn't recognize the Excel file, so one thought was simply using ExcelServices to open and do a "Save as" to make Sharepoint and Power_ realize it really was an Excel file.

This short script connects to the Excel Service, opens the workbook and does a SaveWorkbookCopy and closes.  (As of now, the documentation for SaveWorkbookCopy seems to be different than the implementation).  One thing I noticed is how quick this is considering the size of some of the workbooks - so it must be doing the work completely on the web service.  I could be wrong, but that is how it "seems".

 $URI = "https://your.sharepoint.url/site/_vti_bin/excelservice.asmx"  
 $proxy = New-WebServiceProxy -Uri $URI -UseDefaultCredential  
 $culture = "en-US"  
 $wkbook = "https://your.sharepoint.url/site/UnrecognizeExcel.xlsx";  
 $newwkbook = "https://your.sharepoint.url/site/RecognizedExcel.xlsx";  
 $wsStatus = $null  
 $sessionId = $proxy.OpenWorkbook($wkbook,$culture,$culture,[ref]$wsStatus)