How to import external file with date as name-CodePudding

I wanted to import or copy and paste data from an external file into the current Excel file using VBA. However, the external file contain a date of the previous month in it. For example, the external file name is Report_20221128. Every month, this external file date maybe different and not necessary be 28 of the month.

Here is what I have done so far.

Sub Report_Run()
    
    Dim wb As Workbook
    Dim file As Variant
    Dim wbrow As Long, wbrow2 As Long, wbrow3 As Long

    Day = Application.WorksheetFunction.EoMonth(Now(), "-1")
    Set wb = Workbooks("Run Report " & VBA.Format(LDay, "ddmmyyyy") & ".xlsb")
 
    wb.Worksheets("DD").Activate
    wbrow3 = Cells(Rows.Count, "A").End(xlUp).Row
    
    file = Dir(Environ("userprofile") & "\Desktop\Reports\Report_" & Format(Date, "yyyymmdd") & ".xlsx")

End Sub

However, the code unable to read on this line

file = Dir(Environ("userprofile") & "\Desktop\Reports\Report_" & Format(Date, "yyyymmdd") & ".xlsx")

Therefore, how should I set the code so that it can read this external file that contain any date of the previous month in it?

CodePudding user response：

Import Worksheet From File Matching a Pattern

Sub ImportLastMonth()
    
    ' Constants
    Const SRC_PATH_RIGHT As String = "\Desktop\Reports\"
    Const SRC_FILE_LEFT As String = "Report_"
    Const SRC_FILE_RIGHT As String = ".xlsx"
    Const SRC_WORKSHEET_ID As Variant = "Sheet1" ' adjust! Name or Index
    
    ' Source Path
    Dim sPathLeft As String: sPathLeft = Environ("USERPROFILE")
    Dim sPath As String: sPath = sPathLeft & SRC_PATH_RIGHT
    sPath = "C:\Test\T2022\74877583\Reports\"
    Dim sFolderName As String: sFolderName = Dir(sPath, vbDirectory)
    If Len(sFolderName) = 0 Then
        MsgBox "The path '" & sPath & "' was not found.", vbCritical
        Exit Sub
    End If
    
    ' Source File
    Dim sPatternLeft As String: sPatternLeft = SRC_FILE_LEFT _
        & Format(CDate(Application.EoMonth(Now, "-1")), "yyyymm")
    Dim sPattern As String: sPattern = sPatternLeft & "*" & SRC_FILE_RIGHT
    Dim sFileName As String: sFileName = Dir(sPath & sPattern)
    If Len(sFileName) = 0 Then
        MsgBox "No files matching the pattern '" & sPattern & "' in '" _
            & sPath & "' found.", vbCritical
        Exit Sub
    End If
    
    ' Day
    
    Dim DayStart As Long: DayStart = Len(sPatternLeft)   1
    
    Dim DayNumString As String, DayNum As Long, NewDayNum As Long
    
    Do While Len(sFileName) > 0
        DayNumString = Mid(sFileName, DayStart, 2)
        If IsNumeric(DayNumString) Then
            NewDayNum = CLng(DayNumString)
            If NewDayNum > DayNum Then DayNum = NewDayNum
        End If
        Debug.Print sFileName, DayNumString, NewDayNum, DayNum
        sFileName = Dir
    Loop
            
    If DayNum = 0 Then
        MsgBox "No file found.", vbCritical
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    ' Source
    Dim sFilePath As String
    sFilePath = sPath & sPatternLeft & Format(DayNum, "0#") & SRC_FILE_RIGHT
    Dim swb As Workbook: Set swb = Workbooks.Open(sFilePath, True, True)
    Dim sws As Worksheet: Set sws = swb.Sheets(SRC_WORKSHEET_ID)
    
    ' Destination
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    
    ' Copy
    sws.Copy After:=dwb.Sheets(dwb.Sheets.Count) ' last
    swb.Close SaveChanges:=False
        
    Application.ScreenUpdating = True
    
    ' Inform.
    MsgBox "Last month's final report imported.", vbInformation
        
End Sub

CodePudding user response：

Using FileSystemObject and Like

Option Explicit
Sub Report_Run()
    
    Dim wb As Workbook, TargetWB As Workbook
    Dim DT As Date
    Dim wbrow As Long, wbrow2 As Long, wbrow3 As Long
    Dim FSO As Object, oFolder As Object, oFile As Object
    
    Set FSO = CreateObject("scripting.filesystemobject")
    ' > This needs to be the folder you expect to contain your report
    Set oFolder = FSO.getfolder("C:\Users\cameron\Documents\")
    
    ' > Date is already a VBA function, you have to use a different variable
    DT = Application.WorksheetFunction.EoMonth(Date, "-1")
    ' > I have this set to "ThisWorkbook" as it's fewer things to worry about, but feel free to change this. _
                                                  What is LDay? \|/ you don't have this variable declared
    Set wb = ThisWorkbook 'workbooks("Run Report " & VBA.Format(LDay, "ddmmyyyy") & ".xlsb")
    
    ' > Avoid using activate
    wbrow3 = wb.Worksheets("DD").Cells(Rows.Count, "A").End(xlUp).Row
    
    ' > Check each file to see if they're from last month
    For Each oFile In oFolder.Files
        If oFile.Name Like "Report_" & Format(DT, "yyyymm") & "*" & ".xlsb" Then 'Report name with wildcard for day
            Set TargetWB = Workbooks.Open(oFile.Path)
            Exit For
        End If
    Next oFile
    
    ' > You now have the report book from last month open and saved to "TargetWB"

End Sub