Excel VBA open file with partial name

Computers can't open files if they don't know the full file name.

If, in your code, you could provide an input box where you could type the rest of the file name, you could concatenate both variables into one, thereby providing the full file name.

If the format of the date stays the same you can make the file name yourself in the vba:

e.g. the below code will give you a file name of 'abcd - 13-Jan-09.xls'

Sub ...

Dim InputDate as date
Dim strFileName as string

InputDate = Date 'using today's date

strFileName = "abcd - " & format(InputDate, "dd-mmm-yy") & ".xls"

End Sub

Pass in the file path (without a '\' on the end) and the partial name that you want (e.g. the first 4 characters).

This will get the full name for the first file that it finds that matches.

Function GetFullFileName(strfilepath As String, _
strFileNamePartial As String) As String

Dim objFS As Variant
Dim objFolder As Variant
Dim objFile As Variant
Dim intLengthOfPartialName As Integer
Dim strfilenamefull As String

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.getfolder(strfilepath)

'work out how long the partial file name is
intLengthOfPartialName = Len(strFileNamePartial)

For Each objFile In objFolder.Files

'Test to see if the file matches the partial file name
If Left(objFile.Name, intLengthOfPartialName) = strFileNamePartial Then

'get the full file name
strfilenamefull = objFile.Name
Exit For

Else

End If

Next objFile

'Return the full file name as the function's value
GetFullFileName = strfilenamefull

End Function