CODEWITHSUNDEEP
excelcsvpowershell
Nate
Nate

Reputation: 862

PowerShell Error Creating XLSX

Okay, I have 6 CSVs each containing one column. For this example, the data from the first CSV is used to create the initial document, and each after that is attempting to save to the document.

#ID

  $csvFile = "$path\ID.csv"
  $fpath = $Filename 
$processes = Import-Csv -Path $csvFile 
$Excel = New-Object -ComObject excel.application 
$Excel.visible = $false 
$workbook = $Excel.workbooks.add() 
$excel.cells.item(1,1) = "ID" 
$i = 2 
foreach($process in $processes) 
{ 
 $excel.cells.item($i,1) = $process.ID 
 $i++ 
} #end foreach process 
$workbook.saveas($fpath) 
$Excel.Quit() 
Remove-Variable -Name excel 
[gc]::collect() 
[gc]::WaitForPendingFinalizers() 

#SRP

  $csvFile = "$path\SRP.csv" 

$processes = Import-Csv -Path $csvFile 
$Excel = New-Object -ComObject excel.application 
$Excel.visible = $false 
$workbook = $Excel.workbooks.add() 
$excel.cells.item(1,2) = "SRP" 
$i = 2 
foreach($process in $processes) 
{ 
 $excel.cells.item($i,2) = $process.SRP 
 $i++ 
} #end foreach process 
$workbook.save($fpath) 
$Excel.Quit() 
Remove-Variable -Name excel 
[gc]::collect() 
[gc]::WaitForPendingFinalizers()

When I get down to the save line on the second one (#SRP section) I get the following error:

Cannot find an overload for "Save" and the argument count: "1".
At D:\UserAdminScripts\0_Powershell_Test_Scripts\Scripts_For_Lisa\NED Stuff\NED_Reports.ps1:130 char:15
+ $workbook.save <<<< ($fpath) 
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

If I let the script run after the error, I get the same result for each column.

I'm aware this script isn't clean, because I don't need to close and reopen Excel for each run, but I butchered this script from http://blogs.technet.com/b/heyscriptingguy/archive/2010/09/09/copy-csv-columns-to-an-excel-spreadsheet-by-using-powershell.aspx. The saveas works correctly in the first attempt, it's just the save causing an error.

I find when I break the script, I get a pop-up to confirm whether or not I want to make changes to Book2.xlsx. I tell it yes and I check and Book2 has the 2nd column filled out with the data I wanted as the second column on my original sheet.

Any help is appreciated.

Upvotes: 0

Views: 1242

Answers (2)

Nate
Nate

Reputation: 862

Well, it looks like I answered my own question, due to ConanW's inspiration to actually, you know, think critically. The answer to my issue is as follows:

#ID

  $csvFile = "$path\ID.csv"
  $fpath = $Filename 
$processes = Import-Csv -Path $csvFile 
$Excel = New-Object -ComObject excel.application 
$Excel.visible = $false 
$workbook = $Excel.workbooks.add() 
$excel.cells.item(1,1) = "ID" 
$i = 2 
foreach($process in $processes) 
{ 
 $excel.cells.item($i,1) = $process.ID 
 $i++ 
} #end foreach process 


#SRP

  $csvFile = "$path\SRP.csv" 

$processes = Import-Csv -Path $csvFile 
$excel.cells.item(1,2) = "SRP" 
$i = 2 
foreach($process in $processes) 
{ 
 $excel.cells.item($i,2) = $process.SRP 
 $i++ 
} #end foreach process 
$workbook.saveas($fpath) 
$Excel.Quit() 
Remove-Variable -Name excel 
[gc]::collect() 
[gc]::WaitForPendingFinalizers()

Upvotes: 0

ConanW
ConanW

Reputation: 486

Have a look at the $Workbook object with Get-Member:

Save                             Method                void Save ()

The Save() method doesn't accept any arguments and it's failing to work out what to do with that method and your file name argument.

You Quit the Excel app in the #ID section (thus closing the file) so you need to reopen the file before accessing the workbook and trying to write to it.

Upvotes: 1

Related Questions