# Find & delete files in a Sharepoint folder with VBA



## akshay27 (Aug 29, 2022)

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 (Sunday at 10:05 AM)

akshay27 said:


> 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?
> 
> ...



Did you ever come up with a solution for this?


----------



## akshay27 (Monday at 4:52 AM)

Pinaceous said:


> 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.


----------

