none
Access VBA to get the file name of a text file RRS feed

  • Question

  • Hi All,

    I am using below code successfully to import text file from a folder into existing access database.

    DoCmd.TransferText acImportDelim, "ImportSpec1", accesstablename, _
    CurrentProject.Path & "\TextFilesFolderName\LastestImportSales_20150121.txt"

    The above code works great but i get text file every week with new file name that always contains string "Sales". So in above code i have to change the file name (LastestImportSales_20150121.txt) every week.

    Is there any possibility to get the file name (text file) from the folder that has text file that contains string "Sales"?

    Thanks,

    Zaveri


    • Edited by zaveri cc Wednesday, October 28, 2015 7:54 PM
    Wednesday, October 28, 2015 7:52 PM

Answers

All replies

  • The easiest way is to acquire a copy of  ["VBA Developer's Handbook" by Ken Getz and Mike Gilbert, Copyright 1997; Sybex, Inc. All rights reserved.] and use their code.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, October 28, 2015 8:59 PM
  • I am using below code successfully to import text file from a folder into existing access database.

    DoCmd.TransferText acImportDelim, "ImportSpec1", accesstablename, _
    CurrentProject.Path & "\TextFilesFolderName\LastestImportSales_20150121.txt"

    The above code works great but i get text file every week with new file name that always contains string "Sales". So in above code i have to change the file name (LastestImportSales_20150121.txt) every week.

    Is there any possibility to get the file name (text file) from the folder that has text file that contains string "Sales"?

    Yes, but what if there are more than one such file?  Will there be old, already imported files in the folder?  What if you miss a week and you have to import both last week's file and this week's?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, October 28, 2015 9:04 PM
  • After import i kill that file.

    Any way,  i found out the solution using Dir function

    While MyFile <> ""
    If MyFile Like "*Sales*" Then
    SrtSalesFile = MyFile
    End If
    If MyFile Like "*Orders*" Then
    SrtOrdersFile = MyFile
    End If
      
    
       MyFile = Dir()
    Wend
    

    Thanks,

    Zav

    Wednesday, October 28, 2015 9:25 PM
  • After import i kill that file.

    Any way,  i found out the solution using Dir function

    While MyFile <> ""
    If MyFile Like "*Sales*" Then
    SrtSalesFile = MyFile
    End If
    If MyFile Like "*Orders*" Then
    SrtOrdersFile = MyFile
    End If
      
    
       MyFile = Dir()
    Wend

    Thanks,

    Zav

    Why don't you try using a common file dialog to select the desired file.  See below:

    Function GetImportFilePath(InitialFilePath As String) As String
    
        ' Requires reference to Microsoft Office ##.0 Object Library.
        
        On Error GoTo Err_Process
    
        Dim fDialog As Office.FileDialog
        Dim strReturn As String
        Dim strMsg As String
            
        strReturn = ""
        
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        
        With fDialog
            .Title = "Select File"
            .AllowMultiSelect = False
            .InitialFileName = InitialFilePath
            .Filters.Add "Text File", "*.txt"
            
            If .Show = True Then
                strReturn = .SelectedItems(1)
            End If
        End With
        
    Exit_Process:
        GetImportFilePath = strReturn
        Exit Function
    
    Err_Process:
        Select Case Err
        Case Else
            strMsg = Err.Number & " " & Err.Description
        End Select
        MsgBox strMsg, vbExclamation, "Error - GetImportFilePath"
        Resume Exit_Process
        Resume 0 'Debug purposes only
        
    End Function
    

    Wednesday, October 28, 2015 10:32 PM