Reading through files in a folder

by Linda Quinn

Close Window

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

=================================================================
This content was created by Linda Quinn of LQNet.

See http://www.lqnet.com for a great collection of articles on this and other topics.

=================================================================

====================================================================
Want an expert to help with your project? LQ Systems, Inc. Business Solutions
====================================================================

Reading through files in a folder

Copyright © 2006-2009, LQ Systems,Inc. All rights reserved.