Listing Opening Fixed Width Files Simplified

Sub TestOpenFixedWidthFileO Dim wb As Workbook Dim vFields As Variant

The third column of the orders file is a date column (MM/DD/YYYY). The rest are general (default)

Array(0, xlGeneralFormat), _ Array(7, xlGeneralFormat), _ Array(21, xlMDYFormat), _ Array(32, xlGeneralFormat), _ Array(43, xlGeneralFormat))

Set wb = OpenFixedWidthFile( _

"C:\fixed width orders.txt", 1, vFields)

Set wb = 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

The OpenFixedWidthFile procedure is smaller than OpenDelimitedFile because OpenFixedWidthFile doesn't have an optional parameter. Although OpenFixedWidthFile is smaller and has fewer parameters than OpenDelimitedFile, it is definitely more tedious to call it. This is because you need to set up the vFieldInfo parameter. In order to set it up, you need to know where each column of data is located in the source file. For example, in my sample data file (Figure 15.2), the first column of data begins at zero and the second column of data begins with the seventh character. Obviously, this isn't the kind of thing you can determine dynamically—you need to know what the specifications are or have a sample of the text file at development time. Figure 15.12 shows the result of the OpenFixedWidthFile on my sample data file.

0 0

Post a comment