Create Microsoft Excel documents per scheduled task with PowerShell
We were recently faced with the task of automatically filling an Excel 2010 document with PowerShell on a Windows 2008 R2 Server. If you are not using a english system, make sure to set the culture info to “en-US”. Otherwise you won’t be able to open an Excel 2010 document or to save it. We wanted to use our PowerShell script as a scheduled task. The Excel document was created through the scheduled task, but it was not saved.
In order to create a Microsoft Excel 2010 document with a PowerShell script and a scheduled task on a Windows 2008 server – the following folder must exist on the server, otherwise the document is not saved.
|
1 2 |
Windows 2008 x64 "C:\Windows\SysWOW64\config\systemprofile\Desktop" |
|
1 2 |
Windows 2008 x32 "C:\Windows\System32\config\systemprofile\Desktop" |
A Powershell script could be implemented this way::
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
$fExists = <strong>Test-Path</strong> <em>-Path</em> "C:\Windows\SysWOW64\config\systemprofile\Desktop" if($fExists -eq $false){ <strong> new-item</strong> "C:\Windows\SysWOW64\config\systemprofile\Desktop" <em>-ItemType</em> Directory } $ci = [System.Globalization.CultureInfo]"en-US" $Excel = <strong>New-Object</strong> <em>-ComObject</em> "Excel.application" $Excel.visible = $false $workbook = $Excel.Workbooks $workbook = $workbook.psbase.gettype().InvokeMember("Add",[Reflection.BindingFlags]::InvokeMethod,$null,$workbook,$null,$ci) $workSheet = $workBook.Worksheets.item(1) $worksheet.Cells.item(<Line>,<Row>).formulalocal = “Text Cell” $excel.workbooks.psbase.gettype().InvokeMember("SaveAs",[Reflection.BindingFlags]::InvokeMethod,$null, $workbook, <ExcelFilePath>, $ci) > $null $excel.workbooks.psbase.gettype().InvokeMember("Close",[Reflection.BindingFlags]::InvokeMethod,$null, $workbook, 0, $ci) > $null $ExcelProcess = <strong>get-process</strong> excel $ExcelProcess | <strong>foreach</strong> {<strong>stop-process</strong> ($_.id)} [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) [gc]::collect() [gc]::WaitForPendingFinalizers() |
Artikel erstellt am: 16.12.2013
1 Comment
Leave your reply.