Fixed Width Layout
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
|