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