将所有书籍中的数据收集到某个表中 [英] Take data from all books to some table

问题描述

我在EXCEL(尤其是VBA)中是一个新手.我尝试写出这样的逻辑:

I am very new in EXCEL (especially in VBA). I try to write logic that:

转到所有打开的书，如果某本书的工作表名为"Test"，则它应从命名范围"Table"中获取数据，然后将其从书ALLDATABOOK中的ALLDATA表中追加到Table1中.我尝试写这个，有人可以帮我吗?

go to all open books, if some book has sheet with name "Test", it should take data from named range "Table" and then append it to the Table1 from sheet ALLDATA in book ALLDATABOOK. I try to write this, can someone help me?

这是我的代码:

Private Sub CommandButton1_Click()
   Dim book As Object
    Dim lst As ListObject
   Dim iList As Worksheet
   For Each book In Workbooks

   For Each iList In book.Sheets
        If iList.Name = "Test" Then

        book.Sheets(iList.Name).Activate
Range("Table").Select


        End If
    Next

   Next
End Sub

推荐答案

尝试一下(为Excel 2007+编写，可能不适用于早期版本)

Try this (written for Excel 2007+, may not work for earlier versions)

Private Sub CommandButton1_Click()
    Dim book As Workbook
    Dim lst As ListObject
    Dim iList As Worksheet
    Dim Rng As Range

    Dim wbAllDataBook As Workbook
    Dim shAllData As Worksheet

    ' Get reference to ALLDATA table
    Set wbAllDataBook = Workbooks("ALLDATABOOK.xlsm")  '<-- change to suit your file extension
    Set shAllData = wbAllDataBook.Worksheets("ALLDATA")
    Set lst = shAllData.ListObjects("Table1")

    For Each book In Workbooks
        ' Use error handler to avoid looping through all worksheets
        On Error Resume Next
        Set iList = book.Worksheets("Test")
        If Err.Number <> 0 Then
            ' sheet not present in book
            Err.Clear
            On Error GoTo 0
        Else
            ' If no error, iList references sheet "Test"
            On Error GoTo 0
            ' Get Reference to named range
            Set Rng = iList.[Table]
            ' Add data to row below existing data in table.  Table will auto extend
            If lst.DataBodyRange Is Nothing Then
                ' Table is empty
                lst.InsertRowRange.Resize(Rng.Rows.Count).Value = Rng.Value
            Else
                With lst.DataBodyRange
                    .Rows(.Rows.Count).Offset(1, 0).Resize(Rng.Rows.Count).Value = Rng.Value
                End With
            End If
        End If
    Next
End Sub

---

更新:

要与Excel 2003一起使用，请替换

To use with Excel 2003 replace

If lst.DataBodyRange Is Nothing Then

使用

If Not lst.InsertRowRange Is Nothing Then

这篇关于将所有书籍中的数据收集到某个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！