VB Script to open multiple folders on HD & provide file property details.

[Laura Boyd]

Hello all...


I am creating a database spreadsheet for files I have on my hard drive. I found the script I want, and it works, but there are 2 functions I would like to have it do to smooth the process.


What this code does is pulls all the files in 1 folder & gives me the data from the "Properties/Details" tab of the file. I found the information I want it to provide & its working like a charm. However the issue is, I need it to open the folders within the folder I selected.


Example:
Files I want are in: G:/#
G:/A
G:/B
G:/C
G:/D
and so on until G:/Z


If I select G:/ it just provides the folder names and not any files within those folders. If I select G:/# I get the files I wanted in that folder, but it won't pull from the other folders. So ultimately, I THINK (I am a self teacher of excel & am FAR from a pro, so you all please help if there is an easier process) what I need to do is add a code that will automatically selected the G drive when I run the macro (right now it has a popup window asking for the folder location) and then the code will repeat over and over to automatically go through each folder running the script until there is no more folders to open.


I hope this made sense, I tried to be as detailed as possible but if someone would like to see my excel sheet I am working with, I won't mind sharing.


Code is:

Option Explicit
Sub GetDetails()
  Dim oShell As Object
  Dim oFile As Object
  Dim oFldr As Object
  Dim lRow As Long
  Dim iCol As Integer
  Dim vArray As Variant
  vArray = Array(1, 4, 9, 13, 15, 16, 18, 21, 24, 27)  
  Set oShell = CreateObject("Shell.Application")
  lRow = 1
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select the Folder..."
    If .Show Then
      Set oFldr = oShell.Namespace(.SelectedItems(1))
      With oFldr
        For iCol = LBound(vArray) To UBound(vArray)
          Cells(lRow, iCol + 1) = .getdetailsof(.items, vArray(iCol))
        Next iCol
        For Each oFile In .items
          lRow = lRow + 1
          For iCol = LBound(vArray) To UBound(vArray)
            Cells(lRow, iCol + 1) = .getdetailsof(oFile, vArray(iCol))
          Next iCol
        Next oFile
      End With
    End If
  End With
End Sub

Thanks for any help provided in advance

 

[Fluff]

Have a look at this & see if it helps
https://www.mrexcel.com/forum/excel...les-subdirectories-directory.html#post2228984

 

[Laura Boyd]

I have gotten the solution thanks to another forum. I wanted to try and mark this thread as [SOLVED] but I can't edit to do so.

In the event someone else is looking to do this in the future... the answer was provided here:
https://www.excelforum.com/excel-pr...-on-hd-and-provide-file-property-details.html