Ahhh.

I thought you might have used the data on that other sheet to create the code
that you needed. Or even used that data to create the array you needed.

Roger Govier wrote:

It wasn't a case of trusting Excel to get the parsing correct.
It was just letting it create a macro, so that I could see the form it took
for specifying the layout.

Once I had that, I created the array from scratch, using the values that I
already knew for field lengths.
--
Regards
Roger Govier

"Dave Peterson" wrote in message
...
I don't think I'd trust excel to guess at the field lengths. But if it
worked
ok for you, then congrats.

I might have edited a text file (in notepad--or another text editor) and
put a
dummy record in near the top to help get the columns where I wanted them.

Letting excel guess at anything always scares me.

Roger Govier wrote:

Hi Dave

Many thanks for the suggestion. I have just managed to get back to the
problem this morning.
The code I wrote worked absolutely fine, but naturally was very slow.
My mind had become fixated on reading the data and chopping it into the
appropriate length strings.
The problem with Datatext to Columns, was that there are many fields
which
are empty. Trying to "eyeball" where the column dividers should be placed
was very difficult, so I discarded that.
However, following your suggestion about using a macro (also made to me
by
Debra in a private email -great minds think alike eh!!), I decided to
record
a macro on a copy set of data, letting Excel make it's own decision as to
where the splits should be, then manually amend the macro with the field
sizes as per the values in my Definition sheet and setting the range that
is
to be used for carrying out the conversion.

The resulting code, which worked perfectly (and instantaneously) is shown
below as it may help others approaching this type of problem
Many thanks for "dragging" me out of my fixed way of looking at the
problem.

Sub ParseToColumns()
Dim wss As Worksheet
Dim lr As Long
Set wss = ThisWorkbook.Sheets("OldData")
lr = wss.Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(2, 1), Cells(lr, 1)).Select

Selection.TextToColumns Destination:=Range("A2"),
DataType:=xlFixedWidth,
_
FieldInfo:= _
Array(Array(0, 1), Array(14, 1), Array(17, 1), Array(23, 1),
Array(26, 1), _
Array(35, 1), Array(38, 1), Array(47, 1), Array(50, 1), Array(53,
1), Array(54, 1), _
Array(57, 1), Array(67, 1), Array(77, 1), Array(79, 1), Array(81,
1), Array(91, 1), _
Array(92, 1), Array(95, 1), Array(105, 1), Array(108, 1),
Array(111,
1), Array(113, 1), _
Array(117, 1), Array(119, 1), Array(121, 1), Array(123, 1),
Array(125, 1), Array(127, 1), _
Array(130, 1), Array(132, 1), Array(133, 1), Array(135, 1),
Array(137, 1), Array(140, 1), _
Array(143, 1), Array(145, 1), Array(147, 1), Array(148, 1),
Array(149, 1), Array(150, 1), _
Array(158, 1), Array(159, 1), Array(163, 1), Array(165, 1),
Array(166, 1), Array(168, 1), _
Array(170, 1), Array(173, 1), Array(176, 1), Array(178, 1),
Array(180, 1), Array(182, 1), _
Array(184, 1), Array(186, 1), Array(188, 1), Array(191, 1),
Array(193, 1), Array(195, 1), _
Array(199, 1), Array(203, 1), Array(208, 1), Array(213, 1),
Array(216, 1), Array(219, 1), _
Array(222, 1), Array(225, 1))

End Sub

--

Regards
Roger Govier

"Dave Peterson" wrote in message
...
After you've transposed the data, you could record a macro that parses
your data
(Data tab|Data tools group|text to columns).

You'll have to separate & specify all 67 fields again, but once it's
done,
it's
done.

Well, until the layout changes!

Maybe you could do all of it in one macro.

Create a new workbook and start record when you open the file,
copy|transpose,
and data|text to columns. (And continue formatting (print headers &
footers,
column headers, freeze panes, autofilter, pivottables...)

Then save that new workbook with a nice name:
WorkbookToOpenParseAndFormatDOSOutput.xlsm

And just open that whenever you need to do it again.



Roger Govier wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson