TextToColumns macro using a map file

[Leahs_Dad]

I get a lot of data files that are fixed field ASCII that need to be
converted to Excel spreadsheets. I find the Text To Columns feature
often makes bad guesses as to where the separators go and I spend a
lot of time removing or moving them.

Most of these data files come with a 'map' file with field name,
length, and start column.

Is there a way to feed this information to the TextToColumns function
rather than having to hard code the macro or manually do Text To
Columns?

Thanks

Tom H

[Dick Kusleika]

Tom

Well, two days and about 500 restarts and I think I have an answer for you.
Yes you can feed that info to text to columns, but it's not easy. Basically
you have to read the text file in and build an array of arrays to pass to
the FieldInfo argument. Say you have a map file that looks like this

Field1,0,5
Field2,5,5
Field3,10,5
Field4,15,5
Field5,20,5

Field Name, Start Column, Length

Here's a an example macro that will parse this map, build an array, and
perform the text to columns:

Sub TTCwMap()

Dim FName As String
Dim FNum As Long
Dim Rng As Range
Dim FldStr As String
Dim Tmp As Variant
Dim i As Long, j As Long
Dim Flds() As Variant
Dim FldInfo() As Variant

FName = "C:\My Documents\Map.txt"
FNum = FreeFile

Open FName For Input As FNum

'Cycle through the file
Do While Not EOF(FNum)

'Input the line into FldStr
Line Input #FNum, FldStr

'Split the input line by comma into an array
Tmp = Split(FldStr, ",")

'Increase the size of Flds to hold the new data
ReDim Preserve Flds(0 To 2, 0 To j)

'Fill Flds with the info
For i = LBound(Tmp) To UBound(Tmp)
Flds(i, j) = Tmp(i)
Next i
j = j + 1
Loop

Close FNum

'Size the array that will be passed as an argument
ReDim FldInfo(j - 1)

'Create an array of arrays: first dimension is column start,
'second dimension is data type - all general in this example
For i = LBound(FldInfo) To UBound(FldInfo)
FldInfo(i) = Array(CLng(Flds(1, i)), 1)
Next i

Set Rng = Sheet1.Range("ttcrange")

'Pass the array of arrays
Rng.TextToColumns , xlFixedWidth, , , , , , , , , FldInfo

Set Rng = Nothing

End Sub

You have to have XL2000 or higher to use the Split function. Also, don't
forget the CLng function - passing an array with text to TextToColumns
crashes XL2000. I learned that the hard way.

Good luck and post back if you need more help.

--
Dick Kusleika
MVP - Excel

"Leahs_Dad" <th...@aol.com> wrote in message
news:a8e7953f.0302...@posting.google.com...