Excel Export of Windows Explorer Meta Data

[emacleod]

I have a need to export the contents of a Windows Folder Directory to an Excel Spreadsheet. This folder is not on my local drive, it is on a file server. I am using Excel 2010 and have the Power Query add-in which almost does exactly what I need with the exception of bringing in the meta data related to the 'Owner' column. The function for this in Power Query can be found under the External Data portion of its ribbon: From File==> From Folder==> then selecting the path of the folder itself. Again, this is brilliant! It queries the data and pulls it into an Excel Table, but is missing the one attribute I desperately need, 'Owner'. I am able to see the query, but have been unable to alter it to include the 'Owner' meta data attribute.

I've looked into third-party tools which do not cut it and have tried cmd line batch files which do not do the job, either. Although I'm good with Excel, I do not have the skills to use VBA--providing that would be a solution to my problem.

As you know, you can setup your view of each Windows Folder differently with the long list of different attributes which goes beyond the File Name, File Type, Size, Date Modified... The view that I have for the particular folder I need an Excel spreadsheet on has these attributes in this order: Name, Date Modified, Size, Type, Author & Owner.

Let me know if there is any additional information you would need. I deeply appreciate any assistance which can be offered to solve my problem.

Thank you again,

~emacleod

 

[John_w]

You can get the Owner of a file (along with the other properties you list) using the Microsoft Shell Controls and Automation library - CreateObject("Shell.Application") is the late binding version. See http://www.mrexcel.com/forum/excel-questions/691130-retrieve-image-dimensions-if-exists.html for example code.

The 2nd argument to GetDetailsOf is a number specifying the file property you want to retrieve and for a particular property it can vary depending on the MS operating system. Try 8 for Owner. If this is wrong, there is code in that thread which can determine the number to use to get the Owner property.

 

[brawnystaff]

Try the following:

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(0, 1, 2, 3, 10, 20)
    '0=Name, 1=Size, 2=Item Type, 3=Date Modified, 10=Owner, 20=Authors
  
  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

Note that I am now using Windows 10 as my operating system, and the values specified in the array can give different results depending on the OS. If not giving the correct element, then let me know what OS you are using.

 

[emacleod]

Brilliant! Works Perfectly! Thank you both so much!

~emacleod