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 in Others by Kithuzzz
• 27,740 points
33 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 by narikkadan
• 51,240 points

Related Questions In Others

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
• 51,240 points
155 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,240 points
183 views
0 votes
1 answer

VBA Code to Populate Balance Sheet from Trial Balance Data

It appears that what you need to ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 51,240 points
188 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
522 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
2,382 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
• 22,940 points
110 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,640 points
274 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 in Others by narikkadan
• 51,240 points
45 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