VBA - list file names of the given extensions in the given folder and its all subfolders down to last level

Question

Hello I am trying to get specific files by extension from multiple folders and their subfolders and I am having trouble with this task. WHat I have so far is:

Sub ListFiles()

'Declare variables
Dim i As Long

Dim fileName As Variant
fileName = Dir("J:\BREAKDOWNS\*.PDF")

i = 2
While fileName <> ""
Cells(i, 1).Value = Left(fileName, Len(fileName) - 4)
i = i + 1
fileName = Dir
Wend

End Sub

Could someone, please, help?

P.S.

What I need and what I got so far is

folder = "J:\BREAKDOWNS\*.PDF"
    
sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & folder & ibox & """ /s /a /b").StdOut.ReadAll, vbCrLf)
     
Sheets(1).Cells(2, 1).Resize(UBound(sn) + 1) = Application.Transpose(sn)

But it returns teh full address, where I need just teh filename without extension even.

yes, the format of the file, aka .pdf or . xlsx. I am looking to get a list of all .pdf files from multiple folders and their subfolders as opposed to one folder and it's subfolders, aka from drive C: and drive D: — Eduards, Aug 17 '21 at 06:59
So what trouble are you having? Did it not work or an error occured (if so, which line and what message?) — Raymond Wu, Aug 17 '21 at 07:01
https://stackoverflow.com/questions/68471751/searching-for-unique-value-and-call-sub-if-not-go-to-next-cell/68472552#68472552 has an example function you can use to return a collection of matching file objects - see `GetMatches` — Tim Williams, Aug 17 '21 at 07:03
Well too many problems, the examples online mostly are rubbish or works for only one folder and one level or gets all files but can't specify the extension etc. So the closest I got I provided in OP — Eduards, Aug 17 '21 at 07:03
For reference, [Loop Through All Subfolders Using VBA](https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba) and [Cycle through sub-folders and files in a user-specified root directory](https://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory) both using `Scripting.FileSystemObject`... Answer below uses `wscript.shell` with option to select multiple files extensions. — Naresh, Aug 22 '21 at 16:48

Naresh · Accepted Answer · 2021-08-19T10:34:29.640

1

Try below code which lists file names of the given extensions in the given folder and its all subfolders down to last level on a newly added sheet

Credits: https://www.youtube.com/watch?v=ddA2_SOaq14

Option Explicit

Sub List_File_Names()
'This macro lists file names of the given extensions in the given folder and _
    its all subfolders down to last level on a newly added sheet
'https://stackoverflow.com/questions/68812888/ _
vba-list-file-names-of-the-given-extensions-in-the-given-folder-and-its-all-su

'reference - https://www.youtube.com/watch?v=ddA2_SOaq14
Dim FNameStr As String, ExtStr As String, ExrArr, sn, nWs As Worksheet
Dim regex As Object, mc As Object, f As String, i As Long
Dim fldr As FileDialog

Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
fldr.Show
f = fldr.SelectedItems(1)
f = f & "\"

Set regex = CreateObject("VBScript.regexp")
regex.ignorecase = False
regex.Global = True

ExtStr = InputBox("Enter extensions of filesnames to be listed delimited by comma", _
       Default:=".xlsx,.pdf")
ExrArr = Split(ExtStr, ",")

FNameStr = ""

If ExtStr <> "" Then
    For i = LBound(ExrArr) To UBound(ExrArr)
    FNameStr = FNameStr & (CreateObject("wscript.shell").exec("cmd /c Dir /s /b """ & _
                f & """ | findstr """ & ExrArr(i) & """ ").stdout.readall)
    Next i
Else
    FNameStr = FNameStr & (CreateObject("wscript.shell").exec("cmd /c Dir /s /b """ & _
                f & """").stdout.readall)
End If

regex.Pattern = "\S[^\n]+\\" 'to remove folder names from full file name
sn = Split(Replace(regex.Replace(FNameStr, ""), vbCrLf, "|"), "|")
Set nWs = Worksheets.Add(Before:=Sheets(1))
nWs.Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)

End Sub

edited Aug 19 '21 at 10:34

answered Aug 17 '21 at 07:26

Naresh

2,984
2
9
15

2

With if condition you can add only pdf and xls file names in the cells... Like .. `If Right(oFile.Name, 3) = "PDF" Or Right(oFile.Name, 4) = "XLSX" Then ws.Cells(i, 1).Value = oFile.Name i = i + 1 End If` – Naresh Aug 17 '21 at 07:28
1

In your first answer tehreš an automation error on line "ws.Cells(i, 1).Value = oFile.Name" – Eduards Aug 17 '21 at 07:56
is that folder empty then?.. Still it works fine with my laptop. – Naresh Aug 17 '21 at 07:59
1

of Corse not, but even if it would, aka. one of the subfolders is empty... it shouldn't break the code – Eduards Aug 17 '21 at 08:01
2

Please try second code only after inserting a new sheet at first position. Otherwise, it will overwrite the existing first sheet. – Naresh Aug 17 '21 at 08:03
2

I do want to overwrite values btw – Eduards Aug 17 '21 at 08:17
1

How do I specify the address to set one, I don't need browse for folder at all – Eduards Aug 17 '21 at 08:29
Also that code imports full address, but I need specifically just the file name without the extension even, like in my OP I have "Left(fileName, Len(fileName) - 4)" to trim the extension symbols in filename if I have just the filename in teh cell – Eduards Aug 17 '21 at 08:31
1

in the second code .. f = "F:\Folder" & "\" .. comment out lines before the last f= statement ... and path can easily be removed with replacing *\ with nothing("") – Naresh Aug 17 '21 at 08:33
Why? I am talking about set fldr and fldr.Show. I don't need that... I need a set folder address always – Eduards Aug 17 '21 at 08:41
2

Ok so I figured out the set folder location thing which shortened the code by half practically. Now how would you write "sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & F & ibox & """ /s /a /b").StdOut.ReadAll, vbCrLf)" to have only filenames without extensions – Eduards Aug 17 '21 at 08:50
1

Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/236079/discussion-between-eduards-and-naresh). – Eduards Aug 17 '21 at 09:01
2

The cmd list method tends to return #N/A. Why could be the problem with that? – Eduards Aug 18 '21 at 06:45
1

@Eduards .. Oops!! I'm not that good at cmd. I think better to tag shell here. Someone will respond with a better answer. It would be good to learn that. – Naresh Aug 18 '21 at 06:49
2

alright will do, but also what's the deal with cmd error when using address with non English letters, like in my language we have letters like "ā" "ē" etc. which I assume is the issue, I mean other methods don't seem to mind that – Eduards Aug 18 '21 at 06:53
1

I assume both issues is because of the same issue – Eduards Aug 18 '21 at 06:59
@Eduards Yes, I thought so while reading your comment in the chat. – Naresh Aug 18 '21 at 07:18
Any suggestions? – Eduards Aug 18 '21 at 07:32
Not really on that. But, I was just just thinking could that FSO loop be made to drill to the last subfolder level with going back (goto statement) to the parent folder level and starting again loop for the second subfolder.. So that, cmd procedure could be avoided. But need to scratch brain (not just head) for that I guess :) – Naresh Aug 18 '21 at 07:36
2

Ha Ha well I like cmd method as it loads files list almost instantaneously even when i have 200k+ items. The FSO method in my experience takes some time – Eduards Aug 18 '21 at 07:42
1

Will try it out a little later today, friend – Eduards Aug 20 '21 at 07:25

VBA - list file names of the given extensions in the given folder and its all subfolders down to last level

1 Answers1