Andrew,
Awhile ago I wrote this code for a task very similar to this. The First code calls the ImportDelimitedTestFiles code, which will prompt you to select one (or more) .txt and/or .csv files. Each file will be imported into its own worksheet and the sheet name will be renamed to match the file name. Is that something you can work with?
Sub tgr()
ImportDelimitedTextFiles ","
End Sub
Public Sub ImportDelimitedTextFiles(ByVal sOtherChar As String, _
Optional ByVal bConsecutiveDelimiter As Boolean = False, _
Optional ByVal lTextQualifier As XlTextQualifier = xlTextQualifierDoubleQuote)
Dim ws As Worksheet
Dim FSO As Object
Dim strText() As String
Dim strName As String
Dim i As Long, j As Long
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Clear
.Filters.Add "Text and CSV Files", "*.txt, *.csv"
.AllowMultiSelect = True
.Title = "Select Text Files to Import"
If .Show = False Then Exit Sub 'Pressed cancel
Set FSO = CreateObject("Scripting.FileSystemObject")
For i = 1 To .SelectedItems.Count
strText = Split(FSO.OpenTextFile(.SelectedItems(i)).ReadAll, vbNewLine)
strName = Replace(Mid(.SelectedItems(i), InStrRev(.SelectedItems(i), Application.PathSeparator) + Len(Application.PathSeparator)), ".txt", vbNullString)
For j = 1 To 7
strName = Replace(strName, Mid(":\/?*[]", j, 1), " ")
Next j
strName = Trim(Left(WorksheetFunction.Trim(strName), 31))
Select Case (Not Evaluate("IsRef('" & strName & "'!A1)"))
Case True: Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
ws.Name = strName
Case Else: Set ws = Sheets(strName)
ws.UsedRange.Clear
End Select
With ws.Range("A1").Resize(UBound(strText) - LBound(strText) + 1)
.Value = Application.Transpose(strText)
.TextToColumns .Cells, xlDelimited, lTextQualifier, bConsecutiveDelimiter, False, False, False, False, True, sOtherChar
End With
Erase strText
Set ws = Nothing
Next i
Set FSO = Nothing
End With
End Sub
Bookmarks