Hi, I need to read a text file and load it into excel. I
know the size of each field. I used the wizard, but I have
already done this 43 times with no end in sight. Is there
somebody out there that could give me a sample macro to
read each line of a text file, and split it up into
different fields? Thanks for the help. I would do it
myself, but I have no idea how to do it.

Private Sub CommandButton1_Click()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

FName = Application.GetOpenFilename
Sep = InputBox("Enter the delimiter, for tab delimited data type tab")
If Sep = "tab" Then
Sep = vbTab
End If

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

Dan E

"Clayton L. Wilson" wrote in message
...
Hi, I need to read a text file and load it into excel. I
know the size of each field. I used the wizard, but I have
already done this 43 times with no end in sight. Is there
somebody out there that could give me a sample macro to
read each line of a text file, and split it up into
different fields? Thanks for the help. I would do it
myself, but I have no idea how to do it.

oops,

First Line Should be
Public Sub ImportText()

Dan E

"Dan E" wrote in message
...
Private Sub CommandButton1_Click()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

FName = Application.GetOpenFilename
Sep = InputBox("Enter the delimiter, for tab delimited data type tab")
If Sep = "tab" Then
Sep = vbTab
End If

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

Dan E

"Clayton L. Wilson" wrote in
message
...
Hi, I need to read a text file and load it into excel. I
know the size of each field. I used the wizard, but I have
already done this 43 times with no end in sight. Is there
somebody out there that could give me a sample macro to
read each line of a text file, and split it up into
different fields? Thanks for the help. I would do it
myself, but I have no idea how to do it.

Thanks for the macro, I'll try to figure it out, however
the text files are space delimited (formated text). Is
there any way to split the line up by column widths? Each
field is a different width as well.

-----Original Message-----
Private Sub CommandButton1_Click()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

FName = Application.GetOpenFilename
Sep = InputBox("Enter the delimiter, for tab delimited
data type tab")
If Sep = "tab" Then
Sep = vbTab
End If

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

Dan E

"Clayton L. Wilson"
wrote in message
...
Hi, I need to read a text file and load it into excel. I
know the size of each field. I used the wizard, but I
have
already done this 43 times with no end in sight. Is
there
somebody out there that could give me a sample macro to
read each line of a text file, and split it up into
different fields? Thanks for the help. I would do it
myself, but I have no idea how to do it.


.