将多个工作表复制到VBA工作簿中

Question

将多个工作表复制到VBA工作簿中

3

以下代码可完美地将指定工作簿中活动工作表的数据复制到一个新的未命名工作簿中。它从第一个文件复制第一行，并将其他文件（第一行标题除外）的数据与其合并。

然而，我正在学习并希望知道如何以相同的方式将数据合并到宏工作簿本身中（而不是在新工作簿中）。我打算在数据在同一宏簿内合并后进行一些宏录制。

请告诉我如何做到这一点。我尝试将来自新工作簿（在运行以下代码后生成的）的合并表移动/复制到宏工作簿中，然后关闭新的工作簿而不保存它，但迄今为止没有成功。请帮忙。

Option Explicit
Sub CombineDataFiles()

Dim DataBook As Workbook, OutBook As Workbook
Dim DataSheet As Worksheet, OutSheet As Worksheet
Dim TargetFiles As FileDialog
Dim MaxNumberFiles As Long, FileIdx As Long, _
    LastDataRow As Long, LastDataCol As Long, _
    HeaderRow As Long, LastOutRow As Long
Dim DataRng As Range, OutRng As Range

'initialize constants
MaxNumberFiles = 2001
HeaderRow = 1 'assume headers are always in row 1
LastOutRow = 1

'prompt user to select files
Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
With TargetFiles
    .AllowMultiSelect = True
    .Title = "Multi-select target data files:"
    .ButtonName = ""
    .Filters.Clear
    .Filters.Add ".xlsx files", "*.xlsx"
    .Show
End With

'error trap - don't allow user to pick more than 2000 files
If TargetFiles.SelectedItems.Count > MaxNumberFiles Then
    MsgBox ("Too many files selected, please pick more than " &     MaxNumberFiles & ". Exiting sub...")
    Exit Sub
End If

'set up the output workbook
Set OutBook = Workbooks.Add
Set OutSheet = OutBook.Sheets(1)

'loop through all files
For FileIdx = 1 To TargetFiles.SelectedItems.Count

    'open the file and assign the workbook/worksheet
    Set DataBook = Workbooks.Open(TargetFiles.SelectedItems(FileIdx))
    Set DataSheet = DataBook.ActiveSheet

    'identify row/column boundaries
    LastDataRow = DataSheet.Cells.Find("*", SearchOrder:=xlByRows,     SearchDirection:=xlPrevious).Row
    LastDataCol = DataSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    'if this is the first go-round, include the header
    If FileIdx = 1 Then
        Set DataRng = Range(DataSheet.Cells(HeaderRow, 1),                     DataSheet.Cells(LastDataRow, LastDataCol))
        Set OutRng = Range(OutSheet.Cells(HeaderRow, 1),     OutSheet.Cells(LastDataRow, LastDataCol))
    'if this is NOT the first go-round, then skip the header
    Else
        Set DataRng = Range(DataSheet.Cells(HeaderRow + 1, 1), DataSheet.Cells(LastDataRow, LastDataCol))
        Set OutRng = Range(OutSheet.Cells(LastOutRow + 1, 1), OutSheet.Cells(LastOutRow + 1 + LastDataRow, LastDataCol))
    End If

    'copy the data to the outbook
    DataRng.Copy OutRng

    'close the data book without saving
    DataBook.Close False

    'update the last outbook row
    LastOutRow = OutSheet.Cells.Find("*", SearchOrder:=xlByRows,     SearchDirection:=xlPrevious).Row

Next FileIdx

'let the user know we're done!
MsgBox ("Combined " & TargetFiles.SelectedItems.Count & " files!")

End Sub

- Chetan Chimate

1个回答

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

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

- CBRF23 · Answer 1

将您的OutBook变量更改为引用ThisWorkbook，并将OutSheet更改为此工作簿中的一个工作表。

'set up the output workbook
Set OutBook = ThisWorkbook `Workbooks.Add

你可能想要添加一个新的工作表：

Set OutSheet = OutBook.Sheets.Add
OutSheet.Name = "CombineDataFilesOutput"

如果您经常这样做，您可能希望为工作表分配一个唯一的ID，以便您可以添加多个工作表而不必担心重复的工作表名称。我通常使用某种格式的Now()来创建唯一的标识符：

OutSheet.Name = Format(Now(),"YYYYMMDDhhmmss")

我也注意到您对所选文件限制的评论似乎误导了用户。您告诉他们“请选择超过2000个文件”，但应该说“请选择不超过2000个文件”，甚至更好的是“请选择少于2000个文件”。

'error trap - don't allow user to pick more than 2000 files
If TargetFiles.SelectedItems.Count > MaxNumberFiles Then
    MsgBox ("Too many files selected, please pick less than " &     MaxNumberFiles & ". Exiting sub...")
Exit Sub
End If