☰

将多个 excel 文件合并为一个文件

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba macros
将多个 excel 文件合并为一个文件

这是我的宏，用于将一个目录中的所有文件合并到一个 excel 表中。我无法找到它为什么不能按预期工作。每个文件都有一千行。它从第一个文件复制 1000 行，但在下一个文件中，它从第 1001 行而不是第 1 行复制。同样，第三个文件从第 2001 行复制。我想每隔一段时间复制前 1000 行。哪部分代码导致了问题

Sub MergeFiles()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Dim i
i = 0

'change folder path of excel files here
Dim TTFiles_Path As String
ThisWorkbook.Worksheets(7).Activate
TTFiles_Path = Range("B2").Value

Set dirObj = mergeObj.GetFolder(TTFiles_Path)
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)


'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A4:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(8).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next

End Sub

答案1

分析故障的一种方法：使用宏进行调试。

请看我的回答这里了解最基础的知识。

相关内容