VBA Copying files from one location to another

[ddbroadhead92]

Afternoon all,

I am using the below code (along with some formulas and a UDF) to transfer the latest file in a directory to another location.

The UDF & Formulas compare the latest filename that was uploaded to the latest file in the source location, the names could be something like this:

Last Upload - Upload_Data123.csv
Latest file in source location: Upload_Data124.csv

So the macro will transfer Upload_Data124.csv into the desired folder.

However, sometimes the situation could be this:

Last Upload - Upload_Data123.csv
Latest file in source location: Upload_Data125.csv

I need the code to copy all files GREATER than the last upload file across, so it would copy
Upload_Data124.csv & Upload_Data125.csv

Here is the code, any help would be appreciated:

Please Login or Register  to view this content.

Thanks
Dan

[Stormin']

Hi Dan,

That code seems to just copy every path that is in the Insurer List. I can't see any comparison between file names.

Can you check this is the right code and attach a sample workbook?

[ddbroadhead92]

I have protected the data so i have had to remove some of the formulas but i assure you it searches for a specific file.

Attached is the example workbook

[ddbroadhead92]

Still struggling with this, so just a friendly bump if anyone has any thoughts?

[Stormin']

Hi Dan,

Since your bump I have been working on this in little bits of spare time I can find during the day. Hang in there

[Stormin']

Hi Dan,

I finished this off today on my lunch break. Lots of fun

Due to the different styles of file counters you'll need to add a few columns to your table which tells the code where to look for the counter (and if it is a date).
Additionally, the code ignores your "Transfer Required?" column since the formula used was unreliable, and by finding the difference between the file name counters we can infer whether a transfer is required or not.

The code works with all the file name styles in your sample workbook and works if there are gaps in the incrementation, e.g. if Data123.csv is the last import and Data130.csv is the latest source file, but only Data123.csv, Data125.csv, Data129.csv, and Data130.csv exist, it will import those files that exist and skip the gaps. This is more likely to happen with the date counters of course.
The code will print the transfer details to the console for information purposes. Example:

Please Login or Register  to view this content.

Find my code below. You will need the functions after the main sub, too. Also included at the end are the subs I used to create files / folders to test the code (NB that I set all file extensions to .txt for testing purposes).

Please Login or Register  to view this content.

Also attached is the workbook that I used to create / test this.

Enjoy!

[Stormin']

Hi Dan,

Have you tried this out? Did it work? Let me know

---

If that takes care of your original question, please select Thread Tools from the menu link above to mark this thread as SOLVED.
To say thanks to the user(s) who contributed towards the solution, you can use the "Add Reputation" button on their helpful post(s).
Thanks!