Array not erasing

Atroxell

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

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top