Read item titles from SharePoint Document Library into Array using Excel VBA

Question

I need to read all the item titles for all the documents in a SharePoint document library directly into an Array using Excel VBA. I can't seem to successfully use FileSystemObject and I do not want to map the document library to a drive letter as the macro will be distributed and widely used.

• The SharePoint site has an https address
• I have looked at this thread about referencing scrrun.dll but it does not work because I cannot change the trust settings on my local domain
• This thread looked promising, but again it seems to use FileSystemObject which might be my hang up.
• This thread on the SharePoint stackexchange site works well for reading in a list of files as a worksheet object, but I don't know how it could be adapted to be pushed directly into an array.
• I tend to receive Error 76 "Bad Path", but I am easily able to execute on local (C:) files.
• I have tried using a WebDAV address - like the answer I gave to this thread - but it too encounters a "Bad Path" error.

There must be a way to read in the contents of a SharePoint document library directly into an array that does not violate my local security policies and doesn't depend upon an excel worksheet.

Answer 1

Ok I am going to self answer. I'm not 100% thrilled with my solution, but it does suffice within my constraints. Here are the high level points:

• Use VBA to create BAT files that have the "Net Use" command within them.
• Reference the WebDAV address of the document library and find an available drive letter
  • I doubt that any of my users already have 26 mapped drives...).
• Once the document library is mapped it can be iterated through using FileSystemObject commands and the item titles can be loaded into a two dimensional array.
• The code will have to be modified to allow for 3 the listing of subfolders
  • The location of the file count in the ListMyFiles sub would have to be changed or another dimension would have to be added to the array.

Here is the code - I will try to credit all Stack solutions that were integrated into this answer:

 Private Sub List_Files()
    Const MY_FILENAME = "C:\BAT.BAT"
    Const MY_FILENAME2 = "C:\DELETE.BAT"

    Dim i As Integer
    Dim FileNumber As Integer
    Dim FileNumber2 As Integer
    Dim retVal As Variant
    Dim DriveLetter As String
    Dim TitleArray()

    FileNumber = FreeFile
     'create batch file

    For i = Asc("Z") To Asc("A") Step -1
    DriveLetter = Chr(i)
    If Not oFSO.DriveExists(DriveLetter) Then
        Open MY_FILENAME For Output As #FileNumber
        'Use CHR(34) to add escape quotes to the command prompt line
    Print #FileNumber, "net use " & DriveLetter & ": " & Chr(34) & "\\sharepoint.site.com@SSL\DavWWWRoot\cybertron\HR\test\the_lab\Shared Documents" & Chr(34) & " > H:\Log.txt"
        Close #FileNumber
      Exit For
    End If
  Next i

     'run batch file
    retVal = Shell(MY_FILENAME, vbNormalFocus)

     ' NOTE THE BATCH FILE WILL RUN, BUT THE CODE WILL CONTINUE TO RUN.
     'This area can be used to evaluate return values from the bat file
    If retVal = 0 Then
         MsgBox "An  Error Occured"
        Close #FileNumber
        End
    End If

'This calls a function that will return the array of item titles and other metadata
    ListMyFiles DriveLetter & ":\", False, TitleArray()

    'Create code here to work with the data contained in TitleArray()

    'Now remove the network drive and delete the bat files
    FileNumber2 = FreeFile

    Open MY_FILENAME2 For Output As #FileNumber2
    Print #FileNumber2, "net use " & DriveLetter & ": /delete > H:\Log2.txt"
    Close #FileNumber2

     retVal = Shell(MY_FILENAME2, vbNormalFocus)
     'Delete batch file
    Kill MY_FILENAME
    Kill MY_FILENAME2

End Sub

Here is the function that will read through the directory and return the array of file information:

Sub ListMyFiles(mySourcePath As String, IncludeSubFolders As Boolean, TitleArray())
    Dim MyObject As Object
    Dim mySource As Object
    Dim myFile As File
    Dim mySubFolder As folder
    Dim FileCount As Integer
    Dim CurrentFile As Integer
    'Dim TitleArray()
    Dim PropertyCount As Integer
    CurrentFile = 0
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)

    FileCount = mySource.Files.Count
    ReDim TitleArray(0 To FileCount - 1, 4)

    'On Error Resume Next
    For Each myFile In mySource.Files
        PropertyCount = 1
        TitleArray(CurrentFile, PropertyCount) = myFile.Path
        PropertyCount = PropertyCount + 1
        TitleArray(CurrentFile, PropertyCount) = myFile.Name
        PropertyCount = PropertyCount + 1
        TitleArray(CurrentFile, PropertyCount) = myFile.Size
        PropertyCount = PropertyCount + 1
        TitleArray(CurrentFile, PropertyCount) = myFile.DateLastModified
        CurrentFile = CurrentFile + 1
    Next

    'The current status of this code does not support subfolders.
    'An additional dimension or a different counting method would have to be used
    If IncludeSubFolders = True Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True, TitleArray())
        Next
    End If
End Sub

Thank you to Chris Hayes for his answer to find empty network drives; thank you to Kenneth Hobson on ozgrid for his expanded answer on listing files in a directory. The rest of the code is ancient and I dredged it out of a folder I last touched in 2010.