Breaking News: Grepper is joining You.com. Read the official announcement!

split excel file with powershell based on column value

Innocent Ibex answered on August 7, 2023 Popularity 1/10 Helpfulness 1/10

answer split excel file with powershell based on column value

split excel file with powershell based on column value

Comment

Function Create-Excel-Spreadsheet {
    Param($NameOfSpreadsheet)

    # open excel
    $excel = New-Object -ComObject excel.application
    $excel.visible = $true

    # add a worksheet
    $workbook = $excel.Workbooks.Add()
    $xl_wksht= $workbook.Worksheets.Item(1)
    $xl_wksht.Name = $NameOfSpreadsheet

    return $workbook
}


$objexcel = New-Object -ComObject Excel.Application
$wb = $objexcel.WorkBooks.Open("C:\Temp\Test.xlsx") # Changing path for test.xlsx file.

$objexcel.Visible = $true
$objexcel.DisplayAlerts = $False
$ws = $wb.Worksheets.Item(1)

$usedRange = $ws.UsedRange
$usedRange.AutoFilter()

$totalRows = $usedRange.Rows.Count


$rangeForUnique = $usedRange.Offset(1, 0).Resize($UsedRange.Rows.Count-1) 
[string[]]$UniqueListOfRowValues = $rangeForUnique.Columns.Item(1).Value2 | sort -Unique

for ($i = 0; $i -lt $UniqueListOfRowValues.Count; $i++) {
    $newRange = $usedRange.AutoFilter(1, $UniqueListOfRowValues[$i])

    $workbook = Create-Excel-Spreadsheet $UniqueListOfRowValues[$i]
    $wksheet = $workbook.Worksheets.Item(1)

    $range = $ws.UsedRange.Cells
    $range.Copy()

    $wksheet.Paste($wksheet.Range("A1"))
    $workbook.SaveAs("C:\temp\" + $UniqueListOfRowValues[$i], $xlFixedFormat)
    $workbook.Close()
}

Popularity 1/10 Helpfulness 1/10 Language shell

Source: stackoverflow.com

Tags: excel file powershell shell

Link to this answer
Share Copy Link

Contributed on Aug 07 2023

Innocent Ibex

0 Answers Avg Quality 2/10

split excel file with powershell based on column value

Contents

More Related Answers

split excel file with powershell based on column value

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.