How to determine the length of a .prn file.

[Richard Buttrey]

One of my VBA macros opens and then imports a .prn or .txt file into
an excel workbook.

Sometimes the number of records in the file exceed the maximum rows
permitted by a single Excel worksheet.

How can I test for the number of rows in the file before opening,
parsing and copying it into my workbook? If it exceeds say 65500 rows
I'd like to pop up a message and then exit the Sub.

TIA

Richard Buttrey

 

[Nikos Yannacopoulos]

Richard,

Check out this sample code:

Sub Count_Lines()
lcount = 0
Open "pathandfilename.ext" For Input As #1
Do Until EOF(1)
Line Input #1, vDummy
lcount = lcount + 1
If lcount > 65536 Then
MsgBox "File contains over 65536 lines", vbCritical
Close #1
Exit Sub
End If
Loop
Close #1
MsgBox "Line count " & lcount & " is within Excel limit", vbInformation
End Sub

Stupid as it may seem,. it opens the file and counts the lines one by one,
yet it counts 65536 lines in a fragment of a second on a fairly fast PC.
Modify it to suit your needs as to the action, depending on the count
(instead of the message box).

HTH,
Nikos

 

[Jamie Collins]

(e-mail address removed) (Richard Buttrey) wrote ...

One of my VBA macros opens and then imports a .prn or .txt file into
an excel workbook.

Sometimes the number of records in the file exceed the maximum rows
permitted by a single Excel worksheet.

How can I test for the number of rows in the file before opening,
parsing and copying it into my workbook?

Try this:

Public Function CountTextfileRows( _
ByVal Path As String, _
ByVal Filename As String _
) As Long

Dim oConn As Object
Dim oRs As Object
Dim strSql As String

Set oConn = CreateObject("ADODB.Connection")

strSql = "SELECT COUNT(*)" & _
" FROM " & Filename
With oConn
.CursorLocation = 3 ' adUseClient
.ConnectionString = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=Text"
.Open

Set oRs = .Execute(strSql)

CountTextfileRows = oRs.Fields(0).Value

oRs.Close
.Close

End With

End Function


Jamie.

--

 

[Richard Buttrey]

One of my VBA macros opens and then imports a .prn or .txt file into
an excel workbook.

Sometimes the number of records in the file exceed the maximum rows
permitted by a single Excel worksheet.

How can I test for the number of rows in the file before opening,
parsing and copying it into my workbook? If it exceeds say 65500 rows
I'd like to pop up a message and then exit the Sub.

TIA

Richard Buttrey

Thanks to both Nikos and Jamie for your prompt replies.

I'll be trying your suggestions out in the next day or two.
Kind regards

Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Jamie Collins]

Richard Buttrey wrote ...

Thanks to both Nikos and Jamie for your prompt replies.

I'll be trying your suggestions out in the next day or two.

Mine is additionally a hint as to how you might filter the rows for import.

Jamie.

--

 

[Nikos Yannacopoulos]

Richard,

Check out this sample code:

Sub Count_Lines()
lcount = 0
Open "pathandfilename.ext" For Input As #1
Do Until EOF(1)
Line Input #1, vDummy
lcount = lcount + 1
If lcount > 65536 Then
MsgBox "File contains over 65536 lines", vbCritical
Close #1
Exit Sub
End If
Loop
Close #1
MsgBox "Line count " & lcount & " is within Excel limit", vbInformation
End Sub

Stupid as it may seem,. it opens the file and counts the lines one by one,
yet it counts 65536 lines in a fragment of a second on a fairly fast PC.
Modify it to suit your needs as to the action, depending on the count
(instead of the message box).

HTH,
Nikos