Import multiple csv files in one excel spread sheet - include file name in the first row

Question

Hi,

I am totally new to using VBA in Excel and need your help. Thanks in advance!

I have a folder with multiple .csv files which I need to merge into one excel worksheet.

The file details are as follows:

1. Each file contains two columns of data, the first column is identical in all .csv files. I only need the data from the second columns.
2. all data should be in one data sheet.
3. above each column of data (corresponding to second columns in starting .csv files) should be placed the file name from which that data originates.
4. all starting .csv files are in one folder

Here is the illustration what I expect to achieve:

I found a macros on this website (see below) which almost does what I want, except for putting the names of the starting files in the row 1. What do I need to add it that it also records the file names in the column? Also, when there are multiple files in the folder, which order does the VBA follow in picking .csv files: is it based on the file name or the data it has been created? Huge thanks in advance!

I would greatly appreciate your help,

Kind regards,

Sub GetFromCSVs()

  Dim WB As Workbook

  Dim R As Range

  Dim bFirst As Boolean

  Dim stFile As String

  Dim stPath As String

  stPath = "C:\Temp\CSVs" ' change the path to suit

  stFile = Dir(stPath & "*.csv")

  bFirst = True

  Set R = Workbooks.Add(xlWorksheet).Sheets(1).Range("A1")

  Do Until stFile = ""

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

    If bFirst Then

      WB.Sheets(1).Range("A1").CurrentRegion.Copy Destination:=R

      Set R = R.Offset(, 2)

      bFirst = False

    Else

      WB.Sheets(1).Range("A1").CurrentRegion.Columns(2).Copy Destination:=R

      Set R = R.Offset(, 1)

    End If

    WB.Close saveChanges:=False

    stFile = Dir()  ' next file

  Loop

End Sub

Answer 1

Thanks Hans!

It did not work, but started working when I deleted " stPath = "C:\Users\gebruiker\Documents\Excel\ " !!!

Last question: what is the order of execution of .csv files? Is it based on name or creation date?

Very huge thanks!

D.

Answer 2

Ah sorry - I had to insert that line to make it work on my PC...

The files are processed ordered by name.

Answer 3

Huge thanks anyways! Utmost useful!

Answer 4

Any ideas what is wrong with the script above?

Why should there be something wrong? The video clearly shows how to do it.

Maybe you should try only 3 CSV files first. Then you can copy 5000 files into that folder and the same query imports them all...

...if there is enough memory available, but that limit is the same for VBA.

If you like, zip 3 CSV files, upload it and post the download link here. I show you a sample.

Andreas.