Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine the length of a .prn file.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine the length of a .prn file.
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 "Richard Buttrey" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine the length of a .prn file.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine the length of a .prn file.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine the length of a .prn file.
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to determine the length of a .prn file.
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 "Richard Buttrey" wrote in message om... 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 "Richard Buttrey" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dragging values down a range using a colum to determine its length | Excel Worksheet Functions | |||
Determine Excel file version with no file extension. | Excel Discussion (Misc queries) | |||
Calculate Months to Determine Length of Service | Excel Worksheet Functions | |||
How to determine the arc length in Excel? | Excel Discussion (Misc queries) | |||
Determine maximum length of a page | Excel Programming |