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.

   <source lang="vb">

Sub Main()

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

End Sub

</source>
   
  

Import a file

   <source lang="vb">

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

</source>
   
  

Opening Delimited Files Simplified

   <source lang="vb">

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

</source>
   
  

Opening Fixed-Width Files

   <source lang="vb">

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

</source>