I am not sure what error x 400 means. There are at least two run-time errors of 1004 that could occur. (1) If the csv file does not exist as explained and (2) when the sheet name already exists.

[vba]Sub exa()
Dim rngNames As Range, Cell As Range
Dim wksNew As Worksheet
Dim sConString As String
Dim fn As String

With ThisWorkbook.Worksheets("Sheet1")
Set rngNames = Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each Cell In rngNames
fn = ThisWorkbook.Path & "\" & Cell.Value & ".csv"
If Dir(fn) = Empty Or SheetExists(Cell.Value) Then GoTo NextCell
sConString = "TEXT;" & fn
Set wksNew = ThisWorkbook.Worksheets.Add( _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count), _
Type:=xlWorksheet)

With wksNew
With .QueryTables.Add(Connection:=sConString, Destination:=wksNew.Range("A1"))
.Name = Cell.Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Delete
End With
.Name = Cell.Value
End With
NextCell:
Next Cell
End Sub

Function SheetExists(sname) As Boolean
' Returns TRUE if sheet exists in the active workbook
If IsError(Evaluate(sname & "!A1")) Then SheetExists = False _
Else SheetExists = True
End Function[/vba]