CODEWITHSUNDEEP
export-to-excelexcel-2013powershell-4.0
Captor
Captor

Reputation: 75

Export to 2nd+ spreadsheet

I'm using Export-Csv to export [pscustomobject]s. Then I'm using a second function to convert that into a xlsx. Which works perfect. But what if I wanted to export into the second spreadsheet and rename it to something different? I know Export-Csv doesn't support multi spread sheets.

Function SaveAsXLXS
{
    #Hide Old File
    (Get-Item $ResultsFilePath -Force).Attributes = "Hidden"

    #Opens Old File
    $Excel = New-Object -ComObject Excel.Application
    $Workbook = $Excel.Workbooks.Open($ResultsFilePath)

    #Formating
    if ($GroupsTab.IsSelected -or $OrgBoxesTab.IsSelected)
    {
        $Workbook.Worksheets.Item(1).Columns.Item(1).Font.Bold = $True
        $Workbook.Worksheets.Item(1).Columns.Item(1).Font.Size = 12
    }
    $Workbook.Worksheets.Item(1).Rows.Item(1).Font.Bold = $True
    $Workbook.Worksheets.Item(1).Rows.Item(1).Font.Size = 15
    $Workbook.Worksheets.Item(1).UsedRange.EntireColumn.Autofit()

    #Creates Name for New File
    $ExcelOut = $ResultsFilePath -replace '\.csv$', '.xlsx'
    $dir = Split-Path $ExcelOut
    $FilePathBase = $(Split-Path $ExcelOut -Leaf) -replace '\.xlsx$'
    $FilePath = $ExcelOut

    $n = 1
    while (Test-Path $FilePath) {
        $FilePath = Join-Path $dir $($FilePathBase + "-$n" + '.xlsx')
        $n++
    }

    #Saves New File
    $Workbook.SaveAs($FilePath, 51)

    #Exits Old File
    $Excel.Quit()

    #Removes Old File
    Remove-Item $ResultsFilePath -Force
}

Upvotes: 1

Views: 25

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200243

You're opening the CSV as a new workbook, so you just need to open the workbook to which you want to add it as well and move/copy the sheet.

...
$Workbook = $Excel.workbooks.open($ResultsFilePath)
...

$wb2 = $Excel.Workbooks.Open('C:\path\to\other.xlsx')
$Workbook.Sheets.Item(1).Name = 'whatever'          # rename sheet
$Workbook.Sheets.Item(1).Copy($wb2.Sheets.Item(1))  # copy sheet
$Workbook.Close($false)                             # close CSV without saving
$wb2.Save()                                         # save & close workbook
$wb2.Close()

Of course, if you want to insert multiple CSVs into a workbook you'd open the xlsx file just once and save/close it after all sheets were inserted.

If you want to insert sheets from a CSV after a particular sheet in the destination workbook change the Copy() call to something like this:

$Workbook.Sheets.Item(1).Copy([Type]::Missing, $wb2.Sheets.Item(3))

Upvotes: 1

Related Questions