Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import partial txt file using VBA
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import partial txt file using VBA
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" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import partial txt file using VBA
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import partial txt file using VBA
Any other ideas on how to do this using Excel VBA?
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import partial txt file using VBA
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 "lookin" wrote in message oups.com... Any other ideas on how to do this using Excel VBA? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import partial txt file using VBA
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 "lookin" wrote in message ups.com... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Import partial record from text file string | Excel Programming | |||
Import partial record from text file string | Excel Programming |