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?

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?
.

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?

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?

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

"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
----

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

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

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?
|
|

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