List files or folders using .bat file

Tested in Excel 365 (16.8730.2046) 64-bit. All links open in new tab.

 
 
  1. Sub ListFilesOrFolders(Optional IsFile = True)
  2.    
  3.     Dim Param As String
  4.     Dim RetVal, f, MyHeader, ResultSheet
  5.     Dim MyFolder, MyDoc
  6.     Dim strFile, i, arrData
  7.    
  8.     MyFolder = PickFileOrFolder(0)
  9.     MyDoc = Environ("USERPROFILE") & "\Documents\"
  10.     ToggleExcelFunctionalities (False)
  11.    
  12.     Param = ""
  13.     If IsFile Then
  14.         Param = "-"
  15.         MyHeader = "File"
  16.         ResultSheet = "List of Files"
  17.     Else
  18.         MyHeader = "Folder"
  19.         ResultSheet = "List of Folders"
  20.     End If
  21.    
  22.     f = FreeFile
  23.     Open MyDoc & "List.bat" For Output As #f
  24.     Print #f, "cd " & MyFolder
  25.     Print #f, "dir " & MyFolder & "/s/b/a" & Param & "d>" & MyDoc & "Result.txt"
  26.     Close f
  27.    
  28.     RetVal = Shell(MyDoc & "List.bat", vbHide)
  29.     'needs a pause to allow windows refresh
  30.    Application.Wait (Now() + TimeValue("0:00:03"))
  31.    
  32.     ' transfer the result to sheet
  33.     f = FreeFile
  34.     Open MyDoc & "Result.txt" For Input As #f
  35.     strFile = input(LOF(f), #f)
  36.     Close f
  37.    
  38.     arrData = Split(strFile, vbCrLf)
  39.     If SheetExists(ResultSheet) Then Sheets(ResultSheet).Delete
  40.     Sheets.Add
  41.     ActiveSheet.Name = ResultSheet
  42.     Range("A1").Value = MyHeader
  43.     Range("B1").Value = "Ignore"
  44.    
  45.     For i = 0 To UBound(arrData) - 1
  46.         Cells(i + 2, 1).Value = arrData(i)
  47.     Next i
  48.    
  49.     Kill MyDoc & "List.bat"
  50.     Kill MyDoc & "Result.txt"
  51.     ToggleExcelFunctionalities (True)
  53. End Sub

PickFileOrFolder function is here.

SheetExists function is here.

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.