Excel macro question

[texasguy66]

Hello everyone,

I have been working on an excel macro but not much luck. There is a download folder on my local drive that has two files I need to copy to my report. The file names in the report are P&L and divisional. The file names in the download file vary (they include date in the name). How can I copy from the download file as a partial name match to an exact name match on the report.

File locations:

C:\Users\asmith\Desktop\Project1\report - file name is August 2023 Sales and has tab names P&L and divisional
C:\Users\asmith\Desktop\Project1\download - has file names 0823P&L.dat and divisionalaug23.dat

Thanks for any help you can provide.

 

[Bosquedeguate]

I think from your description you want to import the data that is in the mmyyP&L.dat file into a P&L tab (worksheet) in your Excel Workbook, and
import the data in divisionalmmmyy.dat file into your divisional tab (worksheet)?

Please confirm or clarify.
Also, provide the code you have tried.

What worksheet and cell contains the date that will drive which file is imported? Or are your prompting for a date in a UserForm?
And if a UserForm what triggers the display of the form?

 

[texasguy66]

Yes, you are correct on the first line of your response.

The download folder currently only has the two files in it. Is there a way to copy based on the most recent date?

 

[Bosquedeguate]

Yes, you are correct on the first line of your response.

The download folder currently only has the two files in it. Is there a way to copy based on the most recent date?

In addition to the filename, there are several dates connected to each file in a folder: creation date, date modified, date last written
You could search on one of these dates and selected the date that is largest.
If you do it by filename it is doable, but you should consider a file naming convention that would make the selection a bit easier. For example

the P&L files would be much easier to find the most recent file if the filename was yymmP&L.dat, and likewise the divisional file if it was named divisionalyymmm (e.g. divisional23AUG) or even better
divisional2308(AUG).

if one of the date values associated with a file does not provide the solution for you then you could do a file search based on the file name like search for all files that end with P&L.dat or begin with divisional?????.dat. Save each file in an array store with it the date part of the file name (converting it to a date). Once saved find the most recent date from the conversion and import that file.

I'm thinking the file creation date (above) might be your best option for finding the most recent file.