combining csv workbooks to single sheet - vba works but need help adjusting

[rikvny02]

The below code works exactly as expected. I need help making an adjustment that would not copy the first row in each workbook. Any help is grateful.

Sub CombineCSVSheets()

Dim FolderPath As String
Dim FileName As String
Dim wb As Workbook
Dim ws As Worksheet
Dim wbCombined As Workbook
Dim wsCombined As Worksheet
Dim CurrentRow As Long
Dim rngPaste As Range

'ActiveWorkbook.save

Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
FolderName = sItem
Set fldr = Nothing
FolderPath = FolderName & "\"

' Create a new workbook to combine the CSV sheets
On Error Resume Next
Kill (FolderPath & "BATCH 1.xlsx")
On Error GoTo 0
Set wbCombined = Workbooks.Add
With wbCombined
Set wsCombined = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
.SaveAs FolderPath & "BATCH 1.xlsx"
End With

wsCombined.Name = "STATION STAFFING"
Range("a1:k1").Value = Array("TC", "CO", "EMPLOYEE", "POSITION TO", "PT AUTHORIZATION", "FC", "COPY NUMBER", "DATE", "ATTENDANCE", "DESC", "HOURS")

' Retrieve the first CSV file in the folder.
FileName = Dir(FolderPath & "*.csv")

' Loop through all CSV files in the folder
Do While FileName <> ""

With wsCombined
If Len(Trim(.Range("B1"))) = 0 Then
Set rngPaste = .Range("A1")
Else
Set rngPaste = wsCombined.Range("B" & wsCombined.Cells(wsCombined.Rows.Count, 2).End(xlUp).Row + 1).Offset(0, -1)
End If
End With

With wsCombined.QueryTables.Add(Connection:="TEXT;" & FolderPath & FileName, Destination:=rngPaste)
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With

' Retrieve the next CSV file in the folder.
FileName = Dir

Loop

' Save and close the combined workbook.
'wbCombined.Close SaveChanges:=TRUE

MsgBox "Finished."

End Sub

 

[Alex Blakenburg]

Try adding the line in Blue to your code:

With wsCombined.QueryTables.Add(Connection:="TEXT;" & FolderPath & Filename, Destination:=rngPaste)
    .TextFileParseType = xlDelimited
    .TextFileStartRow = 2
    .TextFileCommaDelimiter = True
    .Refresh
End With

 

[rikvny02]

Try adding the line in Blue to your code:

With wsCombined.QueryTables.Add(Connection:="TEXT;" & FolderPath & Filename, Destination:=rngPaste)
    .TextFileParseType = xlDelimited
    .TextFileStartRow = 2
    .TextFileCommaDelimiter = True
    .Refresh
End With

Thanks you. works great

 

[Automatrix]

Power Query alternative: Get Data --> From File --> From Folder.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.