excel filename wildcard digits

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel filename wildcard digits

Excel filename wildcard digits

resolvedResolved · High Priority · Version 2007

Eileen has attended:
Excel VBA Advanced course

Excel filename wildcard digits

Hi,

We are trying to code a macro that opens the latest file in a folder with a specified filename in this format:

YTD_250412 at 1108.xlsx

where the 250412 and the 1108 change from file to file:

YTD_###### at ####.xlsx

We don't want a file called YTD_250412 at 1108 any other details.xlsx to be opend.

Is there any way of coding in for this, this is what we have so far?

'Sub latestfile()
'
'Dim strCurrentFile As String
'Dim datCurrentFileDate As Date
'Dim strLatestFile As String
'Dim datLatestFileDate As Date
'
'strCurrentFile = Dir("C:\temp\YTD_*" & " at " & "*.xlsx")' Do While strCurrentFile <> ""
' datCurrentFileDate = filedatetime("c:\temp\" & strCurrentFile)
' If datCurrentFileDate > datLatestFileDate Then
' strLatestFile = strCurrentFile
' datLatestFileDate = datCurrentFileDate
' End If
' strCurrentFile = Dir
' Loop
'
' MsgBox "Most recent file: " & strLatestFile & vbCr _
'
'End Sub

This will work but we don't want to specify the date & time:

'strCurrentFile = Dir("C:\temp\YTD_" & "250412" & " at " & "1108" & ".xlsx")

We tried this also but it wont work:

'strCurrentFile = Dir("C:\temp\YTD_" & "[0-9][0-9][0-9][0-9][0-9][0-9]" & " at " & "[0-9][0-9][0-9][0-9]" & ".xlsx")

Any advice?

Eileen.

RE: Excel filename wildcard digits

Hi Eileen, thanks for your query. On your advanced Excel VBA course you will have covered arrays. I would loop through the folder of files extracting (using MID, probably) the date and time of each file and add it to an array.

Then I would loop through the array and find the latest date and time. I would pass those values to two other variables.

I would then concatenate those variables into a filename string and finally open only that file.

Hope this helps,

Anthony

RE: Excel filename wildcard digits

Thanks Anthony.

We will try it the way you suggested.

Kind Regards,

Eileen.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Validating text entries

1. Select the range of cells.
2. From the Data menu, select Validation.
3. Select the Settings tab.
4. From the Allow dropdown list, select Custom.
5. In the Formula box, enter the following formula:

=IsText (A1)

where A1 is the first cell in the range.
6. Click OK.

View all Excel hints and tips


Server loaded in 0.06 secs.