Powershell script for Excel error code

Question

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

Your paths look malformed. It looks like you are building it with $n but then you add the path again with $outputDirectory. I suggest you put in the loop just to see $outputDirectory + $n + ".csv" what that output looks like. You should see an awful looking path. Are you able to just remove $inputWorkbookPath + "_" + from $n? — Matt
– Matt, Commented Jun 11, 2015 at 23:25
I'm with @Matt on this. I just tested this in PS ISE with a test file and kept things simple. The .SaveAs() method works fine. It's your generated path that's probably a bit shonky. — Kev
– Kev, Commented Jun 11, 2015 at 23:28

TheMadTechnician · Accepted Answer · 2015-06-12 02:08:56Z

2

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.

edited Jun 12, 2015 at 2:08

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

answered Jun 11, 2015 at 23:30

Matt

47k9 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheMadTechnician Over a year ago

I would highly suggest creating your output string as a variable before calling the SaveAs method. I have had bad experiences with MSOffice COM object methods trying to construct anything in the method.

Collectives™ on Stack Overflow

Powershell script for Excel error code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related