Macro in Excel using an Array to Delete File in Directory if it Ex
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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.
"Matt" wrote:
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").FileExi sts(.Directory) Then
Kill MyPath & sDirectory
Else
End If
Next sDir
End Sub
|