Solved

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

Posted on 2004-07-30
15
307 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
Comment Utility
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
Comment Utility
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
Comment Utility
  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
 

Author Comment

by:coolcat139
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:coolcat139
Comment Utility
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
Comment Utility
Awsome SCDMETA - thank you!
0
 

Author Comment

by:coolcat139
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Take this comment out

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

Author Comment

by:coolcat139
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now