VBA code to merge and import Multiple CSVs

You have wbResult created in the wrong place

The following will import all the CSVs from 'FolderPath' into the active sheet of the new workbook 'wbResult' , omitting the header rows

Option Explicit

Sub CombineCsvs()

Dim FolderPath As String

Dim FileName As String

Dim wbResult As Workbook

Dim WB As Workbook

  FolderPath = "C:\testfolder\inputfiles"

  If FolderPath Like "*[!/]" Then

    FolderPath = FolderPath & "/"

  End If

  FileName = Dir(FolderPath & "*.csv")

  Set wbResult = Workbooks.Add

  Application.DisplayAlerts = False

  Application.ScreenUpdating = False

  Do While FileName <> vbNullString

    Set WB = Workbooks.Open(FolderPath & FileName)

    WB.ActiveSheet.Rows(1).EntireRow.Delete

    WB.ActiveSheet.UsedRange.Copy wbResult.ActiveSheet.UsedRange.Rows(wbResult.ActiveSheet.UsedRange.Rows.Count).Offset(1).Resize(1)

    WB.Close False

    FileName = Dir()

  Loop

  wbResult.ActiveSheet.Rows(1).EntireRow.Delete

  Application.ScreenUpdating = True

  Application.DisplayAlerts = True

End Sub

Hey @Andreas Killer, thanks for sharing that.

I haven't really worked on power query before but this is something I really wanted to learn. Thanks for sharing this, I will take a look at it and try to replicate it. :)

Move the workbook creation out of the loop i.e.

Set wbResult = Workbooks.Add

    FileName = Dir(FolderPath & "*.csv")

Thanks a lot for the quick response.

I tried running it but it is not copying the data but rather just creating a blank new workbook:

Option Explicit

Sub CombineCsvs()

Dim FolderPath As String

Dim FileName As String

Dim wbResult As Workbook

Dim WB As Workbook

  FolderPath = "C:\testfolder\inputfiles"

  If FolderPath Like "*[!/]" Then

    FolderPath = FolderPath & "/"

  End If

  Application.DisplayAlerts = False

  Application.ScreenUpdating = False

  Do While FileName <> vbNullString

    Set WB = Workbooks.Open(FolderPath & FileName)

    WB.ActiveSheet.UsedRange.Copy wbResult.ActiveSheet.UsedRange.Rows(wbResult.ActiveSheet.UsedRange.Rows.Count).Offset(1).Resize(1)

    WB.Close False

    FileName = Dir()

  Loop

 FileName = Dir(FolderPath & "*.csv")

  Set wbResult = Workbooks.Add

  wbResult.ActiveSheet.Rows(1).EntireRow.Delete

  Application.ScreenUpdating = True

  Application.DisplayAlerts = True

End Sub

Move the workbook creation out of the loop i.e.

Set wbResult = Workbooks.Add

    FileName = Dir(FolderPath & "*.csv")

Watch the video below.

Andreas.

Excel Power Query #04: Import Multiple CSV Files In 1 Step & Retrieve New Files Automatically - YouTube