Windows XP Pro SP2
Excel 2002 SP3

I am trying to detect if a txt or csv file has more than 65,536 lines before
opening it. I've written this little tidbit for testing:

Sub testme()

Dim data As String
Dim fname As String
Dim c As Long

fname = "C:\911\_temp\test_data1.csv"

Open fname For Input As #1
c = 0
Do Until EOF(1)
Line Input #1, data
c = c + 1
Loop
Close #1

MsgBox c

End Sub


This works fine on files with less than or equal to 65,536 lines of data.
If I try the same code on a file with 65,537 lines or more then Excel locks
up.

Is there any way around this? Why is Excel crashing? Should I bite the
bullet and learn to use Access?

Any and all suggestions welcome. Thanks!

-gk-


================================================== ======================
"The creative act is not the province of remote oracles or rarefied
geniuses but a transparent process that is open to everyone."
-Greg Kot in Wilco Learning How To Die-

I ran that code against a giant text file that I have and changed the msgbox to
debug.print and got this back:

1,499,022

Those 1.5 million lines were read in pretty quickly.

Printing the time before and after showed:
13:36:35
1,499,022
13:36:36

(about a second)

====
Are you sure that it's this portion of the code that's causing the trouble?


39N 95W wrote:

Windows XP Pro SP2
Excel 2002 SP3

I am trying to detect if a txt or csv file has more than 65,536 lines before
opening it. I've written this little tidbit for testing:

Sub testme()

Dim data As String
Dim fname As String
Dim c As Long

fname = "C:\911\_temp\test_data1.csv"

Open fname For Input As #1
c = 0
Do Until EOF(1)
Line Input #1, data
c = c + 1
Loop
Close #1

MsgBox c

End Sub

This works fine on files with less than or equal to 65,536 lines of data.
If I try the same code on a file with 65,537 lines or more then Excel locks
up.

Is there any way around this? Why is Excel crashing? Should I bite the
bullet and learn to use Access?

Any and all suggestions welcome. Thanks!

-gk-

================================================== ======================
"The creative act is not the province of remote oracles or rarefied
geniuses but a transparent process that is open to everyone."
-Greg Kot in Wilco Learning How To Die-

--

Dave Peterson

I'm pretty sure because I set a series of test files. The test data is GPS
data, and I was able to create a test file with exactly 65,536 lines and
another with 65,537 lines. The first one executes just fine (and very
quickly as you noted). The second one seems to immediately go the hourglass
mouse pointer, and I can't even Alt|Page Break to get out of it.

The ultimate goal is to figure out a way to either cut these huge files into
multiple file sizes Excel can handle, or bring in the overflowing data into
sequential worksheets within a single Excel file.

Anyway, all I'm doing now is trying to detect files that will be too big for
Excel.

Maybe there's a better method?

-gk-


"Dave Peterson" wrote in message
...
I ran that code against a giant text file that I have and changed the
msgbox to
debug.print and got this back:

1,499,022

Those 1.5 million lines were read in pretty quickly.

Printing the time before and after showed:
13:36:35
1,499,022
13:36:36

(about a second)

====
Are you sure that it's this portion of the code that's causing the
trouble?


39N 95W wrote:

Windows XP Pro SP2
Excel 2002 SP3

I am trying to detect if a txt or csv file has more than 65,536 lines
before
opening it. I've written this little tidbit for testing:

Sub testme()

Dim data As String
Dim fname As String
Dim c As Long

fname = "C:\911\_temp\test_data1.csv"

Open fname For Input As #1
c = 0
Do Until EOF(1)
Line Input #1, data
c = c + 1
Loop
Close #1

MsgBox c

End Sub

This works fine on files with less than or equal to 65,536 lines of data.
If I try the same code on a file with 65,537 lines or more then Excel
locks
up.

Is there any way around this? Why is Excel crashing? Should I bite the
bullet and learn to use Access?

Any and all suggestions welcome. Thanks!

-gk-

================================================== ======================
"The creative act is not the province of remote oracles or rarefied
geniuses but a transparent process that is open to everyone."
-Greg Kot in Wilco Learning How To Die-

--

Dave Peterson

From Dana DeLouis:

Function NumberOfRecords(sFile As String) As Double
' = = = = = = = = = = = = = = = = = = = = = = = =
'// By: Dana DeLouis
'// Returns the number of lines in a text file
'// Use: Answer = NumberOfRecords("C:\Test.txt")
' = = = = = = = = = = = = = = = = = = = = = = = =
Dim f As Object
With CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set f = .OpenTextFile(sFile, 8) ' 8 - Appending
If Err.Number 0 Then
NumberOfRecords = 0
Else
NumberOfRecords = f.Line - 1
f.Close
Set f = Nothing
End If
End With
End Function



39N 95W wrote:

I'm pretty sure because I set a series of test files. The test data is GPS
data, and I was able to create a test file with exactly 65,536 lines and
another with 65,537 lines. The first one executes just fine (and very
quickly as you noted). The second one seems to immediately go the hourglass
mouse pointer, and I can't even Alt|Page Break to get out of it.

The ultimate goal is to figure out a way to either cut these huge files into
multiple file sizes Excel can handle, or bring in the overflowing data into
sequential worksheets within a single Excel file.

Anyway, all I'm doing now is trying to detect files that will be too big for
Excel.

Maybe there's a better method?

-gk-

"Dave Peterson" wrote in message
...
I ran that code against a giant text file that I have and changed the
msgbox to
debug.print and got this back:

1,499,022

Those 1.5 million lines were read in pretty quickly.

Printing the time before and after showed:
13:36:35
1,499,022
13:36:36

(about a second)

====
Are you sure that it's this portion of the code that's causing the
trouble?


39N 95W wrote:

Windows XP Pro SP2
Excel 2002 SP3

I am trying to detect if a txt or csv file has more than 65,536 lines
before
opening it. I've written this little tidbit for testing:

Sub testme()

Dim data As String
Dim fname As String
Dim c As Long

fname = "C:\911\_temp\test_data1.csv"

Open fname For Input As #1
c = 0
Do Until EOF(1)
Line Input #1, data
c = c + 1
Loop
Close #1

MsgBox c

End Sub

This works fine on files with less than or equal to 65,536 lines of data.
If I try the same code on a file with 65,537 lines or more then Excel
locks
up.

Is there any way around this? Why is Excel crashing? Should I bite the
bullet and learn to use Access?

Any and all suggestions welcome. Thanks!

-gk-

================================================== ======================
"The creative act is not the province of remote oracles or rarefied
geniuses but a transparent process that is open to everyone."
-Greg Kot in Wilco Learning How To Die-

--

Dave Peterson

--

Dave Peterson