CODEWITHSUNDEEP
loopsexcelvba
Cenderze
Cenderze

Reputation: 1222

Looping through november, december and subsequently january using VBA

I'm intending to create a macro which finds a file corresponding to the current year and loop through its sheets. The sheets' names are corresponding to the month names (in Swedish). My problem is however that I due to possible deletion of data etc. in the files I import, I do not want to import data which is entered longer than 2 months ago, and loop from that corresponding month to today's month (well, actually to UBound(MyHeadings) as some data I want to extract is constant over the year). 

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

The above is what I've tried. The problem arises if MonthNo -2 is less than 0, since then I have to import the previous year's file (which I do by the IIf statement). The loop however becomes wrong. If the current month is, for instance, january 2015, I want to open up 2014's file, loop through november and december, then open up 2015 file and loop through january. Is there any neat way of achieving this?

Upvotes: 0

Views: 1029

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

Your code could look like this:

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

This code should work OK for any month offset defined in the constant.

Upvotes: 1

Related Questions