Macro in Excel using an Array to Delete File in Directory if it Exists

[Matt]

Hello everyone:

I am trying to use an array in an Excel XP macro to delete a file(s)
if they exist. I have written some code and attached it but I am
getting a Compilte Error message when it hits the line
"Files(sDir).Select". I think that I need to change the word Files in
that line of code, but am not sure what to change it to, assuming I
have written that part correct. Any help would be greatly
appreciated. Thanks in advance.

Matt


Sub Delete_Dir_If_Exists()

Dim sbj As String
Dim sCustomer As String
Dim sLease As String
Dim sWellNo As String
Dim sSum As String
Dim sProc As String
Dim sVol As String
Dim sPres As String
Dim sBlen As String
Dim sDir As File

ActiveWorkbook.Sheets("SC Database").Activate
sCustomer = ActiveSheet.Range("Customer")
sField = ActiveSheet.Range("Field")
sWell = ActiveSheet.Range("Well")
sbj = sCustomer & " " & sField & " " & sWell & " "
sSum = sbj & "Onsite.doc"
sProc = sbj & "Pumping.doc"
sVol = sbj & "Volume.doc"
sPres = sbj & "Pressure.doc"
sBlen = sbj & "Calculations.doc"

' Finds out if the file already exists, and if it does deletes it.
sDirectory = Array(sSum, sProc, sVol, sPres, sBlen)
For Each sDir In sDirectory
Files(sDir).Select
If
CreateObject("scripting.fileSystemObject").FileExists(.Directory) Then
Kill MyPath & sDirectory
Else
End If
Next sDir
End Sub

 

[merjet]

Could you just replace:
Files(sDir).Select
If CreateObject .... Then
Kill MyPath & sDirectory
Else
End If
with: Kill MyPath & sDirectory
and before the For ...Next loop put:
On Error Resume Next

Hth,
Merjet

 

[mdupris]

Matt,

A few observations:
You probably want to use "FileSearch" instead of "File" for what
you're trying. You'll probably also want to define 'sDir' as
'Variant', since that's what 'For Each' is happiest with. Some code
snippets for this kind of operation:

Dim fs As FileSearch
Dim sDir as Variant

Set fs = Application.FileSearch
With fs

For Each sDir In sDirectory
.LookIn = MyPath
.FileName = sDir
If .Execute > 0 Then
Kill MyPath & sDirectory
End If
Next sDir

End With

I realize your code as posted is incomplete (e.g. "MyPath" is never
declared or initialized) but the above code may point you to a useful
direction.

= Marchand =

 

[Martin Fishlock]

Matt try this at the instead of your code:

' Finds out if the file already exists, and if it does deletes it.
sDirectory = Array(sSum, sProc, sVol, sPres, sBlen)

On Error Resume Next
' no need to check as it will just resume
' at the next line

For Each sDir In sDirectory

' what is my path
Kill MyPath & sDirectory
Next sDir
End Sub

and you also need to change

Dim sDir As File
to

Dim sDir As string


=============
But to tidy it up have a look at:

Option Explicit

Const csSum As String = "Onsite.doc"
Const csProc As String = "Pumping.doc"
Const csVol As String = "Volume.doc"
Const csPres As String = "Pressure.doc"
Const csBlen As String = "Calculations.doc"

Sub Delete_Dir_If_Exists()
' Dim mypath As String
Dim sbj As String
Dim sCustomer As String
Dim sField As String
Dim sWell As String

Dim sDirectory As Variant
Dim sDir As Variant

With ActiveWorkbook.Sheets("SC Database")
sCustomer = .Range("Customer")
sField = .Range("Field")
sWell = .Range("Well")
End With

sbj = sCustomer & " " & sField & " " & sWell & " "

' Finds out if the file already exists, and if it does deletes it.
sDirectory = Array(csSum, csProc, csVol, csPres, csBlen)

On Error Resume Next

For Each sDir In sDirectory
Kill mypath & sbj & sDirectory
Next sDir
End Sub

'-------------------
Note that I use option explicit to ensure that variables properly declared
as you have a couple of problem areas.