merge workbooks into one sheet

merge_workbooks.ps1

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $True
$excel.DisplayAlerts = $False
$folders = Get-ChildItem -Directory | select -ExpandProperty FullName
#Open up a new workbook
$dest = $excel.Workbooks.Add()

foreach ($folder in $folders)
{
	$files = Get-ChildItem $folder | ?{$_.Extension -Match "xlsx?"}
	foreach($file in $files){
    $source = $Excel.Workbooks.Open($file.FullName,$true,$true)
		[void]$source.Activate()
		$lastRow = ($source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row
		[void]$source.ActiveSheet.Range("A:A").EntireColumn.Insert()
		$source.ActiveSheet.Range("A1").Formula = "FileName"
		$source.ActiveSheet.Range("A2").Formula = $file.Basename
		[void]$source.ActiveSheet.Range("A2:A$lastRow").FillDown()
		
		If(($dest.ActiveSheet.UsedRange.Count -eq 1) -and ([String]::IsNullOrEmpty($dest.ActiveSheet.Range("A1").Value2))){
			#The Dest sheet is empty, copy the header bar
			[void]$source.ActiveSheet.Range("A1","A$(($source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").EntireRow.Copy()
			[void]$dest.Activate()
			[void]$dest.ActiveSheet.Range("A1").Select()
		}Else{
			#The Dest sheet is not empty, do not copy the header bar
			[void]$source.ActiveSheet.Range("A2","A$(($source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").EntireRow.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()
		Write-host "Processed $file.FullName"
	}
}
$Dest.SaveAs("$(Convert-Path .)\Merged.xlsx", [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
$excel.Quit()
Remove-Variable -Name excel
[gc]::collect() 
[gc]::WaitForPendingFinalizers()