VBA/Excel/Access/Word/Excel/Text to Excel

Excel has built-in options to read files where fields are delimited by tabs, semicolons, commas, or spaces.

 
Sub Main()
    Workbooks.OpenText Filename:= "C:\sales.txt", Origin:=437, DataType:=xlDelimited, Other:=True, OtherChar:= "|", FieldInfo:=...
End Sub

Import a file

 
Option Explicit
Sub ImportDemo()
  Dim fname$
  Dim newworkb As Workbook
  fname = ThisWorkbook.Path + "\file.txt"
  Workbooks.OpenText Filename:=fname, _
    Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
    Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
    Other:=False, DecimalSeparator:=".", ThousandsSeparator:=" ", _
    FieldInfo:=Array(Array(1, 1), Array(2, 3), Array(3, 1), Array(4, 1))
  Set newworkb = ActiveWorkbook
  newworkb.Worksheets(1).Copy after:=ThisWorkbook.Worksheets(1)
  newworkb.Close False
End Sub

Opening Delimited Files Simplified

 
Sub TestOpenDelimitedFile() 
    Dim myWorkbook As Workbook 
    Dim vFields As Variant 
    vFields = Array(Array(3, xlMDYFormat)) 
    Set myWorkbook = OpenDelimitedFile("C:\tab delimited.txt", 2,xlTextQualifierNone, False, vbTab, vFields) 
    Set myWorkbook = Nothing 
End Sub 
Function OpenDelimitedFile(sFile As String, _ 
                           lStartRow As Long, _ 
                           TxtQualifier As XlTextQualifier, _ 
                           bConsecutiveDelimiter As Boolean, _ 
                           sDelimiter As String, _ 
                           Optional vFieldInfo As Variant) As Workbook 
    On Error GoTo ErrHandler 
    If IsMissing(vFieldInfo) Then 
        Application.Workbooks.OpenText _ 
            Filename:=sFile, _ 
            StartRow:=lStartRow, _ 
            DataType:=xlDelimited, _ 
            TextQualifier:=TxtQualifier, _ 
            ConsecutiveDelimiter:=bConsecutiveDelimiter, _ 
            Other:=True, _ 
            OtherChar:=sDelimiter 
    Else 
        Application.Workbooks.OpenText _ 
            Filename:=sFile, _ 
            StartRow:=lStartRow, _ 
            DataType:=xlDelimited, _ 
            TextQualifier:=TxtQualifier, _ 
            ConsecutiveDelimiter:=bConsecutiveDelimiter, _ 
            Other:=True, _ 
            OtherChar:=sDelimiter, _ 
            FieldInfo:=vFieldInfo 
    End If 
    Set OpenDelimitedFile = ActiveWorkbook 
ExitPoint: 
    Exit Function 
ErrHandler: 
    Set OpenDelimitedFile = Nothing 
    Resume ExitPoint 
End Function

Opening Fixed-Width Files

 
Sub TestOpenFixedWidthFile() 
    Dim myWorkbook As Workbook 
    Dim vFields As Variant 
    vFields = Array( _ 
        Array(0, xlGeneralFormat), _ 
        Array(7, xlGeneralFormat), _ 
        Array(21, xlMDYFormat), _ 
        Array(32, xlGeneralFormat), _ 
        Array(43, xlGeneralFormat)) 
    Set myWorkbook = OpenFixedWidthFile("C:\fixed width orders.txt", 1, vFields) 
    Set myWorkbook = Nothing 
End Sub 
Function OpenFixedWidthFile(sFile As String,lStartRow As Long,vFieldInfo As Variant) As Workbook 
    On Error GoTo ErrHandler 
    Application.Workbooks.OpenText _ 
        Filename:=sFile, _ 
        StartRow:=lStartRow, _ 
        DataType:=xlFixedWidth, _ 
        FieldInfo:=vFieldInfo 
    Set OpenFixedWidthFile = ActiveWorkbook 
ExitPoint: 
    Exit Function 
ErrHandler: 
    Set OpenFixedWidthFile = Nothing 
    Resume ExitPoint 
End Function