Delete SharePoint file

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,598
Office Version
  1. 365
Platform
  1. Windows
Is there a way to delete an SP file using VBA?

The usual Kill function does execute when the file is given an Object variable but sometimes this works and sometimes it doesn't......
VBA Code:
Sub GetUpdatedSOData()

Dim rng As Range

Dim objFolder As Object
Dim objNet As Object
Dim objFSO As Object

Dim strFolder As String
   
strFolder = ActiveWorkbook.Path & "/SO Updates/"

Set objNet = CreateObject("WScript.Network")
Set objFSO = CreateObject("Scripting.FileSystemObject")

objNet.MapNetworkDrive "A:", strFolder

Set objFolder = objFSO.getfolder("A:")

Set rng = Range("A1")

GetAllFilesFolders rng, objFolder, "" & strFolder

objNet.RemoveNetworkDrive "A:"

Set objNet = Nothing
Set objFSO = Nothing

End Sub

Public Sub GetAllFilesFolders(rng As Range, objFolder As Object, strFolder As String)

Dim objFile As Object

Dim strFile As String

Set wbDR = ActiveWorkbook

Set rngStart = Range("AD_CFWCol")
Set rngEnd = Range("AD_InitialEmailCol")

For Each objFile In objFolder.Files
   strSO = objFile.Name
   
   strSO = Replace(strSO, ".xlsx", "")
   
   Range("AD_SO") = strSO
   
   Calculate
   
   lngRow = Range("AD_SORow")
   
   Set wbUpdate = Workbooks.Open(objFile)
   
   UpdateAD
      
   wbUpdate.Close savechanges:=False
   
   Set wbUpdate = Nothing
   
   Kill objFile
Next

Set objFile = Nothing

Set objFolder = Nothing

Set rngStart = Nothing
Set rngEnd = Nothing
Set rngAD = Nothing

Range("AD_SO") = ""

Set wbDR = Nothing

End Sub



TIA
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
You mention sometimes it works and sometimes not, so I assume you have it properly mapped to A: and it does in fact delete the file sometimes. It looks like you are targeting xlsx files specifically, are the times it fails caused by file types other than xlsx?

What is your UpdateAD function doing?

It looks like you open the file, run this updateAD function then close the file and kill the file.

It could be that you are trying to kill the file while it is still open or in use and thus fails, or something in this update is going wrong.
 
Upvote 0
You mention sometimes it works and sometimes not, so I assume you have it properly mapped to A: and it does in fact delete the file sometimes. It looks like you are targeting xlsx files specifically, are the times it fails caused by file types other than xlsx?

What is your UpdateAD function doing?

It looks like you open the file, run this updateAD function then close the file and kill the file.

It could be that you are trying to kill the file while it is still open or in use and thus fails, or something in this update is going wrong.

The files to be deleted are all .xlsx as another routine I have built is creating the files to be deleted.

The UpdateAD routine is taking data from the file wbUpdate file once it is opened and then it needs to be deleted.

The file is definitley closed as I am running this in a test folder nobody has access to so this isn't the issue.
 
Upvote 0
I am on an O365 subscription so this isn't the problem as it applies to 2003-2007.
 
Upvote 0
We use Office365 as well and I have seen this occur with this format as well, the example I posted may have been specific to 2003-2007 however it was just to point out that Excel can appear to be closed but not actually be closed and it can happen with O365 as well. Good Luck.
 
Upvote 0
Example that other users have this issue with 2016/O365

https://answers.microsoft.com/en-us...r/a58b9d59-4a2d-445b-85cb-82ac5f19f311?page=1

I have seen times where files that are sync'd with OneDrive will remain open after being closed while it tries to sync for example. There are likely other causes as well.

Maybe try to put a check in your loop to see if the file is still open before deleting it? If still open try to close it again? Or Insert a check to see if the file has in fact been deleted. When you see that it failed check if Excel process is still open?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top