HELP: Copy Data from Multiple CSV files to an output xls file

Hi All,

Would like to seek your help on creating a Macro that will search for the filename listed under column A and copy values from that file (under Column C) to a new excel sheet as named under Column B.

Example:

These columns are under Index Workbook. All of the files under Column A are saved on one folder in desktop. So if the file opened from the folder is named as BOM Gen A English.csv or BOM Gen B English.csv, it copies the data on those sheets under Column C to a new workbook named as BOM Res1 English.xls and then adds the values of the column information pasted.

Column A Column B
BOM Gen A English.csv BOM Res1 English.xls
BOM Gen B English.csv BOM Res1 English.xls
BOM Gen A French.csv BOM Res1 French.xls
BOM Gen B French.csv BOM Res1 French.xls
CAN TA A Chinese.csv CAN TA Chinese.xls
CAN TA B Chinese.csv CAN TA Chinese.xls

I have attached the sample files needed.

Would really appreciate your help on this.

Thanks so much in advance, excel vba macro Gods.

Cheers,
Anata

Hi..

it copies the data on those sheets under Column C to a new workbook named as BOM Res1 English.xls and then adds the values of the column information pasted.

So you want all data in Column C of the Source File to be copied to the newly created sheet (name taken from Column B of the Index file)..?

If so.. where do you want the data copied to.. which column ?

Hi Apo,

Thank you for your reply. Output data to be copied to column A of the new worksheet.

Thanks,
Anata

Hi apo. Thank you for the codes. However it does not append the data to the bottom of the last value in column a, it overwrites the entire file instead. I think it is because the workbooks.add and save as overwrites the first saved file before the loop. Help pls?

mm.. changed some filepath values.. try this..


I get this:

In the resulting file.. Bom Res1English.csv.xls.... i get 154332 rows.. meaning that both the BOM General Enquiries English.csv & BOM New Reservations English.csv files have been added to a combined row total of 154332... is that not what you get when you run it??

Please Login or Register  to view this content.

Thank you so much for the codes, worked perfectly!