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.

19595
19596
19594
19597

[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