Copy and paste an excel file with variable name

Question

What I want to design is a file which can copy and paste a file in the active worksheet. However, this file will be updated daily and also change its name. Therefore, I'd like to refer to a dynamic name so to say.

Sub stackoverflow()

' copy workbook to new worksheet

    Workbooks.Open Filename:= _
    "/Documents/Documenten/movementreport202001075.xlsx"
    Cells.Select
    Selection.Copy
    Windows("test").Activate
    ActiveSheet.Paste

End Sub

For instance, the "20200107" can be "20200108.xlsx" tomorrow. So it is dependent on the date, time and seconds you download the report.

braX · Accepted Answer · 2020-01-07T09:36:55.167

0

You can use the format function to put it into the format you want:

Workbooks.Open Filename:= "/Documents/Documenten/" & Format$(Date, "d.m.yyyy") & ".xlsx"

_{Seems like you would want to use a drive letter too tho...}

Update:

It's the same concept (all you are doing is formatting the date and concatenating string values):

Workbooks.Open Filename:= "/Documents/Documenten/movementreport" & Format$(Date, "dmyyyy") & ".xlsx"

see how that works now?

_{I still say you would probably want to use a drive letter too tho...}

edited Jan 07 '20 at 09:36

answered Jan 07 '20 at 08:52

braX

11,506
5
20
33

Thanks! However, the name would be something more like: movementreport712020.xlsx.. I should have been more clear in my question. – BHKES Jan 07 '20 at 09:01
I just checked the file name and it it dependent on date, time and seconds. Is this is also possible to describe in a formula? Now() just provides the time right now and not the time the file is downloaded from the internet. Is there a method to look for the file name with the "newest" time? – BHKES Jan 07 '20 at 10:01
Yes, you can use a `FileSystemObject` to do that by looping thru all the files, but that is not in the scope of this question, and you keep changing the question. Create a new question after you close this one that asks how to do that after you do some research on how to loop thru a folder of files to find the most recent one and include your code in the question if you get stuck. – braX Jan 07 '20 at 10:07
https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba – braX Jan 07 '20 at 10:21

Copy and paste an excel file with variable name

1 Answers1