Thursday, October 29, 2009

VBA: Find all files in folder by extension


Sub findBSAfiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
' Specify the folder...
strPath = "pathname"
' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
' Check extension of each file in folder.
For Each objFile In objFolder.Files
If Right(objFile.Name, 4) = ".xls" Then
strName = strName & "**" & objFile.Name & "**" & vbCrLf
End If
Next objFile ' Display file names in message box.
MsgBox strName
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

1 comment:

j2associates said...

Hello John,

The file system object GetExtensionName function will get the extension for you, minus the period.