Import partial txt file using VBA

[lookin]

I have a txt file that has >161,200 lines. Because it is so large I am
unable to open it using excel. I am using the following code to open
the txt file:

Shell "Notepad.exe" & " " & "path\filename.txt", vbMaximizedFocus

Now I can't figure out how to select and copy only the lines that I
need, lines 161237-161267.

Can anyone help me w/ this?

 

[Guest]

Have you got Access installed on your system? That would do it (just make a
new database and then choose File, Get External Data, Import).

 

[lookin]

Access doesn't allow the proper formatting - I would prefer to copy the
necessary rows using an automated process. I don't want end-users to
have to open Access, and import the files themselves.

 

[lookin]

Any other ideas on how to do this using Excel VBA?

 

[NickHK]

lookin,
If the file is structured and you have way to identify the required rows,
just query the text file, something like:
SELECT * FROM MyTextFile WHERE ID>=161237 AND ID<=161267.
(or is "LIMIT 161236 ,30" supported ?)
Record a new macro whilst making the query will give you the code to play
with. Even set the limits as parameters so you can decide yourself what you
want to import.

NickHK

 

[kounoike]

This one will import text file as comma Delimited data.
if you want data in text file in one line as whole into one cell, this
will not work as it is.
but try this

Sub textfileread()
Dim filename
Dim FileNum As Integer
Dim Counter As Long, maxrow As Long
Dim WorkResult As String
Dim startnum As Long, endnum As Long
Dim co As Long

On Error GoTo ErrorCheck
startnum = 161237 <<== change this
endnum = 161267 <<== change this
co = 0
maxrow = Cells.Rows.Count
filename = Application.GetOpenFilename(FileFilter:="All File
(*.*),*")
If VarType(filename) = vbBoolean Then
Exit Sub
End If

Application.ScreenUpdating = False
Application.EnableEvents = False
Counter = Cells(Cells.Rows.Count, "a").End(xlUp).Row
If Counter <> 1 Then
Counter = Counter + 1
End If
FileNum = FreeFile()
Open filename For Input As #FileNum
Do While Not EOF(FileNum)
Line Input #FileNum, WorkResult
co = co + 1
If co > endnum Then
Exit Do
ElseIf co >= startnum Then
If WorkResult <> "" Then
Cells(Counter, 1) = """" & WorkResult
Cells(Counter, 1).TextToColumns _
Destination:=Cells(Counter, 1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
End If
Counter = Counter + 1
If Counter > maxrow Then
MsgBox "data have over max rows"
Exit Do
End If
End If
Loop
Close FileNum
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
ErrorCheck:
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "An error occured in the code."
End Sub

keizi