VBA copy data from other file - problem with file name change-CodePudding

I am using below code to copy data from other file without opening, then paste data to my workbook to sheet PASTEHERE.
How can I use this code if the target file name is changing - I want to select that file by text contain which is always stable: "RB".

Sub DownloadRA()
    Dim rgTarget As Range
    Set rgTarget = ActiveWorkbook.Sheets("PASTEHERE").Range("B23:EA6000") 'destination file .
    rgTarget.FormulaArray = "='D:\2023 MOJE pliki tygodniowe\kopie zapasowe\[2023.W01 RB File 03012023.xlsx]Sheet1'!$B$23:$EA$6000"
    rgTarget.Formula = rgTarget.Value
End Sub

CodePudding user response：

You can use Dir() to check for the source file. Note this will only pick the first match it finds - if you might have >1 file you would need to explain how to select the "correct" one...

Sub DownloadRA()
    Const FLDR As String = "D:\2023 MOJE pliki tygodniowe\kopie zapasowe\"
    Dim rgTarget As Range, f
    Set rgTarget = ActiveWorkbook.Sheets("PASTEHERE").Range("B23:EA6000") 'destination file .
    
    f = Dir(FLDR & "*RB*.xlsx")
    If Len(f) > 0 Then
        rgTarget.FormulaArray = "='" & FLDR & "\[" & f & "]Sheet1'!" & rgTarget.Address(True, True)
        rgTarget.Formula = rgTarget.Value
    Else
        MsgBox "No source file detected"
    End If
End Sub

CodePudding user response：

error after using above code @Tim Williams