Macro to copy 2 columns from .csvs to one sheet in another file

[meetcha]

Hello everyone,

I have quite an amount of .csv files in one folder which I want to merge. The .csvs contain 2 columns of data which I want to put in one workbook.

Does someone have an idea on how to automatize this?

Best and thanks upfront for the support
Micha

[alansidman]

Do you want all the data in two columns on one sheet in your workbook? or do you wish for each .csv file to be on a separate sheet in the new workbook? Are there any files in the folder that will not be copied to the new file? Is the new file also resident in the folder? Are the columns to be copied all in the same location? ie. Columns A & B?

[meetcha]

Yes in one sheet. No, there are only the csv I want to be in the sheet. Yes its also resident in the folder. Yes they are all in A and B.

Best
Micha

[meetcha]

Also, the names of the .csv are according to a list of search queries which are already in an excel file, if that helps.

Cheers
Micha

[alansidman]

This link should help you

http://www.rondebruin.nl/win/s3/win021.htm

[meetcha]

Hey Alan, thanks for the link. The problem with the copy *.csv all.txt command is that it puts all the data beneath the previous one, so not next to each other. Is there a command to do excactly that?

Best
Micha

[alansidman]

I don't understand what you mean.

[meetcha]

Ok sorry, let me elaborate.

My Data in the .csv looks like this

A B
search query xxx
date value
xxx yyyy
xxx yyyy
xxx yyyy
xxx yyyy

When I use the copy *.csv all.txt command the created csv looks like this

A B
search query xxx
date value
xxx yyyy
xxx yyyy
xxx yyyy
xxx yyyy


search query zzzz
date value
xxx zzzz
xxx zzzz
xxx zzzz
xxx zzzz

but I want it to be like this

A B C D
search query xxx search query zzzz
date value date value
xxx yyyy xxx zzzzz
xxx yyyy xxx zzzzz
xxx yyyy xxx zzzzz
xxx yyyy xxx zzzzz

Hope this makes it clearer.

Best
Micha

[alansidman]

I have to look at the code explicitly and see what Ron is doing. I don't think that you can do it with that code. You will need to build a loop to loop through each file and copy and then paste. I don't have time right now to develop that for you, but will look at it in the next day or so as time permits. In the mean time, hopefully, someone else will jump in here. I have done something similar to this with .xls? files but I believe it is slightly different for .csv files. Here is a starting point if you want to try yourself.

http://windowssecrets.com/forums/sho...o-master-sheet

[Docmarti]

but I want it to be like this

A B C D
search query xxx search query zzzz
date value date value
xxx yyyy xxx zzzzz
xxx yyyy xxx zzzzz
xxx yyyy xxx zzzzz
xxx yyyy xxx zzzzz

Here is a way of doing it.

Cheers

Docmarti

[meetcha]

Hey Doc,

thanks a lot, much appreciated! Unfortunately the macro does not seem to work, nothing happens. Do I have to modify something to fit my system? I already tried changing the path according to where the *.csv are located on my computer but that did not help.

Best
Micha

[Docmarti]

Hey Doc,

thanks a lot, much appreciated! Unfortunately the macro does not seem to work, nothing happens. Do I have to modify something to fit my system? I already tried changing the path according to where the *.csv are located on my computer but that did not help.

Edit:
You must modify the following line according to your own directory path

Please Login or Register  to view this content.

Docmarti

[meetcha]

Hi Doc,

already tried, unfortunately nothing happens.

Best
Micha

[meetcha]

Hi Doc,

already tried, unfortunately nothing happens.

Best
Micha

[Docmarti]

Hi Micha

The code for my path is :

Please Login or Register  to view this content.

What is yours ?


Docmarti

[meetcha]

Hi Doc,

thanks for the ongoing support. Mine is

 ThePath = "C:\Users\M\Desktop\New folder\csv us Harvard"  

Best
Micha

[Docmarti]

Hi Micha

There must be a "\" at the end of your path. Sorry.

ThePath = "C:\Users\M\Desktop\New folder\csv us Harvard\"

Docmarti

[meetcha]

Uff, sorry my fault! Now it works like a charm! Thank you so much man, you saved me a lot of time! Much appreciated!

Hows the time travelling going btw?

[Docmarti]

Just fine.
I knew you were going to like that code.