vba - 关于 Dir VBA 的问题

Question

我有一组文件，我想用文件名循环遍历:Budget 2015、Budget 2016、Budget 2017 到 2022。我使用 Dir 循环遍历它们。

Dim OpenWb as workbook
path = Dir("C:\pathtofile\Budget 20??.xlsx") 'Using the ? Dir Wildcard
filepath = "C:\pathtofile\" 'Since Dir returns file name, not the whole path
myHeadings = Split("Januari,Februari,Mars,April,Maj,Juni,Juli,Augusti,September,Oktober,November,December", ",")
j = 0
 Do While Len(path) > 0
    i = 0
    If Dir = "Budget 2014.xlsx" Then
        j=0
    Else
        For i = 0 To UBound(myHeadings)
            Set openWb = Workbooks.Open(filepath & path)
            MsgBox Len(path)
            Set openWs = openWb.Sheets(myHeadings(i))
            If openWs.Range("C34") = 0 Then
                currentWb.Sheets("Indata").Cells(70, i + 27 + 12 * (j + 1)).Value = ""
            Else
                currentWb.Sheets("Indata").Cells(70, i + 27 + 12 * (j + 1)).Value = openWs.Range("C34")
            End If
        Next i
   End if
   path = Dir
   j= j + 1
  Loop

问题在于，在文件路径中还有一个名为 Budget 2014 的文件，我不想循环遍历它，因为 1)没有必要，值已经计算过了，2)因为它在循环中搞砸了我的索引

更新了我的代码。但是在 for i = 0... 循环中使用 msgBox(路径)会返回我不想循环的“Budget 2014.xlsx”，因此这与我的 j 下标“困惑”。

Answer 1

您可以使用 Year 方法。就像是，

Dim OpenWb as workbook, yearNo As Long, filepath As String

filepath = "C:\pathtofile\"
yearNo = Year(Date())

path = Dir("C:\pathtofile\Budget " & yearNo & ".xlsx")

Do While Len(path) > 0
    set OpenWb = Workbooks.open(filepath & path) ' Since Dir only returns file name
    'Doing some things
    yearNo = yearNo + 1
    path = Dir("C:\pathtofile\Budget " & yearNo & ".xlsx")
Loop