Excel automation with Powershell

Powershell is Microsoft’s answer of decent shell scripting on the Windows platform. Equipped with the functions you would expect from VBScript, it behaves as an Unix shell script would. All commands can work from the command line, or as a set of instructions in a file.

Adding to the basic shell functions one would expect, such as file and directory manipulation, reading and writing text files, manipulating strings with regular expressions, Powershell also integrates with .NET 2.0 and higher to communicate with other functions, such as accessing the System.Windows.Forms object to make neat forms equipped with all the controls you would expect in a compiled application, or in this case: manipulating Excel workbooks.

Using Excel together with PowerShell can be very easy. Too bad the documentation is sparse on this topic, and the official documentation makes no mention of a workaround that is needed when using Excel with PowerShell on a machine which is not configured with regional settings set to EN-us.

We start off creating an Excel object, as described in the Scripting Guy post:

$objExcel = New-Object -comobject Excel.Application
$objExcel.Visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
$objWorksheet = $objWorkbook.Worksheets.Item(1)
$objWorksheet.Cells.Item(1,1) = "A value in cell A1."

This little bit wil start Excel, add a worksheet, inset a string in cell A1, and save the Excel worksheet to a file, before quitting. Unless your machine is not set up with regional settings EN-us. Then you are greeted with the next message:

Exception calling "Add" with "0" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018
At line:1 char:39
+ $objWorkbook = $objExcel.Workbooks.Add( < <<< )

When looking for this Error, you will run into threads regarding other scripting and programming languages. Most notably, KB320369 has some information.

Ok, there is a workaround. See what this code does:

function Invoke([object]$m, [string]$method, $parameters)
$method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters,$ciUS)

$ciUS = [System.Globalization.CultureInfo]'en-US'

$objExcel = New-object -com Excel.Application
$objExcel.visible = $True
$ci = [System.Globalization.CultureInfo]'en-us'
$objWorkbook = Invoke $objExcel.Workbooks Add
$objWorksheet = $objWorkbook.Worksheets.Item(1)
$objWorksheet.Cells.Item(1,1) = "A value in cell A1."
Invoke $objWorkbook SaveAs "C:ScriptsTest.xls" > $null
Invoke $objWorkbook Close 0 > $null

Still an error!

Exception setting "Item": "Exception from HRESULT: 0x800A03EC"
At C:scriptingtest.ps1:15 char:25
+ $objWorksheet.Cells.Item( < <<< 1,1) = "A value in cell A1."

Modify the above code a bit, with this line instead of line 14:

$objWorksheet.Cells.Item(1,1).FormulaLocal = "A value in cell A1." 

The result? An Excel-file with data!

If you want to open an Excel-file instead of creating a new one, use the following as a replacement for line 12:

$objWorkbook = Invoke $objExcel.Workbooks Open"c:ScriptsTest.xls" 

And if you want to save the Excel file to the existing name, add this before line 16:

Invoke $objWorkbook Save > $null 



4 thoughts on “Excel automation with Powershell

  1. Thanks for posting this. It really helped me. Do you have any idea how to access other sheet properties that also throw the same error when you try and set them? I want to set the sheet name but it’s not working for the same reason as setting the cell value wasn’t.

  2. This is really helpful. However, from my understanding is it’s not possible to use PowerShell to format cells with colours and to autofit etc when regional settings aren’t set to EN-US. Do you know a way round this?

  3. I can’t find the last row. on office 2003 I used $introw = $sheet.UsedRange.Rows.Count + 1, but with offce 2007 it won’t work.

    do you know how to do it?

  4. I know this thread is old, but I am having this same problem in 2019! I use it to open excel files and then I manipulate the columns and then convert into loadable .csv files. This didn’t work for me and my language is already US. My scripts worked fine for months until a MS Office update the other day. Any ideas on what to do to fix this? My code is pretty simple:


    #input parameters

    #[System.Threading.Thread]::CurrentThread.CurrentCulture = New-Object “System.Globalization.CultureInfo” “en-US”;

    function Invoke([object]$m, [string]$method, $parameters)
    $method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters,$ciUS)

    $ciUS = [System.Globalization.CultureInfo]”en-US”;

    # Get COM Object
    $oExcel = New-Object -ComObject “Excel.Application”;

    # Open Excel in the background
    $oExcel.Visible = $false;

    $ci = [System.Globalization.CultureInfo]’en-us’;

    #open excel file
    #$oExcelDoc = Invoke $oExcel.Workbooks Open($excelFile);
    $oExcelDoc = Invoke $oExcel.Workbooks Open”C:\dataLoads\volt\badgeUploads\voltUserAdvanceList.xlsx”

    # Open 1st Worksheet
    $oWorksheet = $oExcelDoc.Worksheets.item($workSheetNum);

    # Activate, show it



    The error:

    Exception calling “InvokeMember” with “6” argument(s): “Object does not match target type.”
    At C:\scripts\powershell\qa\convertToCsv.ps1:36 char:1
    + $m.PSBase.GetType().InvokeMember(
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : TargetException

    Any help you could provide would be appreciated!


Leave a Reply