VBA to open files with variable characters-CodePudding

I need to open a series of workbooks wherein there are trailing characters that change daily.

The files are named in the following convention:

FileName_YYYY_MM_DD_(random trailing numbers)

I've done a ton of research and only found answers related the date changing. Ideally, I'd like to be able to write around both the issue of the date changing as well as the random trailing characters.

Sub openwb()

Dim sPath As String, sFile As String, sWild As String
Dim wb As Workbook

sPath = "the path where the file is located "
  sWild = sPath & "Name of the file"
    sFile = sPath & Dir(sWild)

Set wb = Workbooks.Open(sFile)

End Sub

CodePudding user response：

Using Format, Date, and a wildcard *:

sWild = sPath & Format(Date, "_yyyy_mm_dd_") & "*"
sFile = Dir(sWild)

If sFile <> vbNullString Then
   Set wb = Workbooks.Open(sPath & sFile)
End If

CodePudding user response：

Open Excel Files in Folder

Sub OpenWorkbook()
    
    ' Define constants.
    
    Const SRC_FOLDER_PATH As String = "C:\Test"
    Const SRC_FILE_BASE_NAME As String = "*" ' adjust!
    Const SRC_DATE_PATTERN As String = "_YYYY_MM_DD_"
    Const SRC_TRAIL As String = "*"
    Const SRC_EXTENSION_PATTERN As String = ".xls*" ' adjust!
    
    ' Source Path
    
    Dim pSep As String: pSep = Application.PathSeparator
    Dim sPath As String: sPath = SRC_FOLDER_PATH
    If Right(sPath, 1) <> pSep Then sPath = sPath & pSep
    
    Dim sFolderName As String: sFolderName = Dir(sPath, vbDirectory)
    If Len(sFolderName) = 0 Then
        MsgBox "The path '" & sPath & "' was not found.", vbExclamation
        Exit Sub
    End If
    
    ' First Source File
    
    Dim sDate As Date: sDate = Date ' 'Date' for today; adjust!
    
    Dim sFilePattern As String: sFilePattern = SRC_FILE_BASE_NAME _
        & Format(sDate, SRC_DATE_PATTERN) & SRC_TRAIL & SRC_EXTENSION_PATTERN
    
    Dim sFileName As String: sFileName = Dir(sPath & sFilePattern)
    
    If Len(sFileName) = 0 Then
        MsgBox "No files matching the pattern '" _
            & sFilePattern & "' found in '" & sPath & ".", vbExclamation
        Exit Sub
    End If
    
    ' Loop
    
    Dim swb As Workbook ', sws As Worksheet, srg As Range

    Do While Len(sFileName) > 0
        
        ' Test with...
        Debug.Print sFileName
        
        'Set swb = Workbooks.Open(sPath & sFileName)
           
        ' code per each opened workbook...
           
        ' When reading...
        'swb.Close SaveChanges:=False
        sFileName = Dir ' next file
    Loop

    ' Inform.

    MsgBox "Done", vbInformation

End Sub