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

0 votes

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
Jan 20, 2023 in Others by Kithuzzz
• 38,010 points
472 views

1 answer to this question.

0 votes

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
answered Jan 20, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Copy data with filter applied using Excel VBA

Try this: Private Sub CommandButton1_Click() Dim ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,010 points
335 views
0 votes
1 answer

Excel: importing data from another Excel file using VBA

Refer to the file variables folderName and ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
520 views
0 votes
1 answer
0 votes
1 answer

Excel VBA if file closed, then open and paste, else just paste data

Slightly re-worked to add full workbook/sheet qualifiers ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,700 points
699 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
980 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,330 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
634 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
854 views
0 votes
1 answer

Name for excel graph problem with the vba generator

Change the name of the workbook : Sub Macro_name_graph2() ...READ MORE

answered Jan 7, 2023 in Others by narikkadan
• 63,700 points
552 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP