vba - Excel 目录返回意外的 null

Question

我正在尝试编写我认为是一个简单的例程，以获取目录中的所有 Excel 工作表并将它们复制到主工作表中的选项卡。这是我正在尝试的:

Sub GetSheets()
      myPath = "C:\Users\Brian.Scott\Documents\2017_INVENTORY\TestInv"
      Filename = Dir(myPath)
      MsgBox (Filename)
    Do While Filename <> ""
      Workbooks.Open Filename:=myPath & Filename, ReadOnly:=True
      For Each Sheet In ActiveWorkbook.Sheets
      Sheet.Copy After:=ThisWorkbook.Sheets(1)
      Next Sheet
      Workbooks(Filename).Close
      Filename = Dir()
    Loop
  End Sub

当什么都没有发生时，我放入了 MsgBox。它返回一个空值。 myPath 返回正确的名称 - 我从资源管理器中复制并粘贴了该名称以避免拼写错误。我的目录中只有excel文件，所以我不是错误检查。这是一次性需求，但目录中有超过 200 个文件，所以我想我可以花一点时间在代码上。
任何想法都将不胜感激，因为我对此非常满意。

Answer 1

声明所有变量 - 指定 Option Explicit在每个模块的顶部，然后声明您使用的每个变量，直到代码再次编译。不这样做只会自找麻烦； VBA 将愉快地编译和运行一个错字，调试一点也不好玩。使用选项显式。总是。

Option Explicit

Public Sub GetSheets()
    Const myPath As String = "C:\Users\Brian.Scott\Documents\2017_INVENTORY\"
    Const myFilter As String = "TestInv*.xls?"

    Dim myFilename As String
    myFilename = Dir(myPath & myFilter)

    Do While myFilename <> vbNullString
        MsgBox myFilename
        With Workbooks.Open(Filename:=myPath & myFilename, ReadOnly:=True)
            Dim sh As Worksheet
            For Each sh In .Worksheets
                sh.Copy After:=ThisWorkbook.Sheets(1)
            Next
            .Close
        End With
        Filename = Dir
    Loop
End Sub

请注意 With block - 它持有对 Workbook 的引用反对 Workbooks.Open函数返回，所以你可以做 .Worksheets和 .Close反对它，无需针对 ActiveWorkbook 编写代码，并且无需从 Workbooks 重新获取完全相同的对象引用在每次迭代中收集。

您的 myPath包含路径，是的，但也包含通配符，我怀疑这会按预期工作:

Workbooks.Open "C:\Users\Brian.Scott\Documents\2017_INVENTORY\TestInv*.xls?\TestInv42.xlsx"

这就是我拆分 myPath 的原因将字符串转换为路径和过滤器:您将过滤器提供给 Dir函数，并提供 Workbooks.Open具有文件名 Dir 的路径的函数回来。