VBA loop through Sharepoint documents folder to refresh queries without TEMP/cache file error-CodePudding

Goal:

Create VBA routine to refresh queries in all workbooks in the SharePoint Online site document folder.

Folder Setup:

SharePoint site -> Documents -> Main Folder -> 10 Subfolders -> 1 Excel file in each

Current Code:

Sub RefreshFW()
    
    Dim fso, oFolder, oSubfolder, oFile, queue As Collection
    Dim lCnt As Long

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set queue = New Collection
    
    queue.Add fso.GetFolder("C:\Users\user1\organization\main_folder") 'from Sharepoint sync

    Do While queue.Count > 0
        Set oFolder = queue(1)
        queue.Remove 1 'dequeue
        For Each oSubfolder In oFolder.SubFolders
            queue.Add oSubfolder 'enqueue
        Next oSubfolder
        For Each oFile In oFolder.Files
            Workbooks.Open fileName:=oFile
            ActiveWorkbook.Unprotect Password:="wb"
            With ActiveWorkbook
                For lCnt = 1 To .Connections.Count
                    If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
                        .Connections(lCnt).OLEDBConnection.BackgroundQuery = False
                    End If
                Next lCnt
            End With
            ActiveWorkbook.RefreshAll
            ActiveWorkbook.Protect Password:="wb"
            ActiveWorkbook.Close SaveChanges:=True
        Next oFile
    Loop
    
End Sub

Issues:

Runtime 1004 Error

"Microsoft cannot access the file 'C:\Users\user1\organization\folder\.123A1234-D756*******' The file name or path does not exist or The file is being used by another program" during Workbooks.Open fileName:=oFile

Things I've tried:

Pointing to a local folder instead of the Synced "local" SharePoint folder - this worked.

Changing the SharePoint path to UNC format using DavWWWRoot - did not work* may have messed this up, but path was not found

Adding the Main Folder web address to a mapped network drive instead of using the "Sync" button on SharePoint online, then pointing to the network drive instead - lack of permission (not a trusted site, need to contact internal resources to get approval)

Questions:

Is this .123A1234-D756**** file a cache/temp file living within the Main Folder?

How do I adjust my code to ignore this cache/temp file while looping through the folder?

Any other recommendations?

Thank you

CodePudding user response：

Sub RefreshFW()
    Application.ScreenUpdating = False
    Dim fso, oFolder, oSubfolder, oFile, queue As Collection
    Dim lCnt As Long

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set queue = New Collection
    
    queue.Add fso.GetFolder("C:\Users\user1\organization\main_folder")

    Do While queue.Count > 0
        Set oFolder = queue(1)
        queue.Remove 1 'dequeue
        For Each oSubfolder In oFolder.SubFolders
            queue.Add oSubfolder 'enqueue
        Next oSubfolder
        For Each oFile In oFolder.Files
            If oFile.Name Like "*xls*" Then
                Workbooks.Open fileName:=oFile
                ActiveWorkbook.Unprotect Password:="wb"
                With ActiveWorkbook
                    For lCnt = 1 To .Connections.Count
                        If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
                            .Connections(lCnt).OLEDBConnection.BackgroundQuery = False
                        End If
                    Next lCnt
                End With
                ActiveWorkbook.RefreshAll
                ActiveWorkbook.Protect Password:="wb"
                ActiveWorkbook.Close SaveChanges:=True
            End If
        Next oFile
    Loop
    Application.ScreenUpdating = True
End Sub

Added If oFile.Name Like "*xls*" Then and End If to check for excel extensions only.