Multiple Apps

Loop through all/selected files in a folder and its subfolders

Ease of Use

Intermediate

Version tested with

97, 2000, 2003 

Submitted by:

mdmackillop

Description:

The programme gets the path and file name of all or selected file types for processing by other code. 

Discussion:

Basic Coding courtesy of GrahamSkan: If you need to list, delete/rename or otherwise such process all files of a certain type or name, this will create the source of files to be processed. 

Code:

instructions for use

			

Option Explicit Option Compare Text Sub OneType() Const MyPath = "C:\Atest" ' Set the path. Const FileType = "*.*" ' or "*.doc" ProcessFiles MyPath, FileType End Sub Sub OneName() Const MyPath = "C:\Atest" ' Set the path. Const FileName = "MyTest" & "*.*" ProcessFiles MyPath, FileName End Sub Sub MoreTypes() Dim FileType, FT, MyFT As String Const MyPath = "C:\Atest" ' Set the path. FileType = Array("doc", "dot", "xls") For Each FT In FileType MyFT = "*." & FT ProcessFiles MyPath, MyFT Next End Sub Sub ProcessFiles(strFolder As String, strFilePattern As String) Dim strFileName As String Dim strFolders() As String Dim iFolderCount As Integer Dim i As Integer 'Collect child folders strFileName = Dir$(strFolder & "\", vbDirectory) Do Until strFileName = "" If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then If Left$(strFileName, 1) <> "." Then Redim Preserve strFolders(iFolderCount) strFolders(iFolderCount) = strFolder & "\" & strFileName iFolderCount = iFolderCount + 1 End If End If strFileName = Dir$() Loop 'process files in current folder strFileName = Dir$(strFolder & "\" & strFilePattern) Do Until strFileName = "" 'Do things with files here***************** Debug.Print strFolder & "\" & strFileName '******************************************* strFileName = Dir$() Loop 'Look through child folders For i = 0 To iFolderCount - 1 ProcessFiles strFolders(i), strFilePattern Next i End Sub

How to use:

  1. Open Word or Excel.
  2. Alt + F11 to open the VBE.
  3. Insert | Module.
  4. Paste the code in the Code Window that opens up.
  5. Close the VBE (Alt + Q or press the X in the top right corner).
  6. For added flexibility; incorporate the "Allow user to Browse for a Folder" KB entry
  7. http://www.vbaexpress.com/kb/getarticle.php?kb_id=246
 

Test the code:

  1. Alter the Const values and/or FileType array to your chosen settings
  2. Select and run one of the three sub procedures
  3. View the results in the Immediate window
 

Sample File:

No Attachment 

Approved by Ken Puls


This entry has been viewed 252 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express