Open File with Partial Name

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

Open File with Known Partial Name

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

Open File with Known Partial Name

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.