Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width Layout
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width Layout
I don't think that there's any generic way to tell how fixed width files are
laid out. Any chance you can go back to the programmer who made the text file and ask him/her for the spec. I would bet that the only way that Access can know is that someone took the time to set up the rules. Maybe you can talk to that person for this specific file type???? 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width Layout
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width Layout
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width Layout
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width Layout
I left the msgbox and added a few lines of code to write the same info to the
active worksheet 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) Cells(Fields + 1, "A") = "Field " & (Fields + 1) Cells(Fields + 1, "B") = StartField(Fields) Cells(Fields + 1, "C") = MyWidth 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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width Layout
Thanks Joel
Can I add thos to the code ReadFilename = Application.GetSaveAsFilename( _ fileFilter:="Text Files, *.txt", _ InitialFileName:="FixedWidth.txt",Title:="SelectDa taFile") If possible I want to be ablde to navigate a choose the file. I tried it but ofcourse it did not work. The good the about all help help you have given me is every time the code is mode I can campare the difference. This is a huge help in learning to do this on my own. Thanks On Thu, 13 Sep 2007 19:26:02 -0700, Joel wrote: I left the msgbox and added a few lines of code to write the same info to the active worksheet 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) Cells(Fields + 1, "A") = "Field " & (Fields + 1) Cells(Fields + 1, "B") = StartField(Fields) Cells(Fields + 1, "C") = MyWidth 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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width Layout
The fix was simple. The old code had the following:
ReadPathName = MyPath + ReadFilename Notice the path was included in the ReadPathName. GetSaveAsFilename was also including the path. You had the path in the name twice. I simply removed MyPath from ReadPathName. I nthe code below I change your InitialFileName to include MyPath. Now the Pop Up window goes to the MyPath directory (folder). The Pop up will return the full pathname of the file which you can simply open. There is a problem with some VBA file selection methods that you have to strip the filename from the path to use them. I originally set the code up keeping the path and the filename seperate so these functions can be used (you don't have this problem yet). In the past, I've had to write a simple loop to strip the file name from the path when using some of these commands. It is just extra code that is unecessary. Your code is just fine! Sub advanceformat() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Dim StartField(120) ReadFilename = Application.GetSaveAsFilename( _ fileFilter:="Text Files, *.txt", _ InitialFileName:=MyPath & "FixedWidth.txt", _ Title:="SelectDataFile") Set fsread = CreateObject("Scripting.FileSystemObject") 'open files Set fread = fsread.GetFile(ReadFilename) 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) Cells(Fields + 1, "A") = "Field " & (Fields + 1) Cells(Fields + 1, "B") = StartField(Fields) Cells(Fields + 1, "C") = MyWidth 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 Joel Can I add thos to the code ReadFilename = Application.GetSaveAsFilename( _ fileFilter:="Text Files, *.txt", _ InitialFileName:="FixedWidth.txt",Title:="SelectDa taFile") If possible I want to be ablde to navigate a choose the file. I tried it but ofcourse it did not work. The good the about all help help you have given me is every time the code is mode I can campare the difference. This is a huge help in learning to do this on my own. Thanks On Thu, 13 Sep 2007 19:26:02 -0700, Joel wrote: I left the msgbox and added a few lines of code to write the same info to the active worksheet 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) Cells(Fields + 1, "A") = "Field " & (Fields + 1) Cells(Fields + 1, "B") = StartField(Fields) Cells(Fields + 1, "C") = MyWidth 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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Width Layout
Thanks for the education. Everything works as advertised. I do have one more question which I will post under a new subject. I has to do with copying a module to another workbook. Thanks for all your help On Fri, 14 Sep 2007 15:44:01 -0700, Joel wrote: The fix was simple. The old code had the following: ReadPathName = MyPath + ReadFilename Notice the path was included in the ReadPathName. GetSaveAsFilename was also including the path. You had the path in the name twice. I simply removed MyPath from ReadPathName. I nthe code below I change your InitialFileName to include MyPath. Now the Pop Up window goes to the MyPath directory (folder). The Pop up will return the full pathname of the file which you can simply open. There is a problem with some VBA file selection methods that you have to strip the filename from the path to use them. I originally set the code up keeping the path and the filename seperate so these functions can be used (you don't have this problem yet). In the past, I've had to write a simple loop to strip the file name from the path when using some of these commands. It is just extra code that is unecessary. Your code is just fine! Sub advanceformat() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Dim StartField(120) ReadFilename = Application.GetSaveAsFilename( _ fileFilter:="Text Files, *.txt", _ InitialFileName:=MyPath & "FixedWidth.txt", _ Title:="SelectDataFile") Set fsread = CreateObject("Scripting.FileSystemObject") 'open files Set fread = fsread.GetFile(ReadFilename) 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) Cells(Fields + 1, "A") = "Field " & (Fields + 1) Cells(Fields + 1, "B") = StartField(Fields) Cells(Fields + 1, "C") = MyWidth 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 Joel Can I add thos to the code ReadFilename = Application.GetSaveAsFilename( _ fileFilter:="Text Files, *.txt", _ InitialFileName:="FixedWidth.txt",Title:="SelectDa taFile") If possible I want to be ablde to navigate a choose the file. I tried it but ofcourse it did not work. The good the about all help help you have given me is every time the code is mode I can campare the difference. This is a huge help in learning to do this on my own. Thanks On Thu, 13 Sep 2007 19:26:02 -0700, Joel wrote: I left the msgbox and added a few lines of code to write the same info to the active worksheet 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) Cells(Fields + 1, "A") = "Field " & (Fields + 1) Cells(Fields + 1, "B") = StartField(Fields) Cells(Fields + 1, "C") = MyWidth 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column fixed width | Excel Discussion (Misc queries) | |||
Fixed width fonts | Excel Discussion (Misc queries) | |||
Fixed Width | Excel Programming | |||
fixed width file? | Excel Programming | |||
Fixed Width - Opening Certain Fixed Width Files | Excel Programming |