I'm trying to convert an Excel .xls file that has several worksheets into a .csv with Powershell 4.0. I know the SaveAs in the for each loop isn't phrased right, and that the error is pointing to line 17 and character 9, I just don't know how to fix it or how to interpret the error code 0x800A03EC.
Here's the script:
Function ExportWSToCSV ($inputWorkbookPath, $outputFilePrefix, $outputDirectory)
{
#Start Excel invisibly without pop-up alerts.
$inputWorkbookPath = "R:\Unclaimed Property\NC State\Jun 2015\" + `
"NC_RAW_JUL1986thruMAR2013" + ".xls"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
#Open Excel file.
$workBook = $excel.Workbooks.Open($inputWorkbookPath)
foreach ($workSheet in $workBook.Worksheets)
{
$n = $inputWorkbookPath + "_" + $workSheet.Name
$workSheet.SaveAs($outputDirectory + $n + ".csv", 6)
}
$excel.Quit()
}
ExportWSToCSV -inputWorkbookPath "R:\Unclaimed Property\NC State\Jun 2015\NC_RAW_JUL1986thruMAR2013.xls" `
-outputFilePrefix "output_" `
-outputDirectory "R:\Unclaimed Property\NC State\Jun 2015\"
Here's the error:
Exception calling "SaveAs" with "2" argument(s): "Exception from HRESULT: 0x800A03EC"
At \\ncdfs1\documents$\ANDREWN\My Documents\PSscript_for_NC.ps1:17 char:9
+ $workSheet.SaveAs($outputDirectory + $n + ".csv", 6)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation
I strongly feel your issue is coming from your path concatenation logic. Lets look at the following code from within your loop.
In your example call your variables I think are mapped as follows:
So you are going to try and set the new file name as:
R:\Unclaimed Property\NC State\Jun 2015\R:\Unclaimed Property\NC State\Jun 2015\NC_RAW_JUL1986thruMAR2013.xls_Bagel.csv
Which does not look right at all. If you just had the line in your loop
I think you would see the issue. Just some simple debugging.
Lets fix this
First guess is that you just need to change it to something like this
Outside the scope of this question it would be a good idea to check if that path exists before saving it. It would save some potential headache.