FILES Function in Excel to List Files in a Folder 

Excellent feedback. Thank you so much

@@ExcelMoments Question pls: my macro runs in error because of duplicate filenames (after trimming the original filenames). This is totally correct. My list of filenames has an ordernumber and a specific analysis document number associated with that order. I can have multiple orders that reference the same analysis document number and all I need is, after trimming off the ordernumbers, to only have a unique list of analysis document names. But the macro cannot handle the resulting obvious duplicate file names (either to be skipped or overwritten. Instead it runs into a vba macro error state. Any clues or code to add to handle this? Much appreciated!

@@ExcelMoments I got confused it is runtime error 58 file name exists. I tried to insert the line On Error Resume Next but it continues getting stuck in error. Hope you can guide me pls

@@paulsprunken What i may suggest ifs doing a FILE EXIST check prior to renaming, so if you know the end name after trimming is going to be 123.xlsx, you can check first if 123.xlsx is already a file in that folder, if it is, then you need to modify what your new name is, maybe with an _1,like say 123_1.xlsx. but the trick would be to check first if the new name you are proposing already exists before doing the rename or saving as the case may be

@@ExcelMoments Thanks for challenging me and withholding the code of the solution: I Managed with the following (modified from your original) code: Sub Renamefiles() Dim I As Long Dim strFileExists As String Dim strFileName As String For I = 5 To Range("D" & Rows.Count).End(xlUp).Row strFileName = Range("D" & I) strFileExists = Dir(strFileName) If strFileExists = "" Then Name Range("D" & I).Value As Range("E" & I).Value Else Range("A" & I) = "Duplicate" End If Next I End Sub I'm happy, thank you!

You are welcome

You are welcome once again

That's a good question. i may just add a short Function in VBA, Function Datemodified(path As String) Datemodified = Format(FileDateTime(path), "dd-mmm-yy hh:mm:ss Am/Pm") End Function So, i would use it like =Datemodified(B4), where B4 contains a fully qualified file path. That is one way

@@dustsettler4596 you can use Power Query to get a list of all those files and other associate properties. depending on your request, one can provide some guidance, but i definitely like batch files(takes me back in time 😊😊)

Thanks for the feedback, much appreciated

When you say not in order, what order are you expecting or do you require it in?

@@ExcelMoments So I have the files as 1,2,3,4,5 in my folder aligned with certain names. When I do the function it comes as 1,2,4,3,5 in excel which messes up the whole sequence

@@Football-jc9tn The 4-3 for example, any similarity or differences between those names and why Excel is not putting them in order. Worst case, you can do SORT(Transpose(Myfileslist),1,1) for ascending order. although, i expect FILES to get the list already sorted. I would like to see your specific example

That should not happen though Are you using Excel 365? if No, that may explain it. In the older versions, you have to use an INDEX to extract the components of the array(List of all files in folder)