How to open a file with known partial name from a folder automatically using VBA. Watch the video below:

A user wants to open a file with its partial name, Like he has invoice no 125027 and a folder contains so many files as mentioned below.

estatment-MER-CAP-6281-125027.CSV
estatment-MER-CAP-6281-125028.CSV
estatment-MER-CAP-6281-125029.CSV

Here’s the complete VBA code to open files with only part of the name available:

Sub OpenFileWithPartialName()
Dim sFileName As String
Dim MyPath As String
Dim sPartialName As String
Dim sExt As String

MyPath = “C:\MyDirectory\estatment-MER-CAP-6281-“
sExt = “.CSV”
sPartialName = InputBox(“Enter the partial name of the file”)
sFileName = MyPath & sPartialName & sExt
Workbooks.Open Filename:=sFileName

End Sub

We could have also assigned the common part of the name to a variable like so:

Dim sPartName as String

sPartName=”estatment-MER-CAP-6281-“

If we wish to open all files in the folder then we can use the following VBA code:

Sub OpenAllFiles()

Dim strFileName As String

Dim strPartName As String

Dim strExt As String

strPartName = “C:\MyDirectory\estatment-MER-CAP-6281-“

strExt = “.csv”

strFileName = Dir(strPartName & “*” & strExt)

If strFileName = “” Then
MsgBox “Files not found”
Else

Do While strFileName <> “
Workbooks.Open “C:\MyDirectory\” & strFileName
strFileName = Dir
Loop
End If

End Sub