FOR EACH Loop with Words

Question

How can I use words to apply FOR EACH loops? Suppose I have 96 separate csv files as follows and need to change the very first cell of each. The files are

c:\2010\2010_jan_apple.csv
c:\2010\2010_jan_orange.csv
c:\2010\2010_feb_apple.csv
c:\2010\2010_feb_orange.csv
...  
c:\2010\2010_dec_apple.csv
c:\2010\2010_dec_orange.csv
c:\2012\2011_jan_apple.csv
c:\2012\2011_jan_orange.csv
...  
c:\2016\2016_dec_apple.csv
c:\2016\2016_dec_orange.csv

So I think there would be one FOR loop and two FOR EACH loops.

year=2010,2012,2014,2016  
month=jan,feb,...,dec  
type=apple,orange

My silly code is

sub a()
application.displayalerts=false
for year=2010 to 2016 step 2
workbooks.open "c:\" & year & "\" & year & "_jan_apple.csv"
cells(1,1)="apple"
with activeworkbook
.saveas
.close
end with
workbooks.open "c:\" & year & "\" & year & "_jan_orange.csv"
cells(1,1)="orange"
with activeworkbook
.saveas
.close
end with
...
next year
end sub

In VBA, can I create a list such as {"jan","feb",...,"dec"} or {"apple","orange"} to employ FOR EACH iteration? Many thanks.

Siddharth Rout · Accepted Answer · 2019-08-31T03:41:47.873

If you have only "Apple and Oranges" then you do not need a loop for that. If you have more "fruits" then yes, you will require a loop. Also you do not need to have an array for the month names. You could get that from MonthName()

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim flName As String
    Dim i As Long
    Dim yr As Long

    For yr = 2010 To 2016 Step 2
        For i = 1 To 12
            flName = "c:\" & yr & "\" & yr & "_" & Left(MonthName(i), 3) & "_apple.csv"
            'UpdateWb flName, "apple"
            Debug.Print flName
            flName = "c:\" & yr & "\" & yr & "_" & Left(MonthName(i), 3) & "_orange.csv"
            'UpdateWb flName, "orange"
            Debug.Print flName
        Next i
    Next yr
End Sub

Sub UpdateWb(f As String, fruit As String)
    On Error GoTo Whoa

    Dim wb As Workbook
    Set wb = Workbooks.Open(flName)
    wb.Sheets(1).Cells(1, 1) = fruit
    Application.DisplayAlerts = False
    wb.Close (True)
    DoEvents
LetsContinue:
    Application.DisplayAlerts = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

If you have more fruits then you can use a 3rd loop. For example

Sub Sample()
    Dim flName As String
    Dim fruits() As Variant
    Dim i As Long
    Dim yr As Long
    Dim itm As Variant

    '~~> Add more if required
    fruits = Array("Apple", "Orange", "Guava")

    For yr = 2010 To 2016 Step 2
        For i = 1 To 12
            For Each itm In fruits
                'c:\2010\2010_jan_apple.csv
                flName = "c:\" & yr & "\" & yr & "_" & Left(MonthName(i), 3) & "_" & itm & ".csv"
                'UpdateWb flName, itm
                Debug.Print flName
            Next itm
        Next i
    Next yr
End Sub

luvlogic · Answer 2 · 2019-08-31T18:14:32.100

Actually, if you're having real files, as your code shows, you can loop through them. Here's one example showing how to do that:

Loop through files in a folder using VBA?

Sub LoopThroughFiles()
    Dim StrFile As String
    StrFile = Dir("c:\testfolder\*test*")
    Do While Len(StrFile) > 0
        Debug.Print StrFile
        StrFile = Dir
    Loop
End Sub

In your case as the files are in different folders, you can recursively iterate..

VBA macro that search for file in multiple subfolders

Function Recurse(sPath As String) As String

    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim mySubFolder As Folder

    Set myFolder = FSO.GetFolder(sPath)
    For Each mySubFolder In myFolder.SubFolders
        Call TestSub(mySubFolder.Path)
        Recurse = Recurse(mySubFolder.Path)
    Next

End Function

Sub TestR()

    Call Recurse("D:\Projets\")

End Sub

FOR EACH Loop with Words

2 Answers2