How to use powershell to copy several excel worksheets and make a new one?

Question

I have about 70 excel files I want to combine into a single document. Each document has only one sheet and follows this format:

Row A with Columns A-F of headings
Row B with the first entry
Row C with the second entry
Up to 150 rows on some sheets

I want to scrape the information from Columns A-F for each row, and combine it into a new file with the information from all of the other files I have in the same directory.

Note: I only want to capture Columns A-F since in Column G there exists a Yes, No dataset to manage the drop down list in Column F.

I tried using dugan's answer from Copy Excel Worksheet from one Workbook to another with Powershell but it resulted in a file with part of the data spread across two sheets.

Here is that 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

Any suggestions? Thank you.

So to be clear, for each file you want to append all data in columns A-F into a new workbook. So if you had 3 files, and each had 50 records, you would want a fourth file with 150 records? — TheMadTechnician, May 16 '14 at 19:45
Yes. Append all data in columns A-F, from every file in the directory, into a new workbook, which will have all data in one place. — Matt A., May 16 '14 at 19:52

score 8 · Accepted Answer · edited Feb 06 '20 at 16:39

Ok, it's a few days later, but you can thank the weekend for that. Take a look at this and see how you like it.

#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()

That'll get a list of excel files, open Excel and create a new document, then cycle through the list of files, opening them, selecting Columns A-F, copying those columns, going back to the new workbook and selecting the next available row, and pasting the data from the other workbook. Then it closes that file and moves on to the next one. At the end it saves your workbook with all the data and closes excel.

Thank you. The script does combine all files together. There is one small error in the Else clause ("B1"... that was making my file wonky. But with an "A2" there, it works! You are in fact, @TheMadTechnician — Matt A., May 23 '14 at 14:53

How to use powershell to copy several excel worksheets and make a new one?

1 Answers1

Linked