powershell XLS to CSV conversion issue

0

I am running below code with to convert XLS to CSV but getting strange error. Can someone point me to right direction as to what can be causing this issue? I am using powershell v3.0. not sure what I am missing here.

PARAM
    (
        [parameter(Mandatory = $true)]
        [string]
        $excelFilePath = $(throw "Must give a valid Excel (*.xls) file path.")
        ,

        [parameter(Mandatory = $true)]
        [int]
        $leadingRowsToDelete = $(throw "Must specify how many leading rows to delete.")
        ,

        [parameter(Mandatory = $false)]
        [string]
        $csvFilePath = $null
        ,

        [parameter(Mandatory = $false)]
        [int]
        $xlCSV = 6
    )


    if(! $csvFilePath)
    {
        $csvFilePath = $excelFilePath -replace ".xls$", ".csv"
    }

    $Excel = New-Object -Com Excel.Application -Property @{Visible = $false} 
    $Excel.displayalerts=$False

    $WorkBook = $Excel.Workbooks.Open($excelFilePath) # Open the file
    $Sheet = $WorkBook.Sheets.Item(1) # Activate the first worksheet

    # Delete the first $leadingRowsToDelete rows
    for($i = 1; $i -le $leadingRowsToDelete; $i ++)
    {
        [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
    }

    $WorkBook.SaveAs($csvFilePath, $xlCSV)

    $Excel.quit()

Error msg I get is:

Exception calling "Open" with "1" argument(s): "The server threw an exception. (Exception from HRESULT: 0x80010105
(RPC_E_SERVERFAULT))"
At \\multinasdub301\Software\SysAdmin\WebDownloads\XLS-To-CSV.ps1:74 char:1
+ $WorkBook = $Excel.Workbooks.Open($excelFilePath) # Open the file
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

You cannot call a method on a null-valued expression.
At \\multinasdub301\Software\SysAdmin\WebDownloads\XLS-To-CSV.ps1:75 char:1
+ $Sheet = $WorkBook.Sheets.Item(1) # Activate the first worksheet
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \\multinasdub301\Software\SysAdmin\WebDownloads\XLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \\multinasdub301\Software\SysAdmin\WebDownloads\XLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \\multinasdub301\Software\SysAdmin\WebDownloads\XLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \\multinasdub301\Software\SysAdmin\WebDownloads\XLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \\multinasdub301\Software\SysAdmin\WebDownloads\XLS-To-CSV.ps1:80 char:2
+     [void]$Sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At \\multinasdub301\Software\SysAdmin\WebDownloads\XLS-To-CSV.ps1:83 char:1
+ $WorkBook.SaveAs($csvFilePath, $xlCSV)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
powershell-3.0
asked on Stack Overflow Nov 19, 2015 by (unknown user)

1 Answer

0

According to OP's comments, issue was caused by faulty permissions on the target Excel file.

Script is working fine on a local file with full permissions.

answered on Stack Overflow Nov 23, 2015 by sodawillow

User contributions licensed under CC BY-SA 3.0