Array not erasing

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
417
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?

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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,188
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Erase empties an array, it does not resize it. Either use variants instead and assign Empty to them when done, or use a Redim without Preserve to resize them as required.
 

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
417
Thanks Rory! Your direction was perfect, as always...

I also discovered that my "x" variable was not being reset so the counter kept incrementing every time a file was loaded. so I added 2 lines of insurance for the loop:

Code:
            ' Open the user selected text file.
            MyFile = FreeFile()
            Open strFileToOpen For Input As MyFile
            
[B][COLOR=#FF0000]            ReDim ar835(0)[/COLOR][/B]
[B][COLOR=#FF0000]            x = 0[/COLOR][/B]


            ' Load the text file into an array variable 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]
 

Forum statistics

Threads
1,078,525
Messages
5,340,974
Members
399,401
Latest member
poiter54

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top