Can someone please help me

[Katy Jordan]

hi, i am new to excel and VBA, and hope I can find some answers on this board

I have searched the forum regarding copying data from multiple worksheets into a main workbook, but I did not find anything that tells me how to copy data from multiple csv files into one workbook tab, I can see a thread created by Hiport which is very similar to what I want.
Basically I have a CSV file saved for each day of the month, these files are saved in their monthly folders, so all the CSV files for May will be saved in U:\Custodians\Interest\Year\Month(May2008), now in this folder will also be a master workbook called “Interest-May2008”.
What I do next is very manual, I have to copy from each CSV file, data from Col 1 and Col2 and paste this to the master workbook- sheet (downloads), all the data will be pasted next to each other, i.e. col 1 and 2 will be data for 01-May-08, and col 3 and 4 will have data for 02-May-08 etc, as you can see it’s a very daunting task having to go through each file and copy and paste.
I need a macro written so it copies data in col 1 and 2 in each csv file and then pastes that data in a master workbook sheet (downloads), the data will be in date order of the CSV files, I want the data pasted in row 3 of the master workbook (downloads).
I hope someone can solve my misery, you will save me 45 minutes of pain of copy and pasting.

 

[Katy Jordan]

also with the above i have the file saved as May2008 as this is the file I want to use, but i get an error message saying Interest-June-2008.xls is not found,

The file will always be saved current month -1, i.e May, and at the endo fJuly it will be June, how do i get around this problem so that the date recognises the month before the current month.

 

[Legacy 98055]

Ok. I should have been a bit more clear.

The headers, if any, I was referring to are the ones located in your CSV files. Also,I needed to see the structure of a typical CSV file...

Is this correct?

16/05/2008,CAETRN,AUDCUST,-300.05
16/05/2008,CBWTGH,AUDCUST,
16/05/2008,CIETRN,AUDCUST,

Are there headers? Is the data in quotes? Open the CSV in notepad to see the actual structure and then paste in your repy...

 

[Katy Jordan]

this is the layout of the CSV file, the data is in quotes in notepad but not excel

End Date, Pfolio, A /c Code, A/c Native at End,
16/05/08, CAETRN, AUDCUST 300.05,
16/05/08 CBWTRN, AUDCUST ,
16/05/08, CIETRN, AUDCUST , ,
16/05/08, CMWTRN, AUDCUST ,

Ok. I should have been a bit more clear.

The headers, if any, I was referring to are the ones located in your CSV files. Also,I needed to see the structure of a typical CSV file...

Is this correct?

16/05/2008,CAETRN,AUDCUST,-300.05
16/05/2008,CBWTGH,AUDCUST,
16/05/2008,CIETRN,AUDCUST,

Are there headers? Is the data in quotes? Open the CSV in notepad to see the actual structure and then paste in your repy...

 

[Katy Jordan]

ok, by chance i deleted this and then it works, but i still have the problem that the code does not recognise May, if I resave the file as June it works

i also want to copy column 2 and 4 of csv files instead of 1 and 2, where do I change that in the code

Set fso = Nothing : ws = Nothing

 

[Legacy 98055]

"it works"

I have another method in mind using ADO. If Jindon's code is working, I'll wait and see. In the mean time, your CSV does not appear to have uniform columns. Is this just a typo?

Should there be a comma between AUDCUST 300.05?

Five columns here? 16/05/08, CIETRN, AUDCUST , ,


Comma on end? A/c Native at End,

End Date, Pfolio, A /c Code, A/c Native at End,
16/05/08, CAETRN, AUDCUST 300.05,
16/05/08 CBWTRN, AUDCUST ,
16/05/08, CIETRN, AUDCUST , ,
16/05/08, CMWTRN, AUDCUST ,

 

[Katy Jordan]

the CSV file has five columns,

Headings are in Row 1 and data is in row 2 onwards

A1= End Date, A2 = P'folio, A3 = A/c Code, A4= A/c Native at End, A5= A/c Interest

Jindon code works but it only works if the file is saved as current month, whereas i need the code to recognise the file name for previous month, also how do i change the code so that the code copies Col 2 and Col 4 from the CSV file instead of Col 1 and 2

"it works"

I have another method in mind using ADO. If Jindon's code is working, I'll wait and see. In the mean time, your CSV does not appear to have uniform columns. Is this just a typo?

Should there be a comma between AUDCUST 300.05?

Five columns here? 16/05/08, CIETRN, AUDCUST , ,


Comma on end? A/c Native at End,

End Date, Pfolio, A /c Code, A/c Native at End,
16/05/08, CAETRN, AUDCUST 300.05,
16/05/08 CBWTRN, AUDCUST ,
16/05/08, CIETRN, AUDCUST , ,
16/05/08, CMWTRN, AUDCUST ,

 

[Legacy 98055]

Jindon will edit his own code...

Is this what your CSV file looks like in notepad?

"End Date", "P'folio", "Ac Code", "A/c Native at End", "A/c Interest"
"16/05/08", "CAETRN", "AUDCUST", "300.05", ""
"16/05/08", "CBWTRN", "AUDCUST" , "", ""
"16/05/08", "CIETRN", "AUDCUST" , "", ""
"16/05/08", "CMWTRN", "AUDCUST", "", ""

 

[Katy Jordan]

sort of, it doesnt have quotes around the headings or data, it just has a comma between the data

End Date, Pfolio, A/c Code, A/c Native at End, A/c Interest Rate

16/05/08, CAETRN, AUDCUST, 300.05, 0.13

Jindon will edit his own code...

Is this what your CSV file looks like in notepad?

"End Date", "P'folio", "Ac Code", "A/c Native at End", "A/c Interest"
"16/05/08", "CAETRN", "AUDCUST", "300.05", ""
"16/05/08", "CBWTRN", "AUDCUST" , "", ""
"16/05/08", "CIETRN", "AUDCUST" , "", ""
"16/05/08", "CMWTRN", "AUDCUST", "", ""

 

[Legacy 98055]

"...this is the layout of the CSV file, the data is in quotes in notepad but not excel..."

"sort of, it doesnt have quotes around the headings or data"


Which?

 

[Katy Jordan]

My data does not have quotes, in notepad, so what now, is that an issue?

"...this is the layout of the CSV file, the data is in quotes in notepad but not excel..."

"sort of, it doesnt have quotes around the headings or data"


Which?