Opening Fixed-Width Files : Text to Excel « Excel « VBA / Excel / Access / Word






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 

 








Related examples in the same category

1.Excel has built-in options to read files where fields are delimited by tabs, semicolons, commas, or spaces.
2.Import a file
3.Opening Delimited Files Simplified