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
Application.ScreenUpdating = False
' 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
.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
For i = 1 To .FoundFiles.Count
ActiveCell.Offset(rowOffset:=i - 1, columnOffset:=0) = .FoundFiles(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
MsgBox "There were no files found."
Application.ScreenUpdating = True
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.
If you need to open a file via file filtering
eg *1234.xls then your find that VBA getopenfile is limited...you need to use the comdlg32.dll
Have a look here to do this