将多个工作簿合并到一个主工作簿中 [英] Merging multiple workbooks into a single master workbook

问题描述

我正在使用MS Office 2013 VBA代码将多个工作簿第一张工作表合并到主工作簿表中。一切都工作正常，除非从源表单复制数据时它只复制一部分（例如表A有190列，表B有250列和表C等......但它只从Sheet导入16列表B中的A和10但不是全部），这是我为它编写的代码：





任何帮助都将非常感谢:)



我的尝试：



< pre lang =vb> Sub simpleXlsMerger（）
Dim bookList As 工作簿
Dim mergeObj As 对象，dirObj 作为 对象，filesObj As Object ，everyObj As 对象
Application.ScreenUpdating = 错误
设置 mergeObj = CreateObject（ Scripting.FileSystemObject）

< span class =code-comment>' 更改Excel文件的文件夹路径
设置 dirObj = mergeObj.Getfolder（ C：\ Users\hnoorzai \Desktop\Newfolder ）
设置 filesObj = dirObj.Files
对于 每个 everyObj 在 filesObj
设置 bookList = Workbooks.Open（everyObj）

' 将B3：H更改为您工作的范围还有B i n B65536到任何所需的列。
bookList.Worksheets（ 1 ）。范围（ B3：H&范围（ B65536）。结束 （xlUp）.Row）。复制
ThisWorkbook.Worksheets（ 1 ）。激活

' 以下仅将B列名更改为您所需的列名
范围（ B65536）。结束（xlUp）.Offset（ 1 ， 0 ）。PasteSpecial
Application.CutCopyMode = False
bookList.Close
下一步
结束 Sub

解决方案

参见Range.Cells Property（Excel） [ ^ ]了解如何仅使用数字创建Range对象。

您可以轻松获取已使用的行数和列数

 myrows = bookList.Worksheets（ 1 ）。UsedRange.RowS.Count 
 mycols = bookList.Worksheets（ 1 ）。UsedRange.ColS.Count 

Excel可以直接在书籍之间复制WorkSheets。

表格（ 1 ）。复制之后：= MyBook.Sheets（MyBook.Sheets.Count）

注意Excel 2013处理的行数远远超过65536行。

I am working to merge multiple workbooks first sheet into a master workbook sheet using MS Office 2013 VBA code. Everything is working fine except when copying data from source sheets it only copies a portion of it (for example sheet A has 190 columns and sheet B has 250 columns and sheet C so on... but it is only importing like 16 columns from Sheet A and 10 from Sheet B but not all of them), here is the code I have written for it:


Any help will be much appreciated :)

What I have tried:

Sub simpleXlsMerger()
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")
 
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("C:\Users\hnoorzai\Desktop\Newfolder")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)
 
    'Change B3:H to the range your working on and also B in B65536 to any column required.
    bookList.Worksheets(1).Range("B3:H" & Range("B65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
 
    'Below only change "B" column name to your required column name
    Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    bookList.Close
Next
End Sub

解决方案

See Range.Cells Property (Excel)[^] for how to use numbers only to create a Range object.

You can easily get the number of used rows and columns with

myrows= bookList.Worksheets(1).UsedRange.RowS.Count
mycols= bookList.Worksheets(1).UsedRange.ColS.Count

Excel can copy directly WorkSheets between Books.

Sheets(1).Copy After:=MyBook.Sheets(MyBook.Sheets.Count)

Note that Excel 2013 handle far more than 65536 rows.

这篇关于将多个工作簿合并到一个主工作簿中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！