Desperately seeking a piece of VBA to do the following (tried looking for the answer but don't understand VBA so not sure if any similar examples found on this site would do what I need).
On a weekly basis, 5 users ftp a CSV file to a central server directory. Each CSV file contains 6 columns of data, with the same column headings, and in the same order. I would like to create an Excel workbook to house the contents of these 5 csv files. On a weekly basis, I want to update the workbook with the contents of the latest 5 csv files, without duplicating data. I want to run a macro which does the following:
- Looks for 5 uniquely named csv files (doesn't need to error if it doesn't find them)
- Opens each file and loops through each row to check if COLs A, B, C, D, E & F on the csv file exactly match an existing row (cols A-F) in the spreadsheet
- If they match, update col G only with the current date
- If they don't match, append the row to the end of the spreadsheet, and add the (current) date to col G
- Do a save of the workbook (and delete the csv file as well if possible).
So, an example just to be clear:
MyWorkbook.xls looks for File1.csv, opens it and loops through all its rows. For each row that matches cols A-F already in MyWorkbook.xls, it simply updates col G with the current date, for data it doesn't already find, it adds the data as a new row to MyWorkbook.xls and puts the current date in col G. Loops through to the end of file, then closes File1.csv. Looks for File2.csv and repeats the above. Looks for File3.csv, doesn't find it (the user hasn't sent it this week), ignores it and goes on to look for File4.csv. After it has finished looking for (and processing) all 5 specified csv files, does a save of MyWorkbook.xls. If it could also delete each csv file once processed, that would be a bonus, but is not essential.
I'm sure it's easy, but haven't got a clue how to achieve it!! Could someone provide me with a suitable piece of VBA? Also, please include comments to help me understand what it's doing, so that I may (hopefully) be able to edit it in the future if needs be.
Start Free Trial
