How to import multiple csv files in one excel spread sheet

Question

Hi,

I have hundreds of csv files that contain data which i need to have in one excel sheet. I know I can insert one at a time but i am looking for an easier way. 

Details:

1. each csv file has two columns of data
2. i only need the data from the second column, the first column in each csv is identical in all files 
3. all data should be in one file in one sheet
4. all csv files have different names and are placed in one folder

I would greatly appreciate the help.

Thanks you

Answer 1

That would happen if Excel was opening the files as text rather than recognising them as CSV files.

What is your regional list separator?  Is it different from the separator in the CSV file?

The code worked perfectly for me (with comma in both cases).

Replace

    Set WB = Workbooks.Open(stPath & stFile, ReadOnly:=True)

with

    Workbooks.OpenText stPath & stFile, dataType:=xlDelimited, comma:=True

    Set WB = ActiveWorkbook

Answer 2

I'm still not getting any results, I must be doing something wrong...

I recorded what I am doing, maybe you can spot the mistake.

https://youtu.be/x-A0u7Om3EI

Answer 3

Just noticed that you omitted the final \ on the path,

That would explain the failure to find any 500*.csv files in the ATPase folder

Stick a \ after 500 and try again.

Sorry I didn't notice that before.