Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
have a text file I need to convert to an excel spreadsheet. I
wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Mary,
The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Maybe something like the below pseudo code if the file is spaced delimited
Sub ReadData() Dim ff as Long, rw as Long, v as Variant, line as String Dim i as Long ff = FreeFile() rw = 1 Open "C:\Myfolder\Myfile.txt" for input as #ff do while not eof(ff) Line Input #FF, line line = Application.Trim(line) v = Split(Line," ") for i = lbound(v) to ubound(v) cells(rw,i).value = v(i) Next rw = rw + 1 Loop Close #ff End Sub -- Regards, Tom Ogilvy " wrote: have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Using the reading techniques sugeested by others, this may be an opportunity
to use a set of classes to handle each situation, if you feel you wish to expand your VBA knowledge. Untested, but you get the idea: <cls_01 Public function ProcessLine (argInput as string) as variant 'Assuming single space separated values 'Add trim and/or processing if required ProcessLine = split(argInput, " ") End function </cls_01 <cls_02 Public function ProcessLine (argInput as string) as variant Dim Temp(2) as variant 'Assuming 18 Chars[SPACE]21 Chars[Date] 'Add trim and/or processing if required Temp(0)=left(argInput,18) Temp(1)=Mid(argInput,20,21) Temp(2)=Right(argInput,8) 'Or, if in date type 'Temp(2)=cDate(Right(argInput,8)) ProcessLine =temp() End function </cls_02 ... etc for other formats. It may be a good time to use an interface/Implements if you feel so inclined. To me the advantage of this approach is that if any format change, it very clear and easy to change how each class processes its own data. And also add to the list in the future, by simply creating a new class. NickHK groups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
I'm not sure I totally understand. When bring in a fixed length file I
get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Excel will not be able to import it on own.
Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Just to be clear, the code suggested (and mine) would be in lieu of your
recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
I'm totally down with that. however, i'm just trying to figure out
where to update the code so that I can put in my markers so it delineates in the places I need. Tom Ogilvy wrote: Just to be clear, the code suggested (and mine) would be in lieu of your recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Tom was pointing out that Excel expects every row to be the same, so you
cannot use your code to read file, because the records of different formats. You need to open/close the file yourself, read each record (line), determine the format ("01", "02" etc) and process it accordingly. Hence, - Open the file - Keep reading until the end of the file - Read the line into a variable - Check the format - Process according - Loop - Close the file NickHK egroups.com... I'm totally down with that. however, i'm just trying to figure out where to update the code so that I can put in my markers so it delineates in the places I need. Tom Ogilvy wrote: Just to be clear, the code suggested (and mine) would be in lieu of your recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Mary,
How, for example would you want 023330948951000312 00370058274800350010112/01/2002 split up? Are the last 10 characters a date? Any other meaningful splits, or just use spaces? Post samples of how each string needs to be split, and we can help you with the code to do it. HTH, Bernie MS Excel MVP wrote in message oups.com... I'm totally down with that. however, i'm just trying to figure out where to update the code so that I can put in my markers so it delineates in the places I need. Tom Ogilvy wrote: Just to be clear, the code suggested (and mine) would be in lieu of your recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Yes, I understand that, but what I'm not sure of is how I get my
formatting in. This line: Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr," ") - 1) is where I imagine the formatting is but I'm not sure what I need to do...or will I just have to manually update every line as it comes in? eg: if I need my breaks to be at 2, 10, 25, and 40 in row one (and that represents 4 cells) would that get incorporated into the line I cited? I'm thinking I'm way over my head here but I do so appreciate your help. NickHK wrote: Tom was pointing out that Excel expects every row to be the same, so you cannot use your code to read file, because the records of different formats. You need to open/close the file yourself, read each record (line), determine the format ("01", "02" etc) and process it accordingly. Hence, - Open the file - Keep reading until the end of the file - Read the line into a variable - Check the format - Process according - Loop - Close the file NickHK egroups.com... I'm totally down with that. however, i'm just trying to figure out where to update the code so that I can put in my markers so it delineates in the places I need. Tom Ogilvy wrote: Just to be clear, the code suggested (and mine) would be in lieu of your recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Mary,
Something like this would need to go into the appropriate section (I hope you can see the pattern): Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 1) Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 9) Cells(Counter, 3).Value = "'" & Mid(ResultStr, 11, 15) Cells(Counter, 4).Value = "'" & Mid(ResultStr, 26, 15) Cells(Counter, 5).Value = "'" & Mid(ResultStr, 41, 15) HTH, Bernie MS Excel MVP wrote in message oups.com... Yes, I understand that, but what I'm not sure of is how I get my formatting in. This line: Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr," ") - 1) is where I imagine the formatting is but I'm not sure what I need to do...or will I just have to manually update every line as it comes in? eg: if I need my breaks to be at 2, 10, 25, and 40 in row one (and that represents 4 cells) would that get incorporated into the line I cited? I'm thinking I'm way over my head here but I do so appreciate your help. NickHK wrote: Tom was pointing out that Excel expects every row to be the same, so you cannot use your code to read file, because the records of different formats. You need to open/close the file yourself, read each record (line), determine the format ("01", "02" etc) and process it accordingly. Hence, - Open the file - Keep reading until the end of the file - Read the line into a variable - Check the format - Process according - Loop - Close the file NickHK egroups.com... I'm totally down with that. however, i'm just trying to figure out where to update the code so that I can put in my markers so it delineates in the places I need. Tom Ogilvy wrote: Just to be clear, the code suggested (and mine) would be in lieu of your recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
I'm going from a fixed length file to excel
an 02 row would be start 1 end 2, start 3 end 11, start 12, end 31, start 32 end 36 an 01 row would be start 1 end 2, start 3 end 28, start 29 end 37, start 38 end 97 03 ould be different, etc. Maybe there is no way to actually get it to do what I want. I'm just hoping that I can take these fixed length rows and translate them into excel. There are 6 possible records and each one has slightly differnt fixed lengths. The file can be made up of thousands of records each of which is a 1 through 6. Bernie Deitrick wrote: Mary, How, for example would you want 023330948951000312 00370058274800350010112/01/2002 split up? Are the last 10 characters a date? Any other meaningful splits, or just use spaces? Post samples of how each string needs to be split, and we can help you with the code to do it. HTH, Bernie MS Excel MVP wrote in message oups.com... I'm totally down with that. however, i'm just trying to figure out where to update the code so that I can put in my markers so it delineates in the places I need. Tom Ogilvy wrote: Just to be clear, the code suggested (and mine) would be in lieu of your recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Oops, I messed up my editing and math...
Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 2) Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 8) Cells(Counter, 3).Value = "'" & Mid(ResultStr, 11, 15) Cells(Counter, 4).Value = "'" & Mid(ResultStr, 26, 15) Cells(Counter, 5).Value = "'" & Mid(ResultStr, 41, 15) etc... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Mary, Something like this would need to go into the appropriate section (I hope you can see the pattern): Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 1) Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 9) Cells(Counter, 3).Value = "'" & Mid(ResultStr, 11, 15) Cells(Counter, 4).Value = "'" & Mid(ResultStr, 26, 15) Cells(Counter, 5).Value = "'" & Mid(ResultStr, 41, 15) HTH, Bernie MS Excel MVP wrote in message oups.com... Yes, I understand that, but what I'm not sure of is how I get my formatting in. This line: Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr," ") - 1) is where I imagine the formatting is but I'm not sure what I need to do...or will I just have to manually update every line as it comes in? eg: if I need my breaks to be at 2, 10, 25, and 40 in row one (and that represents 4 cells) would that get incorporated into the line I cited? I'm thinking I'm way over my head here but I do so appreciate your help. NickHK wrote: Tom was pointing out that Excel expects every row to be the same, so you cannot use your code to read file, because the records of different formats. You need to open/close the file yourself, read each record (line), determine the format ("01", "02" etc) and process it accordingly. Hence, - Open the file - Keep reading until the end of the file - Read the line into a variable - Check the format - Process according - Loop - Close the file NickHK egroups.com... I'm totally down with that. however, i'm just trying to figure out where to update the code so that I can put in my markers so it delineates in the places I need. Tom Ogilvy wrote: Just to be clear, the code suggested (and mine) would be in lieu of your recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Geniuses! Thank you so much Bernie! And every one else! This has
been so eye openeing and I even understand! I bow before you!!! wrote: Ok. I get that. Now for some strange reason it doesn't work well... I did: Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 2) Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 11) Cells(Counter, 3).Value = "'" & Mid(ResultStr, 12, 31) Cells(Counter, 4).Value = "'" & Mid(ResultStr, 32, 36) Cells(Counter, 5).Value = "'" & Mid(ResultStr, 37, 51) Cells(Counter, 6).Value = "'" & Mid(ResultStr, 52, 66) Cells(Counter, 7).Value = "'" & Mid(ResultStr, 67, 91) for counter 2 it is adding places 12 & 13, yet on counter 3 it is putting those 12 $ 13 back in. For counter 5 it is running from 37 all the way through 91. Each one contains part of the next element (or all) when I spread out the column on the excel spread sheet. wrote: I'm going from a fixed length file to excel an 02 row would be start 1 end 2, start 3 end 11, start 12, end 31, start 32 end 36 an 01 row would be start 1 end 2, start 3 end 28, start 29 end 37, start 38 end 97 03 ould be different, etc. Maybe there is no way to actually get it to do what I want. I'm just hoping that I can take these fixed length rows and translate them into excel. There are 6 possible records and each one has slightly differnt fixed lengths. The file can be made up of thousands of records each of which is a 1 through 6. Bernie Deitrick wrote: Mary, How, for example would you want 023330948951000312 00370058274800350010112/01/2002 split up? Are the last 10 characters a date? Any other meaningful splits, or just use spaces? Post samples of how each string needs to be split, and we can help you with the code to do it. HTH, Bernie MS Excel MVP wrote in message oups.com... I'm totally down with that. however, i'm just trying to figure out where to update the code so that I can put in my markers so it delineates in the places I need. Tom Ogilvy wrote: Just to be clear, the code suggested (and mine) would be in lieu of your recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed length file to excel? VBA macro possible?
Mary,
This Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 2) Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 11) Cells(Counter, 3).Value = "'" & Mid(ResultStr, 12, 31) Cells(Counter, 4).Value = "'" & Mid(ResultStr, 32, 36) Cells(Counter, 5).Value = "'" & Mid(ResultStr, 37, 51) Cells(Counter, 6).Value = "'" & Mid(ResultStr, 52, 66) Cells(Counter, 7).Value = "'" & Mid(ResultStr, 67, 91) Should be this - MID takes a length, not the right-most str. position: Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 2) Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 9) Cells(Counter, 3).Value = "'" & Mid(ResultStr, 12, 20) Cells(Counter, 4).Value = "'" & Mid(ResultStr, 32, 10) Cells(Counter, 5).Value = "'" & Mid(ResultStr, 37, 5) Cells(Counter, 6).Value = "'" & Mid(ResultStr, 52, 15) Cells(Counter, 7).Value = "'" & Mid(ResultStr, 67, 24) HTH, Bernie MS Excel MVP wrote in message ups.com... Ok. I get that. Now for some strange reason it doesn't work well... I did: Cells(Counter, 1).Value = "'" & Mid(ResultStr, 1, 2) Cells(Counter, 2).Value = "'" & Mid(ResultStr, 3, 11) Cells(Counter, 3).Value = "'" & Mid(ResultStr, 12, 31) Cells(Counter, 4).Value = "'" & Mid(ResultStr, 32, 36) Cells(Counter, 5).Value = "'" & Mid(ResultStr, 37, 51) Cells(Counter, 6).Value = "'" & Mid(ResultStr, 52, 66) Cells(Counter, 7).Value = "'" & Mid(ResultStr, 67, 91) for counter 2 it is adding places 12 & 13, yet on counter 3 it is putting those 12 $ 13 back in. For counter 5 it is running from 37 all the way through 91. Each one contains part of the next element (or all) when I spread out the column on the excel spread sheet. wrote: I'm going from a fixed length file to excel an 02 row would be start 1 end 2, start 3 end 11, start 12, end 31, start 32 end 36 an 01 row would be start 1 end 2, start 3 end 28, start 29 end 37, start 38 end 97 03 ould be different, etc. Maybe there is no way to actually get it to do what I want. I'm just hoping that I can take these fixed length rows and translate them into excel. There are 6 possible records and each one has slightly differnt fixed lengths. The file can be made up of thousands of records each of which is a 1 through 6. Bernie Deitrick wrote: Mary, How, for example would you want 023330948951000312 00370058274800350010112/01/2002 split up? Are the last 10 characters a date? Any other meaningful splits, or just use spaces? Post samples of how each string needs to be split, and we can help you with the code to do it. HTH, Bernie MS Excel MVP wrote in message oups.com... I'm totally down with that. however, i'm just trying to figure out where to update the code so that I can put in my markers so it delineates in the places I need. Tom Ogilvy wrote: Just to be clear, the code suggested (and mine) would be in lieu of your recorded code. It represents a different approach. -- Regards, Tom Ogilvy "NickHK" wrote: Excel will not be able to import it on own. Whilst good at many tasks, it cannot guess your intenetions. So you have to help out with some extra code. See the other responses. NickHK groups.com... I'm not sure I totally understand. When bring in a fixed length file I get something like this: Workbooks.OpenText FileName:= _ "C:\Documents and Settings\ca5ms\Desktop\name line.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, _ 1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1), Array(51, 1), Array(66, 1), _ Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1), Array(241, 1), Array(246, 1), _ Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1), Array(275, 1), Array(278, 1), _ Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1), Array(346, 1), Array(356, 1), _ Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)), TrailingMinusNumbers:= _ True Because i need to format the lines that start with say 02 differently than those that start with 03. In your model where would I put the line formatting? Thanks! Mary Bernie Deitrick wrote: Mary, The macro below shows one way of doing this.... HTH, Bernie MS Excel MVP Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell If Left(ResultStr, 2) = "01" Then MsgBox "That line started with 01" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "02" Then MsgBox "That line started with 02" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc ElseIf Left(ResultStr, 2) = "03" Then MsgBox "That line started with 03" 'do the splitting here, like Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub wrote in message ups.com... have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create an ASCII Fixed Length File from Excel | Excel Discussion (Misc queries) | |||
fixed length file | Excel Discussion (Misc queries) | |||
save an excel file in fixed length records whose fields are blank | Excel Discussion (Misc queries) | |||
Exporting from Excel to a Fixed Length Flat File | Excel Worksheet Functions | |||
How do I export an excel file as fixed length records | Excel Discussion (Misc queries) |