Comment utiliser powershell pour copier plusieurs feuilles de calcul excel et en faire une nouvelle?

J'ai environ 70 fichiers excel que je veux combiner en un seul document. Chaque document a une seule feuille et suit ce format:

  • Ligne Un avec des Colonnes A à F de titres
  • Ligne B avec la première entrée
  • Ligne C avec la deuxième entrée
  • Jusqu'à 150 lignes sur quelques feuilles

Je veux gratter les informations de la colonne A-F pour chaque ligne, et les combiner dans un nouveau fichier avec les informations provenant de tous les autres fichiers que j'ai dans le même répertoire.

Remarque: je ne veux capturer les Colonnes A-F depuis dans la Colonne G, il existe un Oui, Pas de jeu de données pour gérer la liste déroulante dans la Colonne F.

J'ai essayé d'utiliser dugan la réponse de Copie de Feuille de calcul Excel d'un Classeur à un autre avec Powershell mais il a abouti dans un fichier avec une partie des données réparties sur deux feuilles.

Ici, c'est que code:

$file1 = 'C:\Users\Matthew.Andress\Documents\Excel Test\Book1.xlsx' # source's fullpath
$file2 = 'C:\Users\Matthew.Andress\Documents\Excel Test\Book2.xlsx' # destination's fullpath
$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb1 = $xl.workbooks.open($file2) # open target
$sh1_wb1 = $wb1.sheets.item(2) # second sheet in destination workbook
$sheetToCopy = $wb2.sheets.item('Sheet1') # source sheet to copy
$sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook
$wb2.close($false) # close source workbook w/o saving
$wb1.close($true) # close and save destination workbook
$xl.quit()
spps -n excel

Des suggestions? Merci.

  • Donc, pour être clair, pour chaque fichier que vous souhaitez ajouter toutes les données dans les colonnes A à F dans un nouveau classeur. Donc, si vous aviez 3 fichiers, et chacune 50 enregistrements, vous pouvez choisir un quatrième fichier avec 150 dossiers?
  • Oui. Ajouter toutes les données dans les colonnes A à F, à partir de tous les fichiers dans le répertoire, dans un nouveau classeur, ce qui permettra de disposer de toutes les données en un seul endroit.
InformationsquelleAutor Matt A. | 2014-05-16
  1. 7

    Ok, c'est quelques jours plus tard, mais vous pouvez remercier le week-end pour que. Jetez un oeil à ça et de voir comment vous l'aimez.

    #Get a list of files to copy from
$Files = GCI 'C:\Users\Matt\Documents\Excel Test\' | ?{$_.Extension -Match "xlsx?"} | select -ExpandProperty FullName

#Launch Excel, and make it do as its told (supress confirmations)
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$Excel.DisplayAlerts = $False

#Open up a new workbook
$Dest = $Excel.Workbooks.Add()

#Loop through files, opening each, selecting the Used range, and only grabbing the first 6 columns of it. Then find next available row on the destination worksheet and paste the data
ForEach($File in $Files[0..4]){
    $Source = $Excel.Workbooks.Open($File,$true,$true)
    If(($Dest.ActiveSheet.UsedRange.Count -eq 1) -and ([String]::IsNullOrEmpty($Dest.ActiveSheet.Range("A1").Value2))){ #If there is only 1 used cell and it is blank select A1
        [void]$source.ActiveSheet.Range("A1","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
        [void]$Dest.Activate()
        [void]$Dest.ActiveSheet.Range("A1").Select()
    }Else{ #If there is data go to the next empty row and select Column A
        [void]$source.ActiveSheet.Range("A2","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
        [void]$Dest.Activate()
        [void]$Dest.ActiveSheet.Range("A$(($Dest.ActiveSheet.UsedRange.Rows|Select -last 1).row+1)").Select()
    }
    [void]$Dest.ActiveSheet.Paste()
    $Source.Close()
}
$Dest.SaveAs("C:\Users\Matt\Documents\Excel Test\Book1.xlsx",51)
$Dest.close()
$Excel.Quit()

    Que obtiendrez une liste de fichiers excel, ouvrez Excel et créer un nouveau document, puis parcourir la liste des fichiers, de les ouvrir, de sélectionner des Colonnes A à F, la copie de ces colonnes, en remontant vers le nouveau classeur et de la sélection de la prochaine ligne disponible, et coller les données dans le classeur. Puis il ferme le fichier et passe à la suivante. À la fin, il permet d'économiser votre classeur avec toutes les données et ferme excel.

    • Je vous remercie. Le script ne se combiner tous les fichiers ensemble. Il y a une petite erreur dans la clause Else ("B1"... qui a été prise de mon fichier bancale. Mais avec un "A2" là, ça marche!!! Vous êtes, en fait, @TheMadTechnician
    InformationsquelleAutor TheMadTechnician