Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For loop for each file in a folder?
Tom Ogilvy Wrote: sFile = dir("C:\Myfolder\*.txt") do while sFile < "" bk =Workbooks.Open("C:\MyFolder\" & sFile) . . . bk.Close SaveChanges:=false sFile = Dir() Loop Since I'm a beginner at VBA (but not new to programming), I'd like to try to understand the logic in this code... The first 2 lines say, do this as long as the directory of C:\Myfolder\*.txt is not empty, right? Then I'm not sure what the rest means. How does it cycle through each file in a directory? Thanks, Kieran -- Kieran1028 ------------------------------------------------------------------------ Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678 View this thread: http://www.excelforum.com/showthread...hreadid=277010 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For loop for each file in a folder?
sFile = dir("C:\Myfolder\*.txt")
sFile = Dir() This is a paired command. the second use of dir is dependent on the argument of the first use. So each subsequent call to the second dir ( sFile = Dir() ) progressively retrieves file names from the directory. The next call after it retrieves the name of the last file is a null or empty string. So as long as sFile is not a null or empty string, the loop continues. Dir just returns the name of the file (no path). So within the loop, Workbooks.Open opens the file and sets bk as a reference to it (I didn't include the set command - my typo) . Once opened, process the file, then close it, either saving or not saving the changes. Retrieve the next file name (or an empty string) and decide whether to loop or break out. Dim sFile as String, bk as Workbook sFile = dir("C:\Myfolder\*.txt") do while sFile < "" set bk =Workbooks.Open("C:\MyFolder\" & sFile) . . . bk.Close SaveChanges:=false sFile = Dir() Loop -- Regards, Tom Ogilvy "Kieran1028" wrote in message ... Tom Ogilvy Wrote: sFile = dir("C:\Myfolder\*.txt") do while sFile < "" bk =Workbooks.Open("C:\MyFolder\" & sFile) . . . bk.Close SaveChanges:=false sFile = Dir() Loop Since I'm a beginner at VBA (but not new to programming), I'd like to try to understand the logic in this code... The first 2 lines say, do this as long as the directory of C:\Myfolder\*.txt is not empty, right? Then I'm not sure what the rest means. How does it cycle through each file in a directory? Thanks, Kieran -- Kieran1028 ------------------------------------------------------------------------ Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678 View this thread: http://www.excelforum.com/showthread...hreadid=277010 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For loop for each file in a folder?
The first line says return the name of the first file in directory
c:\MyFolder that matches the file string *.txt into sFile Then a do loop while sFile is not empty (if there are none matching in the first statement, this loop never enters) Then it processes it, and the next Dir command retrieves the next file to meet those match criteria, loading sFile with that value, and continuing or exiting the loop -- HTH RP (remove nothere from the email address if mailing direct) "Kieran1028" wrote in message ... Tom Ogilvy Wrote: sFile = dir("C:\Myfolder\*.txt") do while sFile < "" bk =Workbooks.Open("C:\MyFolder\" & sFile) . . . bk.Close SaveChanges:=false sFile = Dir() Loop Since I'm a beginner at VBA (but not new to programming), I'd like to try to understand the logic in this code... The first 2 lines say, do this as long as the directory of C:\Myfolder\*.txt is not empty, right? Then I'm not sure what the rest means. How does it cycle through each file in a directory? Thanks, Kieran -- Kieran1028 ------------------------------------------------------------------------ Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678 View this thread: http://www.excelforum.com/showthread...hreadid=277010 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop through folder of workbooks and add rows | Excel Worksheet Functions | |||
For loop for each file in a folder? | Excel Programming | |||
Loop thru All Files in a Folder | Excel Programming | |||
Loop through all files in a folder | Excel Programming | |||
Loop through workbooks in a folder and return the value of cell M43 | Excel Programming |