User Tools

Site Tools


informatique:windows:powershell:excel-separer-feuilles

Séparer des feuilles Excel dans des fichiers

Le code n'est pas de moi mais d'un utilisateur anonyme (user4039065) sur Stackoverflow. Je le colle ici à titre d'aide mémoire :

Il faut modifier les variables $filepath et $WorkbookName par les noms du fichier à traiter.

$Excel = New-Object -ComObject “Excel.Application”$Excel.Visible = $false #Runs Excel in the background. 
$Excel.DisplayAlerts = $false #Supress alert messages. 
 
$filepath =“D:\powershell\test.xlsx”$Workbook = $Excel.Workbooks.open($filepath)
$WorkbookName = “test.xlsx”$output_type = “xlsx” 
if ($Workbook.Worksheets.Count -gt 0) { 
    write-Output “Now processing: $WorkbookName”    $FileFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook 
 
    $WorkbookName = $filepath -replace “.xlsx”, “” 
    foreach($Worksheet in $Workbook.Worksheets) {
        $Worksheet.Copy()
        $ExtractedFileName = $WorkbookName + “~~” + $Worksheet.Name + “.” + $output_type 
        $Excel.ActiveWorkbook.SaveAs($ExtractedFileName, $FileFormat) 
        $Excel.ActiveWorkbook.Close
 
        write-Output “Created file: $ExtractedFileName”    }
} 
 
$Workbook.Close() 
$Excel.Quit() 
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Stop-Process -Name EXCEL
Remove-Variable Excel
informatique/windows/powershell/excel-separer-feuilles.txt · Last modified: 2020/02/28 10:42 by 127.0.0.1

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki