How to "split" and store the highest array item in a single line of code

[arskiracer]

I currently have the following code to load all file names and paths into my workbook:

For Each File In fld.Files        
        DoEvents                                        'allow other processes to execute
        TotalFileCount = TotalFileCount + 1     'increment file counter
        TempPath = Split(File, "\")                  'grab file name instead of entire path
        
        Worksheets(OutputSheet).Cells(OutputRow, 1).Value = TempPath(UBound(TempPath))
        Worksheets(OutputSheet).Cells(OutputRow, 2).Value = File
        
        OutputRow = OutputRow + 1
        Application.StatusBar = "Elapsed Time: " & Format(Now - StartTime, "hh:mm:ss") _
        & " Searching Directories... " & TotalFileCount & " files found so far"

Next File

I have about 60,000 file names and paths that need to be written into the outputsheet. The above code is executed recursively for all the directory locations that I want to search. I would like to make the code faster and the only thing I can think of is to combine this "Split" line:

TempPath = Split(File, "\")

with this line that writes the file name to the spreadsheet:

Worksheets(OutputSheet).Cells(OutputRow, 1).Value = TempPath(UBound(TempPath))

This is as close as I can get, but this would only return the number of items, not the actual file name:

Worksheets(OutputSheet).Cells(OutputRow, 1).Value = Ubound(Split(File, "\"))

Anyone have any ideas about how I can combine these two lines? If anyone sees any other ways to improve the efficiency of my code, please let me know. Thank you for the help,

Adrian

 

[Rick Rothstein]

Processing 60,000 filenames is going to take some time to do no matter what you do with your code, but I think that DoEvents statement is probably really killing performance... it takes time for the program to yield control and for the operating system to process any pending events... and you are doing that 60,000 times. I would put in a counter, increment it by 1 and when the counter hit (maybe) 100, reset it to 0 and execute the DoEvents. You will have to play with the number 100 to see how big you can make it before you will not be able about to break the loop (that is one reason why its there, right?) in a "snappy" enough manner.

 

[arskiracer]

I tried your suggestion and it reduced my execution time by about 15% (I had some DoEvents in other functions that I was able to apply that to as well). Thank you Rick

 

[pgc01]

Hi Adrian

If I understand correctly the title of the thread should be: Getting the text after the last "\".
This has not necessarily to do with arrays, that are just a tool that you used.

If that is right, if the pathname of the files includes a "\", you could try something like:

Worksheets(OutputSheet).Cells(OutputRow, 1).Value = Mid(objFile.Name,InStrRev(objFile.Name,"\")+1)

Remarks:

- I did not use File as the name for the loop variable because File is the name of an object type.
- I know that the default property of a File object is its name, but I prefer to make it explicit, therefore the objFile.Name