I have created this code may this will help you out.
Code:
Sub OpenCSV()
Dim i As Integer
' change this next line to reflect the actual directory
Const strDir = "c:\csv files\"
Dim ThisWB As Workbook
Dim wb As Workbook
Dim ws As Worksheet
Dim strWS As String
Set ThisWB = ActiveWorkbook
Set fs = Application.FileSearch
With fs
.LookIn = strDir
.Filename = "*.csv"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(i))
strWS = wb.Sheets(1).Name
wb.Sheets(1).UsedRange.Copy (ThisWB.Worksheets(strWS).Range("A2"))
wb.Close False
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
Bookmarks