Home |
Search |
Today's Posts |
#1
|
|||
|
|||
In Excel: If I know a file has 147,000 records, but Excel will on.
In Excel: If I know a file has 147,000 records, but Excel will only load
65,000, how do I get to the rest of the file? |
#2
|
|||
|
|||
MVP Chip Pearson's website has some code that may help
you out: http://www.cpearson.com/excel/imptext.htm HTH Jason Atlanta, GA -----Original Message----- In Excel: If I know a file has 147,000 records, but Excel will only load 65,000, how do I get to the rest of the file? . |
#3
|
|||
|
|||
I don't believe Chip's routines is designed to handle files beyond 64K rows.
Another approach is to pre-parse the file into several 64k row files. I've attached a VBS script that I wrote to do that. Copy it to your desktop and double-click it to run it. -- Jim Rech Excel MVP "Roy Lynn" <Roy wrote in message ... | In Excel: If I know a file has 147,000 records, but Excel will only load | 65,000, how do I get to the rest of the file? |
#4
|
|||
|
|||
attached a VBS script that I wrote to do that.
Was unable to save it, Jim .. Could you paste it in the message itself ? Thanks -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jim Rech" wrote in message ... I don't believe Chip's routines is designed to handle files beyond 64K rows. Another approach is to pre-parse the file into several 64k row files. Copy it to your desktop and double-click it to run it. -- Jim Rech Excel MVP "Roy Lynn" <Roy wrote in message ... | In Excel: If I know a file has 147,000 records, but Excel will only load | 65,000, how do I get to the rest of the file? |
#5
|
|||
|
|||
How about if someone else pastes it in a message?
Jim named his copy: ParseLongFile.vbs (the .VBS is important) '--------------------------------------------------------------- Dim fso, fileSource, fileDest Dim fileSourceNoExt,fileSourceExt Dim lineCount, txtLine Dim wsh, tempFile Dim fileCount, dotPos Set fso = CreateObject("Scripting.FileSystemObject") Set wsh = CreateObject("WScript.Shell") ''Set sh = CreateObject("Shell.Application") tempFile = InputBox("Enter the full path and name of file", _ "Long Text File Parser") ''The following would not return a file, just a folder object, '' even tho with the 16896 it displays files ''tempFile = sh.BrowseForFolder(0, "Select a Folder", 16896, "c:\") If tempFile < "" Then If fso.FileExists(tempFile) Then dotPos = InStrRev(tempFile,".") If dotPos 0 Then fileSourceNoExt = Left(tempFile, dotPos-1) fileSourceExt = Mid(tempFile,dotPos) Else fileSourceNoExt = tempFile End If Set fileSource = fso.OpenTextFile(tempFile) Do While fileSource.AtEndOfLine < True If lineCount = 0 Then FileCount = FileCount + 1 Set fileDest = fso.CreateTextFile(fileSourceNoExt & FileCount _ & fileSourceExt, True) End If txtLine = fileSource.ReadLine fileDest.WriteLine txtLine lineCount = lineCount + 1 If lineCount = 65536 Then fileDest.Close lineCount = 0 End If Loop fileSource.Close wsh.Popup "Done!", 1, "Long Text File Parser" Else wsh.Popup "Source file not found", 2, "Long Text File Parser" End if End If Max wrote: attached a VBS script that I wrote to do that. Was unable to save it, Jim .. Could you paste it in the message itself ? Thanks -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jim Rech" wrote in message ... I don't believe Chip's routines is designed to handle files beyond 64K rows. Another approach is to pre-parse the file into several 64k row files. Copy it to your desktop and double-click it to run it. -- Jim Rech Excel MVP "Roy Lynn" <Roy wrote in message ... | In Excel: If I know a file has 147,000 records, but Excel will only load | 65,000, how do I get to the rest of the file? -- Dave Peterson |
#6
|
|||
|
|||
"Dave Peterson" wrote
How about if someone else pastes it in a message? Jim named his copy: ParseLongFile.vbs (the .VBS is important) .... Thanks, Dave ! Think some steps on how to implement it properly from what you kindly "pasted" would be great (a little lost here) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
Copy that code.
open Notepad paste that code Save the file as ParseLongFile.vbs (desktop or your favorite folder) doubleclick on it to run (in windows explorer or at the desktop). Max wrote: "Dave Peterson" wrote How about if someone else pastes it in a message? Jim named his copy: ParseLongFile.vbs (the .VBS is important) ... Thanks, Dave ! Think some steps on how to implement it properly from what you kindly "pasted" would be great (a little lost here) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- -- Dave Peterson |
#8
|
|||
|
|||
Thanks for the steps, Dave !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Dave Peterson" wrote in message ... Copy that code. open Notepad paste that code Save the file as ParseLongFile.vbs (desktop or your favorite folder) doubleclick on it to run (in windows explorer or at the desktop). Max wrote: "Dave Peterson" wrote How about if someone else pastes it in a message? Jim named his copy: ParseLongFile.vbs (the .VBS is important) ... Thanks, Dave ! Think some steps on how to implement it properly from what you kindly "pasted" would be great (a little lost here) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- -- Dave Peterson |
#9
|
|||
|
|||
Thanks for handling this, Dave.
Max, if you want to be able to save "potentially dangerous attachments" go to Tools, Options, Security and uncheck "Do not allow attachments....". -- Jim Rech Excel MVP "Max" wrote in message ... | attached a VBS script that I wrote to do that. | | Was unable to save it, Jim .. | Could you paste it in the message itself ? | Thanks | -- | Rgds | Max | xl 97 | --- | GMT+8, 1° 22' N 103° 45' E | xdemechanik <atyahoo<dotcom | ---- | "Jim Rech" wrote in message | ... | I don't believe Chip's routines is designed to handle files beyond 64K | rows. | Another approach is to pre-parse the file into several 64k row files. | Copy it to your desktop and | double-click it to run it. | | -- | Jim Rech | Excel MVP | "Roy Lynn" <Roy wrote in message | ... | | In Excel: If I know a file has 147,000 records, but Excel will only load | | 65,000, how do I get to the rest of the file? | | |
#10
|
|||
|
|||
Thanks, Jim !
In my haste to plunge-in and grab, I totally forgot about the settings .. <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jim Rech" wrote in message ... Thanks for handling this, Dave. Max, if you want to be able to save "potentially dangerous attachments" go to Tools, Options, Security and uncheck "Do not allow attachments....". -- Jim Rech Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Excel 2000 file when opened in Excel 2003 generates errors? | Excel Discussion (Misc queries) | |||
double click a xls file and start Excel but without the file | Excel Discussion (Misc queries) | |||
Saving a Excel 97 file into Excel 2003 file | Excel Discussion (Misc queries) |