Solved

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

Posted on 2004-07-30
15
332 Views
Last Modified: 2010-04-17
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
Comment
Question by:coolcat139
  • 9
  • 4
  • 2
15 Comments
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 11680716
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
 

Author Comment

by:coolcat139
ID: 11680887
I need a little more help here on this suggestion...how would this not be hardcoding the filename?
0
 
LVL 6

Expert Comment

by:SCDMETA
ID: 11680967
  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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:coolcat139
ID: 11681034
SCDMETA,

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

Expert Comment

by:Jaime Olivares
ID: 11681040
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
 

Author Comment

by:coolcat139
ID: 11681203
Thank you Jaime - this is very clear and very detailed....i am splitting the points between you guys..
thanks
0
 
LVL 6

Expert Comment

by:SCDMETA
ID: 11681230
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
 

Author Comment

by:coolcat139
ID: 11681240
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
 

Author Comment

by:coolcat139
ID: 11681307
Awsome SCDMETA - thank you!
0
 

Author Comment

by:coolcat139
ID: 11681324
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
 
LVL 6

Expert Comment

by:SCDMETA
ID: 11681480
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
 

Author Comment

by:coolcat139
ID: 11681510
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
 

Author Comment

by:coolcat139
ID: 11681522
Take this comment out

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

Author Comment

by:coolcat139
ID: 11681559
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
 
LVL 6

Accepted Solution

by:
SCDMETA earned 500 total points
ID: 11681631
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is about my first experience with programming Arduino.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question