VBA: wildcard to open file

  • Can some one tell me how to use a wildcard in a VBA code to open a file when the file change daily but the last 4 dig. of th file stay the same

  • in the following example the named range "xlVar_FileString" (in excel) could contain say
    "*same.xls" where the letters same represent the component of the file name that doesn't change.


    Public vba_CriteriaList(), vba_FieldList(), CurrentFileName As String, vba_FileListArea(), vba_RecordNo As Integer, TempData(), vba_FieldNo As Integer, vba_CriteriaNo As Integer, X As Integer, Y As Integer


    Public Sub ListFiles()


    ' zero value in Range Name "xlVar_FileCount"
    Range("xlVar_FileCount").Value = 2


    ' Clear FileList Display area
    Worksheets("Control Sheet").Activate
    Range("xlVar_FileListArea").Clear
    Range("A1").Select


    Application.ScreenUpdating = False
    Range("xlVar_FileList").Select


    ' Get Folder value from Range Name "xlVar_Folder"
    vba_Folder = Range("xlVar_Folder").Value
    ' Get file name string (wildcards allowed) from Range Name "xlVar_FileString"
    vba_FileString = Range("xlVar_FileString").Value



    ' Do file search based on Parametres above
    Set fs = Application.FileSearch
    With fs
    .LookIn = vba_Folder
    .Filename = vba_FileString
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then

    ' display dialog box showing total number of files returned
    MsgBox "There were " & .FoundFiles.Count & _
    " file(s) found."

    ' Position at Named Range "xlVar_FileList"
    ' so offset positions results
    Range("xlVar_FileList").Activate
    For i = 1 To .FoundFiles.Count
    ActiveCell.Offset(rowOffset:=i - 1, columnOffset:=0) = .FoundFiles(i)
    Next i
    ' Store total number of files returned in Range Name "xlVar_FileCount"
    ' Value in Range Name "xlVar_FileCount" is used to re-size
    ' Dynamic Named Range "xlVar_FileListArea"
    Range("xlVar_FileCount").Value = .FoundFiles.Count
    Else
    MsgBox "There were no files found."
    End If
    End With
    Application.ScreenUpdating = True


    End Sub

    Robert Hind
    Perth - Western Australia

  • The code in my previous posting was to designed to list a series of files that matched a specification (including wildcards)...the files were listed in a named range within an excel worksheet.

    Robert Hind
    Perth - Western Australia

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!