• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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

Hello,

I am importing Excel files into Access  from a specific location, however, I do not want to hard code the name of the file to import because part of the name will change month to month. Looking for any solution done in code so that I may run it automatically from Access.
Thanks.
0
coolcat139
Asked:
coolcat139
  • 9
  • 4
  • 2
1 Solution
 
Jaime OlivaresCommented:
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"
0
 
coolcat139Author Commented:
I need a little more help here on this suggestion...how would this not be hardcoding the filename?
0
 
SCDMETACommented:
  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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
coolcat139Author Commented:
SCDMETA,

So is this pulling all .xls files? Or can  specify something like "contains xxmop" or *xxmop*?
0
 
Jaime OlivaresCommented:
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:
http://www.experts-exchange.com/Databases/MS_Access/Q_10206493.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
0
 
coolcat139Author Commented:
Thank you Jaime - this is very clear and very detailed....i am splitting the points between you guys..
thanks
0
 
SCDMETACommented:
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*"
0
 
coolcat139Author Commented:
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????
0
 
coolcat139Author Commented:
Awsome SCDMETA - thank you!
0
 
coolcat139Author Commented:
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
0
 
SCDMETACommented:
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
0
 
coolcat139Author Commented:
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







0
 
coolcat139Author Commented:
Take this comment out

'newFileName = Mid(Left$([strFilename], InStr(1, [strFilename], ".") - 1), 30)
0
 
coolcat139Author Commented:
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
0
 
SCDMETACommented:
Just some additional clarification:
The code I gave you

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

Loops through all file names that match the criteria.  When the loop exits the filename variable is equal to "".
You need to work with the filename within the loop.  

I can't evaluate all your code, but I think this is what you are looking for.
Good luck


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 <> ""
   
    ' WORK WITH FILENAME
    'newFileName = Mid(Left$([strFilename], InStr(1, [strFilename], ".") - 1), 30)
   
    TblName = "NA_MASTER_tbl"
   
    Set objWkBook = GetObject("" & "C:\planning\Scanner MOP 2004\" & 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

    ' GET NEXT FILE IF ONE EXISTS    
    strFilename = Dir()
Wend
   
GetWorkbook_Exit:
   Exit Function
GetWorkbook_Err:
    Debug.Print i; intCount; strWSname
   MsgBox Err.Number & " " & Err.Description
   Resume GetWorkbook_Exit

End Function
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now