手动选择文件，并将选择文件中的数据合并到一张工作表

手动选择文件，并将选择文件中的数据合并到一张工作表

本文为《别怕，Excel VBA其实很简单（第3版）》随书问题参考答案

改写后的过程如下：

Sub 合并多工作簿所有工作表的数据() Application.ScreenUpdating = False Dim DataArr As Variant, DataWb As Workbook, DataSht As Worksheet Dim EndRow As Long, ToSht As Worksheet, ToRng As Range Dim FileName As Variant '要合并的工作簿名称 Dim a As Long, b As Long Set ToSht = ThisWorkbook.Worksheets(1) ToSht.Rows("2:1048576").Clear '清除原有数据 FileName = Application.GetOpenFilename(filefilter:="Excel工作簿文件,*.xls?", Title:="请选择文件", MultiSelect:=True) If TypeName(FileName) = "Boolean" Then Exit Sub Dim Fil As Variant For Each Fil In FileName Workbooks.Open FileName:=Fil Set DataWb = ActiveWorkbook For Each DataSht In DataWb.Worksheets EndRow = DataSht.Range("A1048576").End(xlUp).Row DataArr = DataSht.Range("A2").Resize(EndRow - 1, 8).Value Set ToRng = ToSht.Range("A1048576").End(xlUp).Offset(1, 0) For a = 1 To UBound(DataArr, 1) '将数组中超过15位的数字转为文本 For b = 1 To UBound(DataArr, 2) If Len(DataArr(a, b)) > 15 Then DataArr(a, b) = "'" & DataArr(a, b) End If Next b Next a ToRng.Resize(UBound(DataArr, 1), 8).Value = DataArr Next DataSht DataWb.Close savechanges:=False Next Fil Application.ScreenUpdating = True MsgBox "合并完成！"End Sub

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。