FILES Function in Excel to List Files in a Folder

[@paulsprunken]

Wow, a teacher that doesn't only give me the solution but also walks me step by step through the solution, allowing me to acquire some understand of funcxtions and vba; I like your style man, much appreciated

[@ExcelMoments]

Excellent feedback. Thank you so much

[@paulsprunken]

@@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!

[@paulsprunken]

@@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

[@ExcelMoments]

@@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

[@paulsprunken]

@@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!

[@GideonBotes]

Very good video, thank you!

[@ExcelMoments]

You are welcome

[@dustsettler4596]

Hello! Is there any way to extract the "Date modified" of the files we extract, perhaps in a second column?

[@ExcelMoments]

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

[@ExcelMoments]

@@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 😊😊)

[@LuisRiveraAlmeyda]

Thanks, it es really helpful, you are awesome.

[@ExcelMoments]

You are welcome once again

[@Arsenal_Legend234]

Nice one sir

[@ExcelMoments]

Thanks for the feedback, much appreciated

[@Football-jc9tn]

The Files do not come in order. Can u please help?

[@ExcelMoments]

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

[@Football-jc9tn]

@@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

[@ExcelMoments]

@@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

[@emmanuelmiller3034]

it only shows one document from the folder it doesn't show all documents

[@ExcelMoments]

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)