In Excel: If I know a file has 147,000 records, but Excel will on.

[Guest]

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?

 

[Jason Morin]

MVP Chip Pearson's website has some code that may help
you out:

http://www.cpearson.com/excel/imptext.htm

HTH
Jason
Atlanta, GA

 

[Max]

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

 

[Dave Peterson]

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]

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)

 

[Dave Peterson]

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]

Thanks for the steps, Dave !

 

[Jim Rech]

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
|> 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 <at>yahoo<dot>com
| ----
| | > 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
| > | > | 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?
|
|

 

[Max]

Thanks, Jim !
In my haste to plunge-in and grab,
I totally forgot about the settings .. <g>