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
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.
User contributions licensed under CC BY-SA 3.0