Find text file using VBA

Function: Find text file in computer using VBA

This function returns True or False based on whether the file was found in the specified drive (located on a local drive or network)

Public Function FindMyFile(FileAddress As Variant, FileNm As Variant)

Application.ScreenUpdating = False

‘ Code Written & Published by Excelmodelers (www.excelmodeler.com) 19 Dec 12

Dim WbF As Workbook

Dim objFSOF As FileSystemObject

Dim objFolderF As Folder

Dim objFileF As File

Dim FileNameArrF() As String

 

Set objFSOF = CreateObject(“Scripting.FileSystemObject”)

Set objFolderF = objFSOF.GetFolder(FileAddress)

 

If Not objFolderF.Files.Count = 0 Then

ReDim FileNameArrF(1 To objFolderF.Files.Count)

jF = 1

For Each objFileF In objFolderF.Files

FileNameArrF(jF) = objFileF.Name

jF = jF + 1

Next objFileF

Else

ReDim FileNameArrF(1 To 1)

End If

 

If IsError(Application.Match(FileNm, FileNameArrF, 0)) Then

FindMyFile = False

Else

FindMyFile = True

End If

End Function

 

Comments are closed.