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

arskiracer

New Member
Joined
Apr 3, 2013
Messages
18
I currently have the following code to load all file names and paths into my workbook:

Code:
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:

Code:
TempPath = Split(File, "\")

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

Code:
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:

Code:
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
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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