Hamond
02-02-2008, 03:19 PM
Hi,
Sorry, this is a bit of a length one but I guess too much information is better than less!
I have an excel worksheet that I update regulary with latest values from downloaded CSV files. Right now, other than a couple of basic recorded macros to clean up the source data CSV files, most of this process is manual and I'l looking to automate it. I'm aware there is quite a bit of code posted on consolidating data but I've not seen anything that will work for me.
Here's my setup.
1) CSV Data Source Files: Have about 15 of these in the same folder. Each file has two columns, first is a date column and the second is values associated with that date. The length/no of rows of the data varies between files and not all files contain matching dates. All dates will have a value associated with it, there are no nulls/blanks.
2) Excel worksheet. This is where I currently update/consolidate the data from the source CSV files. Basicly, column A contains the dates and each successive column holds data relating to a particulary CSV files. When the latest data becomes avaliable I enter the entries in new rows for each variable. I do not erase or overwrite any of the old data, i.e I'm effectively building a timeseries. The actual data for each series starts in row 4. Where there is no value for a series/column for a given date, it is left blank.
The third row of the sheet contains codes relating to the source files. For example b3 = NBU1, this is the same as the corresponding file in the folder - the folder will have a file called NBU1.csv.
So basicly I'm looking for some code that will open each file in the folder, locate the relevant column in the spreadsheet based on the codes in row 3 and then copy over the latest data via I guess mapping the dates in the CSV files and to the dates in column A of the spreadsheet.
Hope someone can help.
Many Thanks,
Hamond
Sorry, this is a bit of a length one but I guess too much information is better than less!
I have an excel worksheet that I update regulary with latest values from downloaded CSV files. Right now, other than a couple of basic recorded macros to clean up the source data CSV files, most of this process is manual and I'l looking to automate it. I'm aware there is quite a bit of code posted on consolidating data but I've not seen anything that will work for me.
Here's my setup.
1) CSV Data Source Files: Have about 15 of these in the same folder. Each file has two columns, first is a date column and the second is values associated with that date. The length/no of rows of the data varies between files and not all files contain matching dates. All dates will have a value associated with it, there are no nulls/blanks.
2) Excel worksheet. This is where I currently update/consolidate the data from the source CSV files. Basicly, column A contains the dates and each successive column holds data relating to a particulary CSV files. When the latest data becomes avaliable I enter the entries in new rows for each variable. I do not erase or overwrite any of the old data, i.e I'm effectively building a timeseries. The actual data for each series starts in row 4. Where there is no value for a series/column for a given date, it is left blank.
The third row of the sheet contains codes relating to the source files. For example b3 = NBU1, this is the same as the corresponding file in the folder - the folder will have a file called NBU1.csv.
So basicly I'm looking for some code that will open each file in the folder, locate the relevant column in the spreadsheet based on the codes in row 3 and then copy over the latest data via I guess mapping the dates in the CSV files and to the dates in column A of the spreadsheet.
Hope someone can help.
Many Thanks,
Hamond