2,187
edits
Difference between revisions of "Excel (PowerShell)"
Jump to navigation
Jump to search
Added Limitations and Save workaround
(Initial creation - content from PowerShell Examples page) |
(Added Limitations and Save workaround) |
||
| Line 1: | Line 1: | ||
Using PowerShell you can easily export a table of data to a CSV file... | |||
<source lang="powershell"> | |||
Export-CSV FileToWriteTo.CSV -InputObject $DataTable -NoTypeInformation | |||
</source> | |||
... but you can't create an Excel file, directly. Which is a limitation if you want to create an automated (spreadsheet) report for anybody that's incapable of digesting information that's not nicely presented. To do so, requires direct automation of Excel... | |||
= Limitations = | |||
Microsoft Office applications are not built for background automation. It can be achieved, but there are some gotchas. Most notably when it comes to scheduling automation to run outside of an interactive user session. Which means you spend time crafting a wonderful that creates a beautifully formatted spreadsheet, which then fails to complete properly when run as a scheduled task when you're not logged into the machine its running from. It also means that every once in a [https://www.wikipedia.org/wiki/Blue_moon blue moon], it can still fail for no obvious reason. | |||
* See [http://support.microsoft.com/kb/257757 MS KB 257757 - Considerations for server-side Automation of Office] for further info. | |||
** ''server-side'' includes anything run outside of an interactive (logged in to the GUI) session, be it on a server, desktop, laptop or other. | |||
== Unable to Save Workaround == | |||
At the final stage of automation, when your script comes to attempt to save the spreadsheet it fails with an unimaginative <code>Exception calling “SaveAs” with “1″ argument(s): “SaveAs method of Workbook class failed”</code> error. Creating either of the following files resolves the error... | |||
* <code> C:\Windows\System32\config\systemprofile\Desktop </code> | |||
** For 32 bit only or 64 bit only systems | |||
* <code> C:\Windows\SysWOW64\config\systemprofile\Desktop </code> | |||
** For mixed systems where 32 bit Excel is running on 64 bit OS | |||
This error is probably specific to Excel 2007 and later (Excel 2010 is affected), running on Windows 7, 2008 and later | |||
* '''Source:''' [http://www.techdecode.com/wp/?p=58 TechDecode: Gotcha! – Office 2007 Automation with Powershell in Windows 2008] | |||
= Create From CSV = | = Create From CSV = | ||
Basic example, converting an existing CSV file into a XLSX file. | Whilst its not necessary to create a CSV as an intermediate step, it can be beneficial to do so... | ||
* Its easier to create/maintain your script | |||
* When the Excel automation fails you can attempt to repeat or complete manually, rather than re-running the data collection/creation part. | |||
Basic example, converting an existing CSV file into a XLSX file. | |||
<source lang="powershell"> | <source lang="powershell"> | ||
$INfile = "VMs.csv" # Has three columns, labelled VC, Name, Datastore | $INfile = "VMs.csv" # Has three columns, labelled VC, Name, Datastore | ||