CODEWITHSUNDEEP
vbaexcel
Cenderze
Cenderze

Reputation: 1212

Questions regarding Dir VBA

I'm having a set of files which I want to loop through with the filenames: Budget 2015, Budget 2016, Budget 2017 up to 2022. I loop through them using 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

The trouble is that in the filepath there's also a file called Budget 2014, which I do not want to loop through because 1) It's not necessary, the values are computed already and 2) Since it screws up my indices in the loop

Updated my code. But using msgBox (path) inside the for i = 0... loop returns "Budget 2014.xlsx" which I did not want to loop, and hence this "messes" with my j subscript.

Upvotes: 1

Views: 200

Answers (3)

snb
snb

Reputation: 343

Sub M_snb()
 c00 = "C:\pathtofile\"
 sn = Application.GetCustomListContents(4)

 c01 = Dir(c00 & "Budget 20*.xlsx")
 Do While c01 <> ""
  If c01 <> "Budget 2014.xlsx" Then
   With GetObject(c00 & c01)
    For j = 0 To UBound(sn)
     c02 = c02 & "|" & IIf(.Sheets(sn(j)).Range("C34") = 0, "", .Sheets(sn(j)).Range("C34"))
    Next
    .Close 0
   End With
  End If
  c01 = Dir
 Loop

 sp = Split(Mid(c02, 2), "|")
 ThisWorkbook.Sheets("Indata").Cells(70, 51).Resize(, UBound(sp)) = sp
End Sub

Upvotes: 1

FreeMan
FreeMan

Reputation: 5687

You could also try this:

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
'change here: only execute if it's NOT the file you're NOT after
    If Dir <> "Budget 2014.xlsx" Then
        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
'Change here: only update path & j if you processed the file
       path = Dir
       j= j + 1
   End if
  Loop

Upvotes: 1

PaulFrancis
PaulFrancis

Reputation: 5809

You could make use of the Year method. Something like,

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

Upvotes: 1

Related Questions