How can you open a file based on part of the name of a file?

Just put a simple ASCII text file (.txt) in a fixed location (could be c: root or windows directory). Specify excel filename in this file. So, every time you want to access valid excel file, read text file first, for filename, and open it.
Also you can save the valid filename in Windows Registry, by example in some key like:
HKEY_LOCALMACHINE\Software\YourCompany\YourSoftware  ---> Key "Filename",  Value="somefile.xls"

I need a little more help here on this suggestion...how would this not be hardcoding the filename?

  The following vb code will match all files to a wildcard pattern.

    Dim filename As String
    filename = Dir$("c:\mypath\file*.xsl")
    While filename <> ""
        filename = Dir$()
    Wend

Assuming there is only one file the line:
    filename = Dir$("c:\mypath\file*.xsl")
will return the matched file name.

SCDMETA,

So is this pulling all .xls files? Or can  specify something like "contains xxmop" or *xxmop*?

I feel it will be a bit difficult for you to implement this, because you will need to program both excel and access VBA.
I will give you another alternative, but you will have to ask for details in the "Microsoft Access" topic area.
You can analize file date/time to search for most recent. So you have to do nothing from Excel side.
Just implement a function in Access that search the most recent excel file in a directory (or directory branch). Something like this:
https://www.experts-exchange.com/questions/10206493/file-dates-from-winnt-to-access.html

This is a modified version, don't have Access to test right now, maybe another Expert can help:

Function searchLastXlsFile() as String
Dim lpFindFileData As WIN32_FIND_DATA, hFindFirst As Long, i As Long
Dim recentTime, fileTime as Long

With Application.FileSearch
    .NewSearch
    .SearchSubFolders = False
    .filename = "*.xls"
    .lookin = "C:\"               <---- Specify your directory here
    .Execute
    If .FoundFiles.Count >= 1 Then
        For i = 1 To .FoundFiles.Count
            hFindFirst = FindFirstFile(.FoundFiles(i), lpFindFileData)
            fileTime = lpFindFileData.ftCreationTime
            If i = 1 Then
                   recentTime = fileTime
                   searchLastXlsFile = lpFindFileData.cFileName
            Else
                   If (recentTime < fileTime) Then
                         recentTime = fileTime
                         searchLastXlsFile = lpFindFileData.cFileName
                   EndIf
            EndIf
        Next
    End If
End With
End Function

Thank you Jaime - this is very clear and very detailed....i am splitting the points between you guys..
thanks

My example: filename = Dir$("c:\mypath\file*.xsl")
will pull
file.xsl
file1.xsl
filethis is a very long name.xsl

Yes, you can specify "*xxmop*"

Actually, this is what I have for the import path:

Dim strFilename, TblName, TableName As String
Dim newFileName As String
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
Dim altertable As String
Dim update As String


On Error GoTo GetWorkbook_Err
                               
strFilename = "C:\planning\Scanner MOP 2004\Na*.xls"
 
newFileName = Mid(Left$([strFilename], InStr(1, [strFilename], ".") - 1), 30)



Can I modify this to just search for any .xls file??? or a file that "contains" na, or namop????

Awsome SCDMETA - thank you!

Actually SCDMETA, this is not working for me:
My example: filename = Dir$("c:\mypath\file*.xsl")
will pull
file.xsl
file1.xsl
filethis is a very long name.xsl

Yes, you can specify "*xxmop*"


strFilename = Dir$("C:\planning\Scanner MOP 2004\Na*.xls")
     While strFilename <> ""
        strFilename = Dir$()
        Wend

I'm not sure what is not working.  Could you be more specific?
I created this program on my machine, and dumped na* files in the directory.

Sub DumpFileList()
    strFilename = Dir$("C:\planning\Scanner MOP 2004\Na*.xls")
     While strFilename <> ""
            Debug.Print "C:\planning\Scanner MOP 2004\" & strFilename
            ' do something with the file here

            strFilename = Dir$()
     Wend
End Sub

Here is the output in the debug window.
C:\planning\Scanner MOP 2004\Na1-1-2000.xls
C:\planning\Scanner MOP 2004\Na2-1-2000.xls
C:\planning\Scanner MOP 2004\Na3-1-2000.xls

Nice - it may be the later part of my code......can you look at this and tell me whats happening?? I think my references to the strFilename are now being disrupted??


Function GetWorkbook()
Dim objWkBook As Excel.Workbook
Dim objSheet As Worksheet
Dim strWBname As String
Dim strWSname As String
Dim strWSnameNS As String
Dim i As Integer
Dim intCount As Integer
Dim strFilename, TblName, TableName As String
Dim newFileName As String
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
Dim altertable As String
Dim update As String


On Error GoTo GetWorkbook_Err
                               
strFilename = Dir("C:\planning\Scanner MOP 2004\Namop*.xls")
While strFilename <> ""
        strFilename = Dir
        Wend

'newFileName = Mid(Left$([strFilename], InStr(1, [strFilename], ".") - 1), 30)

TblName = "NA_MASTER_tbl"

Set objWkBook = GetObject("" & strFilename & "")
strWBname = objWkBook.Name
'intCount = (objWkBook.Sheets.Count) - 1 ' use either of this two..
intCount = (objWkBook.Sheets.Count)      ' will work
  'MsgBox (intCount)


For i = 1 To (intCount)
Set objSheet = objWkBook.Sheets(i)
  strWSname = objSheet.Name
      'Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
   
    'strWSnameNS = Replace(strWSname, " ", "_")
  'MsgBox (strWSnameNS)

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "" & TblName & strWSnameNS & "", strFilename, False, "" & strWSname & "!B57:CA57"
                               
            'altertable = "ALTER TABLE " & TblName & strWSnameNS & " ADD COLUMN Product_Name text"
'MsgBox (altertable)
       'CurrentDb.Execute (altertable)  ' puts the Product_Name column in the table
   
       
            altertable = "update " & TblName & strWSnameNS & " set Product_Name='" & strWSname & "'"
       CurrentDb.Execute (altertable) 'populates the product_name column with products
MsgBox (altertable)

           
   
Next i

objWkBook.Application.Quit
Set objWkBook = Nothing
Set objSheet = Nothing

GetWorkbook_Exit:
   Exit Function
GetWorkbook_Err:
    Debug.Print i; intCount; strWSname
   MsgBox Err.Number & " " & Err.Description
   Resume GetWorkbook_Exit

End Function

Take this comment out

'newFileName = Mid(Left$([strFilename], InStr(1, [strFilename], ".") - 1), 30)

Thanks again for your help SCDMETA, full points to you - thank you....I will take a look at this when I get home - What I am trying to do is import the file NAMOP0704.xls, but I don't want to rely on on the 0704 (time stamp) as this will change month to month, so i just want to pull in the file namop*
But I think the rest of my code may be choking on this "non-file" name...
Thanks