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

Question

I'm using the code below to copy data from another file without opening it, then I paste the data onto sheet PASTEHERE in my workbook.
How can I use this code if the target file name changes? I want to choose the file based on a text field that is consistently 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

Answer 1

To look for the source file, use the Dir() function. Be aware that this will only select the first match it finds; if you have more than one file, you would need to specify which is the "right" one to choose.

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