Thanks again Joel

Is it possible to have the reults in the spreadsheet to look something
like this. If not you have done more than enough.


A B C
Start Width
Field 1 1 25
Field 2 70 13
Field 3 83 11
Field 4 94 19
Field 5 133 29
Field 6 142 18
Field 7 160 14



Thanks


Litle Penny





On Wed, 12 Sep 2007 23:40:03 -0700, Joel
wrote:

I fixed a couple of problems.

Sub advanceformat()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim StartField(120)

ReadFilename = "FixedWidth.txt"

Set fsread = CreateObject("Scripting.FileSystemObject")

'open files
ReadPathName = MyPath + ReadFilename
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

inputline = tsread.readline
Length = Len(inputline)

StartField(0) = 1
FieldCount = 1
Spaces = False
For ColumnCount = 1 To Length

Select Case Spaces

Case True
If Mid(inputline, ColumnCount, 1) < " " Then
StartField(FieldCount) = _
ColumnCount
FieldCount = FieldCount + 1
Spaces = False
End If
Case False
If Mid(inputline, ColumnCount, 1) = " " Then
Spaces = True
End If
End Select

Next ColumnCount
StartField(FieldCount) = Length + 1
OutputString = "Number of columns = " & _
Length & Chr(13)

For Fields = 0 To (FieldCount - 1)
MyWidth = StartField(Fields + 1) - _
StartField(Fields)
OutputString = OutputString & "Field " & _
(Fields + 1) & " start at " & _
StartField(Fields) & ", width = " & _
MyWidth & Chr(13)
Next Fields
MsgBox (OutputString)
tsread.Close

End Sub



"Little Penny" wrote:

Thanks again Joel

There is one thing. The last column in the file is always a period (.)
for some reason this is the only column that the macro does not
recognize for example my test file has 24 columns the maco only shows
me 23. I'll try and figure out why.


But thanks again





On Wed, 12 Sep 2007 05:30:06 -0700, Joel
wrote:

Thank you Thank you Thank you

Change the filename as required. this is a very primitive function that
only checks 1st line of the code.

Sub advanceformat()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Dim StartField(120)

ReadFilename = "FixedWidth.txt"

Set fsread = CreateObject("Scripting.FileSystemObject")

'open files
ReadPathName = MyPath + ReadFilename
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

inputline = tsread.readline
Length = Len(inputline)

StartField(0) = 1
FieldCount = 1
Spaces = False
For ColumnCount = 1 To Length
a = Mid(inputline, ColumnCount, 1)
Select Case Spaces

Case True
If Mid(inputline, ColumnCount, 1) < " " Then
StartField(FieldCount) = _
ColumnCount
FieldCount = FieldCount + 1
Spaces = False
End If
Case False
If Mid(inputline, ColumnCount, 1) = " " Then
Spaces = True
End If
End Select

Next ColumnCount
StartField(FieldCount) = Length
OutputString = "Number of columns = " & _
Length & Chr(13)

For Fields = 0 To (FieldCount - 2)
MyWidth = StartField(Fields + 1) - _
StartField(Fields)
OutputString = OutputString & "Field " & _
(Fields + 1) & " start at " & _
StartField(Fields) & ", width = " & _
MyWidth & Chr(13)
Next Fields
MsgBox (OutputString)
tsread.Close

End Sub


"Little Penny" wrote:

Is there a macro that can tell me the layout (Number of columns and
column width) of fixed with text file?

I currently import these files into MS Access (Import text wizard) and
use the advance feature to tell me this information.

Thanks