Find & delete files in a Sharepoint folder with VBA

[akshay27]

I need my macro to scan a Sharepoint folder, find files that have certain elements in their filenames, and delete those files. I am really struggling to find a solution that can do this reliably. I have currently implemented a workaround which needs people to Sync the Sharepoint directory to their work machine. However, this method is working for some of us, but is throwing a Runtime Error '76': Path not found for some others. All of us have the same rights, are doing the same thing with the same setups and the same steps, but for some reason, there is this weird inconsistency with how objFSO.GetFolder works.

Is there a way to do this directly with Sharepoint without having this weird local workaround?

Public TicketID         As String

TicketID = Sheets("Combined Score").Range("B1").Value

Sub DeleteIndividualRatings()

'Define a bunch of local variables that we'll use only in this Subroutine
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim ws As Worksheet

'First we create a FileSystemObject so that we can assign it a folder.
'This is currently a big workaround and doesn't let the code work with Sharepoint, perhaps since this is an antiquated method that Microsoft created before Sharepoint existed? IDK
Set objFSO = CreateObject("Scripting.FileSystemObject")
   
'Since we need to use an antiquated method to get the folder object where the files to be deleted are stored,
'The entire directory needs to be synced to the user's local profile on the Company Laptop, from Sharepoint or from the Teams Channel.
'Unless this very specific requirement is met, the deletion will not work & very likely throw a "Path not found" error.
Set objFolder = objFSO.GetFolder(Environ("USERPROFILE") & "\XXXX\In Progress\")
  
'For each file object in the Folder object
For Each objFile In objFolder.Files
    If InStr(1, objFile.Name, TicketID) > 0 Then 'We check if the TicketID is found in the name
        Kill objFile 'If we find such a file, then we Nuke it. Careful here - the Kill command doesn't put files into the Recycle bin! It basically pulls a Thanos!
        'To Do - add yet another a confirmation that shows which files have been found & ask the user if those files really should be deleted.
        'Perhaps throw them into the recycle bin for safety? Not sure if necessary though, since the final file has a record of everyone's ratings anyway.
    End If
Next
   
'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing

End Sub

 

[Pinaceous]

I need my macro to scan a Sharepoint folder, find files that have certain elements in their filenames, and delete those files. I am really struggling to find a solution that can do this reliably. I have currently implemented a workaround which needs people to Sync the Sharepoint directory to their work machine. However, this method is working for some of us, but is throwing a Runtime Error '76': Path not found for some others. All of us have the same rights, are doing the same thing with the same setups and the same steps, but for some reason, there is this weird inconsistency with how objFSO.GetFolder works.

Is there a way to do this directly with Sharepoint without having this weird local workaround?

Public TicketID         As String

TicketID = Sheets("Combined Score").Range("B1").Value

Sub DeleteIndividualRatings()

'Define a bunch of local variables that we'll use only in this Subroutine
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim ws As Worksheet

'First we create a FileSystemObject so that we can assign it a folder.
'This is currently a big workaround and doesn't let the code work with Sharepoint, perhaps since this is an antiquated method that Microsoft created before Sharepoint existed? IDK
Set objFSO = CreateObject("Scripting.FileSystemObject")
  
'Since we need to use an antiquated method to get the folder object where the files to be deleted are stored,
'The entire directory needs to be synced to the user's local profile on the Company Laptop, from Sharepoint or from the Teams Channel.
'Unless this very specific requirement is met, the deletion will not work & very likely throw a "Path not found" error.
Set objFolder = objFSO.GetFolder(Environ("USERPROFILE") & "\XXXX\In Progress\")
 
'For each file object in the Folder object
For Each objFile In objFolder.Files
    If InStr(1, objFile.Name, TicketID) > 0 Then 'We check if the TicketID is found in the name
        Kill objFile 'If we find such a file, then we Nuke it. Careful here - the Kill command doesn't put files into the Recycle bin! It basically pulls a Thanos!
        'To Do - add yet another a confirmation that shows which files have been found & ask the user if those files really should be deleted.
        'Perhaps throw them into the recycle bin for safety? Not sure if necessary though, since the final file has a record of everyone's ratings anyway.
    End If
Next
  
'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing

End Sub

Did you ever come up with a solution for this?

 

[akshay27]

Did you ever come up with a solution for this?

I have not, unfortunately. I haven't been able to change the current implementation and it's really irritating. the whole thing is very wonky because of how annoying OneDrive / Sharepoint is with how it handles sync.

I did, however, recently ask ChatGPT for a solution and this is what it spat out:

I haven't tried implementing this as yet, but I will give it a shot in the coming weeks.

 

[nguyendang]

I have not, unfortunately. I haven't been able to change the current implementation and it's really irritating. the whole thing is very wonky because of how annoying OneDrive / Sharepoint is with how it handles sync.

I did, however, recently ask ChatGPT for a solution and this is what it spat out:
View attachment 82329
I haven't tried implementing this as yet, but I will give it a shot in the coming weeks.

You need to implement Microsoft Graph API to do this task.
First, follow the instructions in this article to register an Azure app, then register your app and grab its Client Id: Get access on behalf of a user
Run the following macro (modify it to suit your need). Remember to download this .xlam file (SharePoint-MicrosoftGraphAPI) before running this macro:

Option Explicit

Public Sub DeleteIndividualRatings()
    Dim TicketId As String
    TicketId = Sheets("Combined Score").Range("B1").Value
    Dim objMicrosoftGraph As MicrosoftGraphOAuth2
    Set objMicrosoftGraph = New MicrosoftGraphOAuth2
    With objMicrosoftGraph
        .ClientId = "Client_Id" 'Client Id
        .ApplicationName = "SharePoint for VBA" 'Acts as a folder in %APPDATA% which stores access token file
        .Scope = Array("Sites.ReadWrite.All") 'Scope required to perform read/write operations on a SharePoint site
        .Tenant = Organizations 'Specify that only work or schools accounts are allowed to sign into the application
        On Error GoTo Err_Handle
        .AuthorizeOAuth2
    End With
    Dim strSiteId As String, strSite As String, strSiteRelativePath As String, strSharePointFolderPath As String
    strSite = "danghome.sharepoint.com" 'hostname
    strSiteRelativePath = "sites/CompanyNews" 'Server-relative path of a site
    'For example, danghome.sharepoint.com/sites/CompanyNews, with danghome.sharepoint.com as hostname and sites/CompanyNews as server-relative path
    strSiteId = objMicrosoftGraph.SiteResource.GetSiteByServerRelativePath(strSite, strSiteRelativePath, "$select=id").Id
    On Error GoTo Err_Handle
    Dim colFiles As Collection
    strSharePointFolderPath = "Email Attachments/dang_master_time@yahoo.com.vn" 'folder on SharePoint site
    'The default page size for each request is 200, you can change how many items are included in each response by specifying $top ODada Query. For example, $top=100
    'For best performance, only select neccessary fields to be included in the returned response with $select
    Set colFiles = objMicrosoftGraph.FilesResource.ListChildren(DestinationSite, SiteId:=strSiteId, ItemPath:=strSharePointFolderPath, ODataQuery:="$select=id,name,file,folder")
    On Error GoTo Err_Handle
    Dim objDriveItem As DriveItem
    Dim i As Long, j As Long
    'Loop through the result
    If colFiles.Count > 0 Then
        For i = 1 To colFiles.Count
            For j = 1 To colFiles.Item(i).Count
                Set objDriveItem = colFiles.Item(i)(j)
                'If the item is not a folder (folder facet is null) then proceed to the next step
                If objDriveItem.Folder Is Nothing Then
                    'If the item's name contains TicketId
                    If InStr(1, objDriveItem.Name, TicketId, vbTextCompare) > 0 Then
                        'Delete the item in the default document library
                        'For best performance, it is recommended to use batch request to save the application significant network latency
                        'However, batch requests are currently limited to 20 individual requests.
                        'For more information, read this artile: https://learn.microsoft.com/en-us/graph/json-batching
                        objMicrosoftGraph.FilesResource.Delete DestinationSite, SiteId:=strSiteId, ItemId:=objDriveItem.Id
                        On Error GoTo Err_Handle
                        Debug.Print objDriveItem.Name & " was successfully deleted!"
                    End If
                End If
            Next
        Next
    End If
Err_Handle:
    If Err.Number <> 0 Then
        MsgBox Err.Description, vbExclamation, "Error"
        Exit Sub
    End If
    On Error GoTo 0
End Sub