Loading text file
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. |
Loading text file
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. |
Loading text file
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. |
Loading text file
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. . |
All times are GMT +1. The time now is 09:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com