Find & delete files in a Sharepoint folder with VBA

akshay27

New Member
Joined
Dec 10, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,181,708
Messages
5,931,592
Members
436,795
Latest member
mazigazi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top