Question

Locked

Excel VBA open file with partial name

By ringer ·
I am trying to create a macro that will open a file based on the first 4 characters. The remainder of the name is the date which changes every day. The files are always in the same folder.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

You will need to provide an input box and type the rest of the name

by ThumbsUp2 In reply to Excel VBA open file with ...

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.

Collapse -

Search for file name

by ringer In reply to You will need to provide ...

Thanks for your response. Is there a way to search for the file (the same way you can do in the Excel file open functions using Tools) and then plug the full name into an open function

Collapse -

Easy to do if the format of the date stays the same

by mglenville In reply to Excel VBA open file with ...

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

Collapse -

Full solution (works in excel 2007, not tested in 2003)

by mglenville In reply to Excel VBA open file with ...

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

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums