Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows in text file by loading into array
Hello --
The app can allow no more than 32,000 data points (1 column of numbers) to be imported using a text file. [1] Is there a way to load the file into an array, count the records loaded, and warn the user if 32,000 has been exceeded? [2] Then, if not exceeded, the app would then put the array values into a column in a worksheet. Can anyone help with ideas or sample code for doing [1] and [2]? Thanks for any help. Larry Mehl mehl_at_cyvest.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows in text file by loading into array
Perhaps use the old I/O routines? ...
Open the file using 'Open' Pre-read using Input# into InputString i = 1 Do cells(i,1).value = InputString refresh InputString using Input# i = i + 1 until EOF or i 32001 Close the file using 'Close' "L Mehl" wrote in message ... Hello -- The app can allow no more than 32,000 data points (1 column of numbers) to be imported using a text file. [1] Is there a way to load the file into an array, count the records loaded, and warn the user if 32,000 has been exceeded? [2] Then, if not exceeded, the app would then put the array values into a column in a worksheet. Can anyone help with ideas or sample code for doing [1] and [2]? Thanks for any help. Larry Mehl mehl_at_cyvest.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows in text file by loading into array
Hi Larry,
See the following thread from last month: where a similar problem was posed: http://tinyurl.com/5hjvo --- Regards, Norman "L Mehl" wrote in message ... Hello -- The app can allow no more than 32,000 data points (1 column of numbers) to be imported using a text file. [1] Is there a way to load the file into an array, count the records loaded, and warn the user if 32,000 has been exceeded? [2] Then, if not exceeded, the app would then put the array values into a column in a worksheet. Can anyone help with ideas or sample code for doing [1] and [2]? Thanks for any help. Larry Mehl mehl_at_cyvest.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows in text file by loading into array
use a dataquery to import the text file. then look at recordcount. OR use this: Sub ReadTextStuff() 'Note as is dimmed as string. 'thus any imported data will be imported AS TEXT Dim h&, cnt&, data() As String Const FileName = "d:\mytext.txt" Const maxLines = 50 h = FreeFile If Dir(FileName) < "" Then ReDim data(1 To maxLines, 1 To 1) Open FileName For Input As #h While Not EOF(h) And cnt < maxLines cnt = cnt + 1 Line Input #h, data(cnt, 1) Wend If Not EOF(h) Then MsgBox "File is too long" ElseIf vbYes = MsgBox( _ "Imported " & cnt & " items from " & FileName & _ vbNewLine & "dump in activesheet?", vbYesNo) Then ActiveSheet.Cells(1).Resize(cnt) = data End If Close h Else MsgBox "File " & FileName & " not found", vbCritical End If End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam L Mehl wrote : Hello -- The app can allow no more than 32,000 data points (1 column of numbers) to be imported using a text file. [1] Is there a way to load the file into an array, count the records loaded, and warn the user if 32,000 has been exceeded? [2] Then, if not exceeded, the app would then put the array values into a column in a worksheet. Can anyone help with ideas or sample code for doing [1] and [2]? Thanks for any help. Larry Mehl mehl_at_cyvest.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows in text file by loading into array
Larry,
The following routine works for me: Function DoLineCount(txtFilepath) Dim FSO As Object Dim objTextFile As Object Dim fileText As String Dim rowBound As Integer Dim t As Integer Dim fileTextArray Dim newArray() Set FSO = CreateObject("Scripting.FileSystemObject") Set objTextFile = FSO.OpenTextFile(txtFilepath, 1) fileText = objTextFile.readAll fileTextArray = Split(fileText, vbCrLf) rowBound = UBound(fileTextArray) If rowBound + 1 32000 Then MsgBox "WARNING: File length exceeds 32000 lines" Exit Function Else ReDim newArray(rowBound, 0) For t = 0 To rowBound newArray(t, 0) = fileTextArray(t) Next ActiveWorkbook.Sheets(1).Range("A1").Resize(rowBou nd + 1, 1).Value = newArray End If objTextFile.Close Set objTextFile = Nothing Set FSO = Nothing End Function The first part of the routine uses the FileSystemObject to read the text into a variable, which is then converted to an array, fileTextArray, using the Split function and vbCrLf. The number of lines in the file is uBound(fileTextArray) + 1. If the file passes the 32000 line test, a second array, newArray, is created. Unfortunately, fileTextArray can't be written directly to the worksheet (it's more like a list), so it's read into newArray. Once that's done, though, the array can be passed to the worksheet in a single step. Hope this helps, Stan Scott New York City "L Mehl" wrote in message ... Hello -- The app can allow no more than 32,000 data points (1 column of numbers) to be imported using a text file. [1] Is there a way to load the file into an array, count the records loaded, and warn the user if 32,000 has been exceeded? [2] Then, if not exceeded, the app would then put the array values into a column in a worksheet. Can anyone help with ideas or sample code for doing [1] and [2]? Thanks for any help. Larry Mehl mehl_at_cyvest.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count rows in text file by loading into array
Thank you all for the suggestions.
We went with the array method. Larry "L Mehl" wrote in message ... Hello -- The app can allow no more than 32,000 data points (1 column of numbers) to be imported using a text file. [1] Is there a way to load the file into an array, count the records loaded, and warn the user if 32,000 has been exceeded? [2] Then, if not exceeded, the app would then put the array values into a column in a worksheet. Can anyone help with ideas or sample code for doing [1] and [2]? Thanks for any help. Larry Mehl mehl_at_cyvest.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count how many different text values in an array. | Excel Worksheet Functions | |||
Need help with array formula to count rows w/ mult.criteria | Excel Programming | |||
Loading Excel Array from VB Array Faster | Excel Programming | |||
Loading 3 Dimensional Array | Excel Programming | |||
Loading text file | Excel Programming |