Opening Delimited Files Simplified : Text to Excel « Excel « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Excel
3. Text to Excel

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 

 

Related examples in the same category