Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel using an Array to Delete File in Directory if it Exists
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel using an Array to Delete File in Directory if it Exists
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel using an Array to Delete File in Directory if it Exists
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 = On Feb 1, 6:23 pm, "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine if a Folder/Directory Exists in Excel VBA | Excel Programming | |||
How do you delete an exported file from a directory | Excel Programming | |||
File exists under directory | Excel Programming | |||
Delete file if file exists. | Excel Programming | |||
How do I force an Excel macro to ask me which file and directory? | Excel Discussion (Misc queries) |