muliti cvs files into excel worksheet

[idnoidno]

I want to copy multi cvs files into excel worksheet, and if there is only one CVS file, the title of the CVS data is the title of the EXCEL worksheet. If more than one CVS file, the title of the second CVS will not be copied, and the data will be attached to the back of the first data.

TEST_20170614_C_4395_2.csv
TEST_20170614_N_6469_2.csv
master.xlsm
TEST_20170614_S_5083_2.csv

[mana]

Please try this

Option Explicit


Sub test()
    Dim p As String
    Dim f As String
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim r As Range
    
    p = "C:\***\****\test\"
    
    f = Dir(p & "*.csv")
    If f = "" Then Exit Sub
    Set ws = Workbooks.Open(p & f).Sheets(1)
    
    f = Dir()
    Do While f <> ""
       Set wb = Workbooks.Open(p & f)
        Set r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        wb.Sheets(1).Cells(1).CurrentRegion.Offset(1).Copy Destination:=r
        wb.Close
        f = Dir()
    Loop
    
    Application.Goto ws.Cells(1)
    
End Sub

[snb]

Sub M_snb()
  sn=split(createobject("wscript.shell").exec("cmd \c Dir G:\OF\Test_2017*.csv /b").stdout.readall,vbcrlf)

  for j=0 to ubound(sn)-1
    thisworkbook.sheets.add , thisworkbook.sheets(thisworkbook.sheets.count),,sn(j)
  next
End Sub

or

Sub M_snb()
  shell "cmd copy G:\OF\test_2017*.csv G:\all_csv"

  do 
    doevents
  loop until dir("G:\all.csv")<>""

  do 
    doevents
  loop until filelen("G:\all.csv")>0
    
  workbooks.open G:\all_csv"
End Sub