Question

我打算创建一个宏,它查找与当前年份相对应的文件并循环遍历其工作表。工作表的名称对应于月份名称 (瑞典语)。然而,我的问题是,由于可能删除导入的文件中的数据等,我不想导入比 2 个月前输入的数据,并从相应的月份循环到今天的月份 (嗯,实际上 UBound (MyHeadings) 是我想要的一些数据。

Dim MonthNo As Integer 'Makes MonthNo = Month(Date) return an integer, instead of actual month name
MonthNo = Month(Date)
myHeadings = Split("Januari,Februari,Mars,April,Maj,Juni,Juli,Augusti,September,Oktober,November,December", ",") 
p3file = "C:\pathtofile\" 'Because Dir does not return pathfile. 
YearNo = Year(Date)
path3 = IIF(MonthNo-2, Dir("C:\pathtofile\" & "-" & YearNo-1 & ".xlsx"),Dir("C:\pathtofile\" & "-" & YearNo & ".xlsx"))
Do While Len(path3) > 0
    Set openWb = Workbooks.Open(p3file & path3)
    For i = MonthNo To UBound(myHeadings)
        Set openWs = openWb.Sheets(CStr(YearNo))
        'Do things
        End If
    Next i
YearNo = YearNo + 1
path3 = Dir("C:\pathtofile\" & YearNo & ".xlsx")   
Loop

以上是我尝试过的。如果 MonthNo-2 小于 0,则会出现问题,因为那时我必须导入上xx年的文件 (由 IIf 语句执行)。然而,循环变得错误。如果当前月份是例如 2015 年 1 月,我想打开 2014 年的文件,循环通过 11 月和 12 月,然后打开 2015 年文件并循环通过 1 月。

1

Answer 1

您的代码可能看起来像这样：

Dim MonthNo As Integer, YearNo As Integer
Dim CurrentYear As Integer
Dim StartDate As Date
Dim i As Integer
Const MonthOffset As Integer = -2
CurrentYear = Year(Date)
StartDate = DateAdd("m", MonthOffset, Date)
MonthNo = Month(StartDate)
YearNo = Year(StartDate)
Do While YearNo <= CurrentYear
'Open file here
  For i = MonthNo To 12
    'Process sheets here
  Next i
  MonthNo = 1
  YearNo = YearNo + 1
Loop

此代码应该可以在常量中定义的任何月份偏移。