Hi,
I cobbled together some code from multiple sources that will process text files.
Since we have multiple files in a folder that need to be read, I built the process to read all of the filenames in a folder into an array (DirectoryListArray()) that I use as a master list of files, then iterate through the array to process each file by name.
Sometimes the files come across as multi-line files, and sometimes they are a single long line of text. So I have to put in the test to see if the array containing records is a single array index or multiple. If it's single, the index is transferred to "TxtLn", then split() back into the ar835 array using tilde as the splitter. If there's multiple indices being used, it jumps over to the data processing block of code.
The problem I am having is that, despite my best attempts, when I am done with ar835() and want to empty it to proceed to the next file, Erase does not do the job of clearing the array. Instead, on the second iteration the array now has index 0 and 1. The third pass has 0, 1 and 2, and so on. This causes problems with my test block after the text file is closed.
Additionally, arRec() is not being erased. So if a file has 10k records, the next file iteration will start at arRex(10001) and proceed from there. I can have 200+files in a folder, so the load for 10k * 200 files gets pretty nasty as things progress. The last full run I executed took over 10 hours.
Can anyone tell me why this is happening?
I cobbled together some code from multiple sources that will process text files.
Since we have multiple files in a folder that need to be read, I built the process to read all of the filenames in a folder into an array (DirectoryListArray()) that I use as a master list of files, then iterate through the array to process each file by name.
Sometimes the files come across as multi-line files, and sometimes they are a single long line of text. So I have to put in the test to see if the array containing records is a single array index or multiple. If it's single, the index is transferred to "TxtLn", then split() back into the ar835 array using tilde as the splitter. If there's multiple indices being used, it jumps over to the data processing block of code.
The problem I am having is that, despite my best attempts, when I am done with ar835() and want to empty it to proceed to the next file, Erase does not do the job of clearing the array. Instead, on the second iteration the array now has index 0 and 1. The third pass has 0, 1 and 2, and so on. This causes problems with my test block after the text file is closed.
Additionally, arRec() is not being erased. So if a file has 10k records, the next file iteration will start at arRex(10001) and proceed from there. I can have 200+files in a folder, so the load for 10k * 200 files gets pretty nasty as things progress. The last full run I executed took over 10 hours.
Can anyone tell me why this is happening?
Code:
For Counter = 0 To UBound(DirectoryListArray) ' Loop through filename array
strFileToOpen = DirectoryListArray(Counter) ' Pick an index
' Get the shortname (no extension) of the filename.
srcShortName = Left(strFileToOpen, (InStrRev(strFileToOpen, ".", -1, vbTextCompare) - 1))
' Open the user selected text file.
MyFile = FreeFile()
Open strFileToOpen For Input As MyFile
' Load the text file into an array for processing.
While Not EOF(MyFile)
ReDim Preserve ar835(x) ' Preserve the Array and add an index
Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=MyFile]#MyFile[/URL] , ar835(x) ' Read line into variable.
x = x + 1 ' increment array count
Wend
' Close the text file.
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]
' Test to see if the text was copied into a single array index by finding the counting indices in the array
If UBound(ar835) = 0 Then
' If the index is minimal, copy the single array index into a text variable.
txtLn = ar835(0)
' Empty the array variable.
Erase ar835
' Split the long textline value into individual array indices based on the presence of a tilde.
ar835() = Split(txtLn, "~")
End If
' Message the user
Application.StatusBar = "Processing " & DirectoryListArray(Counter) & "... File #" & Counter + 1 & " of " & UBound(DirectoryListArray) + 1
DoEvents
recStart = IIf(Range("A1000000").End(xlUp).Row - 1 = 0, 2, Range("A1000000").End(xlUp).Row - 1)
rowID = IIf(recStart = 2, 2, recStart + 1)
' Here is where we loop through the file and generate the records in a temporary worksheet/workbook.
For y = UBound(ar835) To 0 Step -1
arRec = Split(ar835(y), "*")
Run defs
Erase arRec
Next y
' Calculate the record count from the new file
recCnt = Range("A1000000").End(xlUp).Row - 1
recCnt = IIf(Counter > 0, recCnt - recStart, recCnt) ' something goofy here
' Add the file information to the tracking worksheet in the macro workbook.
ThisWorkbook.Sheets(hmRecSheet).Activate
strRow = Range("A1048576").End(xlUp).Row + 1
If ActiveSheet.Name <> hmRecSheet Then Sheets(hmRecSheet).Activate
Cells(strRow, 1) = Counter ' Index # of filename array
Cells(strRow, 2) = srcShortName ' Name of file processed
Cells(strRow, 3) = recCnt ' Number of records processed in file
Cells(strRow, 4) = Now()
Cells(strRow, 4).NumberFormat = "h:mm:ss"
ActiveSheet.UsedRange.EntireColumn.AutoFit
If Counter = UBound(DirectoryListArray) Then
Cells(2, 7) = "End": Cells(2, 8) = Now()
End If
' Return to the output workbook to continue processing files.
Workbooks(strDestwkBk).Sheets(strRecSheet).Activate
Erase ar835
Next Counter
