I need an excel PowerShell script that combines data from multiple sheets of multiple excel documents. So essentially i have a folder with 100+ excel files, each with 10 sheets of data. I need to gather data from two sheets of each excel file and put it into one excel file in a straight list.
All i've been able to do is grab the specific piece of information from one sheet, then copy over the entire other sheet and slap on that one specific piece of information that i grabbed. I need some help from here; I'm stuck.
Here is the script:
#Open excel, show the program, go to filepath with source files
$ExcelObject=New-Object -ComObject excel.application
$ExcelObject.visible=$true
$ExcelFiles=Get-ChildItem -Path $("C:\Users\kpaul\Documents\Webroot Reporting\Runs\20200225T1629219445554Z\Output")
#add sheet, set general row values for grabbing the appropriate information later
$Workbook=$ExcelObject.Workbooks.add()
$Worksheet=$Workbook.Sheets.Item("Sheet1")
$setrow = 6
$setcol = 1
$setrow2 = 2
$setcol2 = 1
<#
Do this for each file
Grab information from a different sheet, save it, go to the next sheet and copy that entire sheet to the merged file
then replace some information from the merged file with the saved information
#>
foreach($ExcelFile in $ExcelFiles){
$excel=$ExcelObject.Workbooks.Open($ExcelFile.FullName)
#go to "site" sheet in source file and save the site name
$sheet=$excel.sheets.item('Site')
$SetSiteName = $sheet.Cells.Item($setrow,$setcol)
#Go to "endpoints" sheet in the source file and copy over information, insert the saved name
$sheet=$excel.sheets.item('Endpoints')
$sheet.Cells.Item($setrow2,$setcol2) = $SetSiteName
$sheet.Copy($Worksheet)
$excel.Close($false)
}
#save merged file
$Workbook.SaveAs("C:\Users\kpaul\Desktop\MergedWebrootReport\AllSiteEnpoints.xlsx")
$ExcelObject.Quit()