Excel Export: Windows Explorer Folder Meta Data

[Mac Leod]

Hello,

This is my first post so I hope I do so correctly

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==> them 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

 

[Mac Leod]

I was able to acquire a solution to this issue:

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

 

[Matt C]

This works perfectly for me, however I have over 140 subfolders, each containing approx 20 files. Can the above code be re-worked so it will go through multiple folders and return a list of all files in order?

 

[Richard Quintal]

Is there any way to make this return information from the sub folders too?

 

[vletm]

Richard Quintal​

As You've read from Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
• Please post, new posts in the correct forums,
  • Your new post should open to VBA Macros
    
    This about eight years old thread is closed now.