VBA code to loop through files in a folder

Question

I am looking for a way to automate the process of opening 1300 separate Excel files in separate subfolders and making the same change in each file. The change will be to the same cell on the same tab of each file. I'm Almost done with the code just having problems with Workbook.open and making sure its selecting the correct excel files. I have my code listed below. I also have some more information if you need it. Thanks for the help!

The Excel Files i need to access are in sub folders in a parent folder/ The Excel files that need to be updated all end in the List.xlsx

Sub LoopFilesInFolder()
Dim folderName As String
Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim FSOFile As Object

'Set the file name to a variable
folderName = "\\net\fs1\users\alex.wood\Desktop\TC Excel Test and Creation Files\VBA TEST FOLDER"

'Set all the references to the FSO Library
Set FSOLibrary = New FileSystemObject
Set FSOFolder = FSOLibrary.GetFolder(folderName)
Set FSOFile = FSOFolder.Files

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile
    Workbooks.Open Filename:=Dir("\\net\fs1\users\alex.wood\Desktop\TC Excel Test and Creation Files\VBA TEST FOLDER\*list*")

    Range("G1").Select
    ActiveCell.FormulaR1C1 = "New Thin Client"
    ActiveWorkbook.Save
    ActiveWindow.Close

Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set FSOFile = Nothing

End Sub

You might want to check this answer https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba — Badda_Bing, Aug 11 '20 at 13:03

Pedro Nunes · Answer 1 · 2020-08-11T14:06:10.533

0

You can do that

Sub LoopFilesInFolder()
    'Improve performance
    Application.ScreenUpdating = True

    mypath = "\\net\fs1\users\alex.wood\Desktop\TC Excel Test and Creation Files\VBA TEST FOLDER\"

    myfile = Dir(mypath & "*.xls*")
  
    Do While myfile <> ""
        Workbooks.Open mypath & myfile
        Workbooks(myfile).Activate
        ActiveSheet.Range("G1").Value = "New Thin Client"
        Workbooks(myfile).Save
        Workbooks(myfile).Close
        myfile = Dir
    Loop

    Application.ScreenUpdating = False
End Sub

edited Aug 11 '20 at 14:06

answered Aug 11 '20 at 12:18

Pedro Nunes

28
6

It does not error out it does not do anything. It acts like the macro is completed but it did not update anything – Alex Wood Aug 11 '20 at 13:05
Sorry, I forgot a \ in the end of `mypath`. You can include a `MsgBox myfile` right before `myfile = Dir` to get the name of file edited. – Pedro Nunes Aug 11 '20 at 14:06

VBA code to loop through files in a folder

1 Answers1

Linked