How to open a SharePoint-List with Excel-VBA

Question

I have the following idea!

1. First, connect my Excel with a SharePoint, to get a SharePoint-List.

2. Second, iterate through every item of the list and open them one by one.

3. In every list-item is a library with different files in it. Such as Word, Excel, etc.

4. I need to count how many list-items are in the list and how many files are in every list-item.

If that's working, then...

5. Open the right Excel file in the list-item of a SharePoint. Something like '%activities% in the name of an excel file.

6. Count the lines in this Excel file and copy the data into a new Excel file.

So in the end we would have a new Excel file with several sheets from different excel files. Is that even possible? Or is that idea too crazy : )

I tried to connect like this:

Sub test()
  Dim dm As New DriveMapper
  Dim sharepointFolder As Scripting.folder

  Set sharepointFolder = dm.MapDrive("\\***.sharepoint.com@SSL\sites\General1462\Lists\")

  Debug.Print sharepointFolder.Path
End Sub

But I got an error code (800704dc). I tried with this solution Get the content of a sharepoint folder with Excel VBA

Answer 1

    Function tableWithAllSub([Microsoft.SharePoint.Client.Web]$Web){

    $counterSub= 1
    $counterLoadingBar = 0
    $counterForFileFound = 0
    $counterFileIsEmpty = 0
    $counterFolderIsEmpty = 0
    $counterForSumupAllRows = 0
    $counterForTemplates = 0

$excludedLists  = @("Reusable Content","Content and Structure Reports","Form Templates")

$Lists = Get-PnPList | Where {$_.Hidden -eq $False -and $excludedLists -notcontains $_.Title}

    $xl = new-object -c excel.application
    $xl.displayAlerts = $false


    $tableAllInformation = foreach ($List in $Lists){
        

        $fileFound = "Nein"
        $counterAllItemsElseIf = 0
        


        #FileRef = Pfad zur Datei. File_x0020_Type = Dateityp. FileLeafRef = Name der Datei.
        $allItems = Get-PnPListItem -List $List -Fields "FileRef", "File_x0020_Type", "FileLeafRef"

        foreach ($Item in $allItems) {


            $counterOfRows = 0
            $counterAllItemsElseIf++

            if ($Item["FileLeafRef"] -like "*Record*") {


                $fileFound = "Ja"
                $counterForFileFound++


                Get-PnPFile -Url $Item["FileRef"] -Path C:\Users\$env:USERNAME\Desktop\$nameOfDirectoryToday\tmp\ -Filename $Item["FileLeafRef"] -AsFile -Force
                

                $nameOfFile = $Item["FileLeafRef"]
                $filePath = "C:\Users\$env:USERNAME\Desktop\$nameOfDirectoryToday\tmp\$nameOfFile"


                $row = 4
                $column = "C"
                
                        
                $excelDocumentSource = $xl.workbooks.open($filePath, $null, $true)
                $sheetToSearchIn = $excelDocumentSource.sheets.item('REGISTER')    
                

                    $cellEmpty = "Nein"
                    if([string]::IsNullOrEmpty($sheetToSearchIn.Cells.Item($row, $column).Value2) ) {
                        $cellEmpty = "Ja"
                        $counterFileIsEmpty++
                    }


                    while(-not ([string]::IsNullOrEmpty($sheetToSearchIn.Cells.Item($row + $counterOfRows, $column).Value2) ) ) {
                        $counterOfRows++        
                    }

                    $counterForSumupAllRows += $counterOfRows


                    if($counterOfRows -eq 36 -and $sheetToSearchIn.Cells.Item(4, "C").Value2 -eq "services" -and $sheetToSearchIn.Cells.Item(5, "C").Value2 -eq "Local"){
                        $cellEmpty = "Template"
                        $counterForTemplates++
                    }
                    

                $excelDocumentSource.close($false)                               


                $userValueCollection = [Microsoft.SharePoint.Client.FieldUserValue[]]$Item["Editor"]
                $lastModifiedBy = $userValueCollection.LookupValue.ToString()


                $modifiedOn = $Item["Modified"]


                    if($List.Title -eq "service"){
                        $subFolderService = searchForForwardSlash($Item["FileRef"].substring(29))
                        $nameofSub = $List.Title + " - " + $subFolderService
                    }
                    else{$nameofSub = $List.Title}


                [PSCustomObject] @{
                'N'                = $counterSub++
                'T'   = $nameofSub
                'D'        = $fileFound
                'Z'     = $cellEmpty
                'A'    = $counterOfRows
                'Z'  = $lastModifiedBy 
                'G'           = $modifiedOn
                'D'             = $Item["FileLeafRef"]
                'P'        = $Item["FileRef"]
                }   
            }

         elseIf ($counterAllItemsElseIf -eq $allItems.count -and $fileFound -eq "Nein"){

                $counterFolderIsEmpty++

                if($List.Title -eq "service"){
                    $subFolderService = searchForForwardSlash($Item["FileRef"].substring(29))
                    $nameofSub = $List.Title + " - " + $subFolderService
                }
                else{$nameofSub = $List.Title}

                [PSCustomObject] @{
                'N'                = $counterSub++
                'T'   = $nameofSub
                'D'        = $fileFound
                'Z'     = " "
                'A'    = " "
                'Z'  = " " 
                'G'           = " "
                'D'             = " "
                'P'        = " "
                }
                    
            }
            
        }

                
        $counterLoadingBar++
        $showProgress = [int]( (100/$Lists.count) * $counterLoadingBar)
        Write-Progress -Activity "Search in Progress" -Status "$showProgress% Complete:" -PercentComplete ( (100/$Lists.count) * $counterLoadingBar)
  
    }

    $endResult= [PSCustomObject] @{
                'A'                = $Lists.count
                'O'                = $counterFolderIsEmpty
                'A'     = $counterForFileFound
                'L'                    = $counterFileIsEmpty
                'A'              = ($counterForFileFound - $counterFileIsEmpty)
                'I'    = $counterForSumupAllRows - ($counterForTemplates * 36)
                'A'             = $counterForTemplates
                }

    $xl.quit()

    $tableAllInformation | Export-CSV $CSVFile -NoTypeInformation -Force

    $tableAllInformation | Format-Table *
    $endResult | Format-Table *
}