Access 2016 VBA code to import files with variable names

Question

I am trying to use Access VBA to automate the import (by the push of a button) of daily files that exist in a format like this:

Final_Data_ver20181101063035.xlsx

where the numeric piece translates as: yyyymmddhhmmss.

However, without knowing exactly what time the file was created, Access can't find it. I've been trying something like:

Dim datepiece as String
Dim file as String

datepiece = format(Date,"yyyymmdd")
file = "c:\Users\brian\Final_Data_ver" & datepiece & "*" & ".xlsx"

But I keep getting errors when I try this. It's looking for a file literally named:

Final_Data_ver20181102*.xlsx

Maybe I'm just not able to use wildcards in a statement like this. Any help would be appreciated.

Thanks!

`Debug.Print Dir(file)` should return the first name which matches that pattern. Can you use that? — HansUp, Nov 02 '18 at 20:17
Possible duplicate of [How do i open a file if i only know part of the file name?](https://stackoverflow.com/questions/2860797/how-do-i-open-a-file-if-i-only-know-part-of-the-file-name) — Jeffrey, Nov 03 '18 at 01:34

score 0 · Accepted Answer · answered Nov 03 '18 at 11:52

This is an example of how you could find all files like Final_Data_ver20181102*.xlsx in Folder c:\:

Sub Test()
    Const TEMPLATE As String = "c:\Final_Data_ver20181102*.xlsx"

    Dim filename As String
    filename = Dir(TEMPLATE)
    Do
        If filename = vbNullString Then Exit Do
        'Instead of just printing the filename you can use it to import it like you did before.
        Debug.Print filename
        filename = Dir
    Loop
End Sub

Access 2016 VBA code to import files with variable names

1 Answers1