Reading through files in a folder
Printer Friendly Page
This code allows the user to read through a folder and process the files that match the criteria.
The dir command will read through the folder given as a parameter.
You can also specify a file extension, a file name or a wildcard in order to restrict which files in the folder are selected.
Dim mfile as String
Dim wkb as Excel.Workbook
mfile = Dir(c:\folder1\ + "JV1*.xls")
Do While mfile <> ""
Set wkb = Workbooks.Open FileName:=mfile
Call processJV1
wkb.Close SaveChanges:=False
mfile = Dir
Loop
MsgBox "Processing is complete "
- MFILE is a variable that contains the name of the file and path found with DIR.
- WKB is the variable assigned to the active workbook that is being processed.
- DIR will return the first file in the path that matches the wildcard JV1*.*.
- DIR will place the first file it finds in the folder into the variable MFILE.
- The code will continue as long as MFILE is not blank.
- If there are no more files in the folder that match the wildcard desciption, then DIR will place a blank value in MFILE.
- Set the variable WKB to the file being opened, which is the Excel file whose name is in MFILE.
- Call the subroutine that processes each Excel workbook.
- After processing the file, the DIR command is executed again.
- DIR will remember the path and wildcard template. Using DIR by itself will read the next file in the folder.
- Loop to the Do While statement to see if another file was found in the folder.
- If there are no more files to process, MFILE will be blank, and the processing will end.
- The DIR command will continue to select files until no more files match the criteria