CODEWITHSUNDEEP
excelpowershellcsvexport-to-csvpowershell-4.0
slumpmonkey
slumpmonkey

Reputation: 79

Powershell script for Excel error code

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

Upvotes: 1

Views: 2648

Answers (1)

Matt
Matt

Reputation: 46730

I strongly feel your issue is coming from your path concatenation logic. Lets look at the following code from within your loop.

$n = $inputWorkbookPath + "_" + $workSheet.Name
$workSheet.SaveAs($outputDirectory + $n + ".csv", 6)

In your example call your variables I think are mapped as follows:

$inputWorkbookPath equals "R:\Unclaimed Property\NC State\Jun 2015\NC_RAW_JUL1986thruMAR2013.xls"
$workSheet.Name equals "Bagel" # I made that up.
$outputDirectory equals "R:\Unclaimed Property\NC State\Jun 2015\"

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

$outputDirectory + $n + ".csv"

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

$path = $outputDirectory + $workSheet.Name + ".csv"
$workSheet.SaveAs($path, 6)

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.

Upvotes: 2

Related Questions