Is there a way to retrieve file metadata from Sharepoint using VBA in excel without having to open each file?

Question

My goal is to present my users a method of previewing the details of files stored on Sharepoint from within a VBA userform in excel. So far I am able to retrieve a list of files using Scripting.FileSystemObject.

I'm aware I could use ContentTypeProperties to access the information I want but this appears to be dependent on opening each file. Is there some other method I could use which would allow me to access the metadata without having to open each file?

I'm not an avid user of Sharepoint so please excuse my use of terminology.

I'm walking in to uncharted territory in respect to my own understanding here so any help or pointers to further reading would be much appreciated.

Thank you!

edit: Existing code relying on Scripting.FileSystemObject to retrieve filename only.

Private Sub UserForm_Activate()
Dim spURL As String
Dim oFile As Object, oFSO As Object, oFldr As Object, oFiles As Object, oDSO As Object
Dim ext As String, title As String
spURL = **sharepoint url**

Set oFSO = CreateObject("Scripting.Filesystemobject")
Set oFldr = oFSO.getfolder(spURL)
Set oFiles = oFldr.Files

For Each oFile In oFiles
    If InStr(1, ext, "xls") Then
        Me.cboFiles.AddItem oFile.Name
    End If  
Next
Set oFSO = Nothing
Set oFldr = Nothing
Set oFiles = Nothing
End Sub

Now lets say I wanted to get document type, which is a custom field in the document properties when the files are checked in to sharepoint, I'm aware I could do something like:

set wb = workbooks.open(spURL & oFile.Name)
debug.print wb.ContentTypeProperties.Item(x).Value

(Where Item(x) is relative to the field I need to pull information from) However, what I'm looking to determine is if there is an alternative method which doesn't require the workbook to be open.

I probably should have thought to ask this as part of my original question but where custom fields are added in to the document properties, is that data stored in the file or on Sharepoint? Would that even make a difference? There's one custom field I'm after in particular called "Doc Type", this could contain values such as "Problem Statement" or "CAR". Access to any of the additional fields would be useful though. — chinrub
You are going to need to do some research on your own and develop some code attempting to do at least part of what you're after. When you have code that throws an error or doesn't give you what you expect then post that here. That is what SO is really here for. — SmileyFtW
I get what you're saying but I didn't really think that posting my code so far would have brought benefit as I mentioned it's entirely reliant on downloading and opening the file so figured any solution would utilise some different method. That being said, I'll update my question with the code I've used thus far to retrieve the filename. If you've any thoughts, they'd be gratefully received — chinrub

chinrub chinrub · Accepted Answer · 2020-05-08T21:53:02

Digging in to things a little deeper I have found the following on http://www.cpearson.com/excel/docprop.aspx: "VBA doesn't directly support reading properties from closed files. However, Microsoft has made available for free download a DLL file called DSO OLE Document Properties Reader 2.1, or simply DSOFile.dll. With this DLL, you can read both BuiltIn and Custom properties from a OLE file, such as an XLS workbook."

Is there a way to retrieve file metadata from Sharepoint using VBA in excel without having to open each file?

1 Answers