VBA-将指定文件夹中的所有excle工作簿合并到同一个工作表？

Question

我编写了以下代码，不知道错在哪儿？望高手指点：
Private Sub CommandButton1_Click()

Dim wkb As Workbook
Dim sht As Sheets
Dim path As String
Dim filename
Dim a As Integer
Dim arry() As String

a = 0

Application.ScreenUpdating = False

path = "C:\Users\fanghao.luo\Desktop\TEST"
filename = Dir(path & "\" & "*.xlsx")

Do While filename <> ""
filename = Dir

If filename = "" Then
Exit Do
End If

a = a + 1
arry(a) = filename

Loop

For i = 1 To a
Workbooks.Open filename = path & arry(i)
Set sht = ActiveWorkbook.Sheets.COPY
ThisWorkbook.Sheets.Add sht
ActiveWorkbook.Close savechanges = False

Next

Application.ScreenUpdating = True

End Sub

Answer 1

错误一，DIR第二次调用会切换到下一个文件，你的循环方法会漏掉第一个文件。
错误二，数组arr为空，赋值会超过下标，可以一开始定义一个较大数量，也可以每次ReDim增加一个。
错误三、打开文件的语句Workbooks.Open filename = path & arry(i)需要在path后面添加&"\"符号
错误四、所有表的复制不能使用Set sht = ActiveWorkbook.Sheets.COPY语句，需要for each st in sheets来逐一执行。