Pull data from csv files added into a network folder

Hi everyone,

Trawled the internet for an answer to this but cant seem to find the solution, hopefully one fo you guys can help me.

Scenario; I have 3rd party software that pulls reports in .csv files. I want to summarise this data into another spread sheet. Ideally I would like to save these files into a folder on the network and my summary workbook simply looks for this folder and is able to find data within all Excel files saved in there (new files will be added on a monthly basis).

Once it has found the folder there will be 2 pieces of data to evaluate;
Cell $B$1; this will be the name of a course (e.g. health & safety, money laundering etc.)
Column $D; this will contain the score achieved by multiple learners.

The summary spreadsheet then averages all of the scores for the different courses(in seperate cells)

The idea is that the .csv files can be pulled and saved into said folder and eliminates the need to manually input this data into the summary spreadsheet.

I hope this is doable and if so someone can help me along with this.

hi aaron_burr, can you upload a couple of CSV-files and what the result should look like?

UK non-core report.xlsxUK Core report.xlsxSummary sheet.xlsx

Hi Watersev, thanks for the reply.

Ive attached 2 reports and the summary sheet. Probably worth mentioning that they dont have to be .csv files as I initially thought.

The summary is relatively straight forward and only needs to average the D column on each report in the corresponding cell on the summary sheet.

This is well beyond me so apologies for the lack of any suggestions/ ideas from myself

Thanks

please check attachment, press Run button or run code "test"(ALT+F8, test, Run)

This is perfect, thanks for your help Watersev, much appreciated.

Hi,

Would you be able to help me amend this code to report on other data, i have tried to play around with it myself to no avail.

Essentially it tis the same as the above but rather than Averaging A1:A i would like the macro to COUNTIF the word cancelled in B1:B (these cells will contain other data and not just the word cancelled).

The formula =COUNTIF(B7:B1000,"=*CANCELLED*") works fine in Excel but I cant get it to translate into the code supplied in the last post.

try this out:

Please Login or Register  to view this content.

Perfect again, think I need to get myself on a VBA course as Excel formulas clearly dont translate exactly into VBA.