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.

   Beantwoorden

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?

   Beantwoorden

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?

   Beantwoorden

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:

   [CmdletBinding()]

   #input parameters
   param(
   [Parameter(Mandatory=$True,Position=1)]
   [String]$excelFile,
   [Parameter(Mandatory=$True,Position=2)]
   [String]$csvFile,
   [Parameter(Mandatory=$True,Position=3)]
   [int]$workSheetNum
   )

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

   function Invoke([object]$m, [string]$method, $parameters)
   {
   $m.PSBase.GetType().InvokeMember(
   $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
   $oWorksheet.Activate();

   $oExcelDoc.SaveAs($csvFile,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows);

   $oExcelDoc.Close($false);

   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!

   Clint

   Beantwoorden