Update Master file from year-long CSV's and summarise per month data in place of Pivot

Hello. I have searched across this forum for an exact answer but find many other examples which don't quite fit.

I'm on a two-year research project in college, looking into continuous (minute-interval) heat pump and room temperature measurements.
My objective is to append (new) timestamped data to a Master .xlsx workbook from a freshly downloaded .csv file and also, to summarise the data, instead of using (the wonderful) Pivot table after each download. This is to be done for 20 different Master files (from 20 different csv files), at least once per month.


Each downloaded .csv defaults to a full year of minute-interval data (around 500,000 rows is typical due to missing data) so I only need to copy the most recent data, to the corresponding Master file (see attached files as an explanation). The Master and csv files both contain 8 columns (A to H) of data.

Master File data example.JPG

While the csv file corresponding to each Master file will be unique, each downloaded csv file is always given the same name: CSVReport.csv

For ease, the folder and filename structure simply follows an XHyy identifier number (where yy is the two-digit number 01, 02, 03,..., up to 20), for example, the Master file for XH01 is found in:

P:\Data Analysis\1.1 Data Management\Master Files\XH01\XH01 Master 12Dec17.xlsx (note: the 12Dec17 is the date that the most recent csv file was downloaded)

and the corresponding CSVReport file is held in:
P:\Data Analysis\1.1 Data Management\Master Files\XH01\XH01 csv\CSVReport.csv

The order of events is:
1. The Master file will be open e.g. XH01 Master 12Dec17.xlsx
2. The corresponding CSVReport.csv file will be in the corresponding XHyy folder, as in the above directory path/filename
3. Run the code so that only the most recent data from the csv file is copied/appended to the XHyy Master file.
4. A monthly summary of the data, consisting of two SUM columns, four AVERAGE columns and one COUNT column (see Sheet2 of attached XH01 Master 12Dec17.xlsx)

I hope I have explained (and not over-explained) concisely, and thanks to all who spend time on this.

Hi Geoff,

I don't have all the answers to your query, but here's a start for you:

Please Login or Register  to view this content.

Thanks Chriz, sorry for late reply. Do I need to declare curworksheet i.e Dim curworksheet as Worksheet?
I ran the code on the train home earlier but my laptop completely closed down after Excel not responding sign for a few minutes.
I had both XH01 Master 12Dec17.xlsx and CSVReport.csv open at the time. Is that the way to run the code? Both files
must be open, or do they need to be in the same directory, or something? Please excuse my ignorance on this.
Also,should maxdate be MaxDate, from a quick Google...

Any idea why laptop crashed?
Still, thanks for your input which may well work, being an error on my side!

Hi again. It worked!
I added the Dim curworksheet As Worksheet and it has worked perfectly. I tried F8 (Debug) to step through and got an error message
after Set curworksheet = Worksheets("XH01 Sheet1"), so thank you very much for the code, and expanding my confidence by getting used to Debug.

I will leave this thread Unsolved until later tomorrow, but will mark it Solved if no other replies about the monthly summaries occur, then re-post my second query
after some browsing (for the answer).

Really appreciate this, thank you.

Hi Geoff,

Ah, yes my bad, I am by no means a VBA master, I have been learning using this forum like yourself. Yes, should have declared the curworksheet variable.

Glad it's a start for you.

All the best.

Chriz