I am having to convert several files of data from an old DOS program and
read them into Excel.
Each file has between 10,000 and 12,000 records of 229 bytes
The records are all contiguous data within the files, with Chr(255)+Chr(255)
determining the start of each new record.
With Excel 2007, I was able to easily use DataText to Columns, setting
Chr(255) as the delimiter and each record of 227 bytes (without the
Chr(255)'s) was created in a separate column on row 1 of the file.
A simple CopyPaste SpecialTranspose allowed me to turn this into 10,139
rows (for the first file) each with 227 characters in column A.

Each of these 227 byte records, is made up of 67 fields of varying lengths.
These I have listed on another sheet with the length of each field and it's
starting position.

Whilst the short piece of code shown below does work and extracts all of the
data into the relevant columns for me, I was wondering whether there was any
faster way of effecting the conversion.
I am using Vista SP1 and XL2007 SP1

Sub CreateRecords()

Dim wss As Worksheet, wsd As Worksheet, wst As Worksheet
Dim i As Long, j As Long, lr As Long, start As Long, length As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wss = ThisWorkbook.Sheets("OldData")
Set wsd = ThisWorkbook.Sheets("NewData")
Set wst = ThisWorkbook.Sheets("Definition")

lr = wss.Cells(Rows.Count, "A").End(xlUp).Row

For j = 2 To lr
' row 1 on Source is blank, row 1 on Destination is a Header row
For i = 1 To 67
start = wst.Cells(i, 3).Value: length = wst.Cells(i, 2).Value
wsd.Cells(j, i) = Mid(wss.Cells(j, 1), start, length)
Next i
Next j
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--

Regards
Roger Govier