如何使用PowerShell复制多个Excel工作表并创建一个新工作表？

Question

如何使用PowerShell复制多个Excel工作表并创建一个新工作表？

excelvbapowershellautomationexcel-2010

4

我有大约70个Excel文件，想要将它们合并成一个文档。每个文档只有一个工作表，并遵循以下格式：

第一行是A-F列的标题
第二行是第一个条目
第三行是第二个条目
某些工作表上最多有150行

我想要从每一行中提取A-F列的信息，并将其与同一目录中所有其他文件的信息组合成一个新文件。

注意：我只想捕获A-F列，因为在G列中存在一个Yes、No数据集来管理F列的下拉列表。

我尝试使用dugan在Copy Excel Worksheet from one Workbook to another with Powershell中的答案，但结果是部分数据分布在两个工作表中。

以下是那段代码：

$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

有什么建议吗？谢谢。

- Matt A.

所以明确一点，对于每个文件，您想将A-F列中的所有数据附加到一个新工作簿中。因此，如果您有3个文件，每个文件有50条记录，那么您将需要第四个文件，其中包含150条记录？ - TheMadTechnician

是的。将目录中每个文件的A-F列中的所有数据附加到一个新工作簿中，使所有数据都在同一位置。 - Matt A.

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- TheMadTechnician · Accepted Answer

好的，虽然有几天时间了，但这都是周末的原因。看看这个，看看你喜不喜欢。

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

这将获取一个Excel文件列表，打开Excel并创建一个新文档，然后循环遍历文件列表，打开它们，选择列A-F，复制这些列，返回到新工作簿并选择下一个可用行，并将来自其他工作簿的数据粘贴到该行。然后关闭该文件并继续下一个。最后保存包含所有数据的工作簿并关闭Excel。