try this code.
I have used this to import files and add a sheet with the same name as the file but without extn or imported into existing sheet.
There's no error checking and it overwrites content but it might get you started.
'Display Open Dialog to select csv location.
filenames = Application.GetOpenFilename("Excel Files (*.csv*)," & _
"*.csv*", 1, "Select CSV files", "Open", False)
'If the user cancels file selection then exit
If TypeName(filenames) = "Boolean" Then
'Set csv as SampleFile
FileCSV = Dir("*.csv")
'Cycle through the directory
Do While FileCSV <> ""
'Remove extension for sheetname, add sheet and rename
CSVNoext = (Left(FileCSV, Len(FileCSV) - 4))
'To import csvs into active WB adding new sheet same name as file without extn use '1'
'To import into existing sheets with same name no extn use '2'
'ThisWorkbook.Sheets.Add - 1
'ActiveSheet.Name = CSVNoext - 1
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Worksheets(CSVNoext).Activate '- 2
FileCSV = Dir