| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Files in Folder
Any guidance on how one would write code to have VBA go
through a given folder and delete those files that do not appear on a list (the list doesn't have to be an external reference) ? TIA! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Files in Folder
I'm only responding because no one else did. I'm sure
someone else can produce better code but they have yet to do so. Also be advised that I'm not a professional programmer. I've never used Kill in practise and using Kill is dangerous. I'm under the impression that files deleted using Kill do not go to the recycle bin but are "just gone". Use with extreme care. For my experiment I created 6 Excel files named "KillTest1.xls" to "KillTest6.xls". The odd file names are listed in the array SaveList. The code will delete all files selected using the GetOpenFileName method except those listed in the SaveList array. The code successfully deleted the even file names in my experiment. Option Base 1 Sub TestKill() Dim FileArr As Variant, SaveList As Variant Dim i As Integer, txt As String, SaveFile As Boolean SaveList = Array ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") On Error Resume Next FileArr = Application.GetOpenFilename(MultiSelect:=True) For i = 1 To UBound(FileArr) For x = Len(FileArr(i)) To 1 Step -1 txt = Mid(FileArr(i), x, 1) If txt = "\" Then Exit For Next FileArr(i) = Right(FileArr(i), Len(FileArr(i)) - x) For ii = 1 To UBound(SaveList) If SaveList(ii) = FileArr(i) Then SaveFile = True Next ii If SaveFile = False Then Kill FileArr(i) SaveFile = False Next i End Sub Regards, Greg -----Original Message----- Any guidance on how one would write code to have VBA go through a given folder and delete those files that do not appear on a list (the list doesn't have to be an external reference) ? TIA! . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Files in Folder
Thanks for the reply. Unfortunately, it doesn't like:
SaveList = Array ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") Any clue? TIA -----Original Message----- I'm only responding because no one else did. I'm sure someone else can produce better code but they have yet to do so. Also be advised that I'm not a professional programmer. I've never used Kill in practise and using Kill is dangerous. I'm under the impression that files deleted using Kill do not go to the recycle bin but are "just gone". Use with extreme care. For my experiment I created 6 Excel files named "KillTest1.xls" to "KillTest6.xls". The odd file names are listed in the array SaveList. The code will delete all files selected using the GetOpenFileName method except those listed in the SaveList array. The code successfully deleted the even file names in my experiment. Option Base 1 Sub TestKill() Dim FileArr As Variant, SaveList As Variant Dim i As Integer, txt As String, SaveFile As Boolean SaveList = Array ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") On Error Resume Next FileArr = Application.GetOpenFilename(MultiSelect:=True) For i = 1 To UBound(FileArr) For x = Len(FileArr(i)) To 1 Step -1 txt = Mid(FileArr(i), x, 1) If txt = "\" Then Exit For Next FileArr(i) = Right(FileArr(i), Len(FileArr(i)) - x) For ii = 1 To UBound(SaveList) If SaveList(ii) = FileArr(i) Then SaveFile = True Next ii If SaveFile = False Then Kill FileArr(i) SaveFile = False Next i End Sub Regards, Greg -----Original Message----- Any guidance on how one would write code to have VBA go through a given folder and delete those files that do not appear on a list (the list doesn't have to be an external reference) ? TIA! . . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Files in Folder
That was web site forced word wrap. That statement should
all be in one line. Alternatively, use the line continuation character: SaveList = Array("KillTest1.xls", "KillTest3.xls", _ "KillTest5.xls") Regards, Greg -----Original Message----- Thanks for the reply. Unfortunately, it doesn't like: SaveList = Array ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") Any clue? TIA -----Original Message----- I'm only responding because no one else did. I'm sure someone else can produce better code but they have yet to do so. Also be advised that I'm not a professional programmer. I've never used Kill in practise and using Kill is dangerous. I'm under the impression that files deleted using Kill do not go to the recycle bin but are "just gone". Use with extreme care. For my experiment I created 6 Excel files named "KillTest1.xls" to "KillTest6.xls". The odd file names are listed in the array SaveList. The code will delete all files selected using the GetOpenFileName method except those listed in the SaveList array. The code successfully deleted the even file names in my experiment. Option Base 1 Sub TestKill() Dim FileArr As Variant, SaveList As Variant Dim i As Integer, txt As String, SaveFile As Boolean SaveList = Array ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") On Error Resume Next FileArr = Application.GetOpenFilename(MultiSelect:=True) For i = 1 To UBound(FileArr) For x = Len(FileArr(i)) To 1 Step -1 txt = Mid(FileArr(i), x, 1) If txt = "\" Then Exit For Next FileArr(i) = Right(FileArr(i), Len(FileArr(i)) - x) For ii = 1 To UBound(SaveList) If SaveList(ii) = FileArr(i) Then SaveFile = True Next ii If SaveFile = False Then Kill FileArr(i) SaveFile = False Next i End Sub Regards, Greg -----Original Message----- Any guidance on how one would write code to have VBA go through a given folder and delete those files that do not appear on a list (the list doesn't have to be an external reference) ? TIA! . . . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Files in Folder
You got hit by wordwrap. That should be all on one line:
SaveList = Array("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") or you could use the continuation characters (spacebar, underscore) to make it a logical line: SaveList = Array _ ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") JEFFRO wrote: Thanks for the reply. Unfortunately, it doesn't like: SaveList = Array ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") Any clue? TIA -----Original Message----- I'm only responding because no one else did. I'm sure someone else can produce better code but they have yet to do so. Also be advised that I'm not a professional programmer. I've never used Kill in practise and using Kill is dangerous. I'm under the impression that files deleted using Kill do not go to the recycle bin but are "just gone". Use with extreme care. For my experiment I created 6 Excel files named "KillTest1.xls" to "KillTest6.xls". The odd file names are listed in the array SaveList. The code will delete all files selected using the GetOpenFileName method except those listed in the SaveList array. The code successfully deleted the even file names in my experiment. Option Base 1 Sub TestKill() Dim FileArr As Variant, SaveList As Variant Dim i As Integer, txt As String, SaveFile As Boolean SaveList = Array ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") On Error Resume Next FileArr = Application.GetOpenFilename(MultiSelect:=True) For i = 1 To UBound(FileArr) For x = Len(FileArr(i)) To 1 Step -1 txt = Mid(FileArr(i), x, 1) If txt = "\" Then Exit For Next FileArr(i) = Right(FileArr(i), Len(FileArr(i)) - x) For ii = 1 To UBound(SaveList) If SaveList(ii) = FileArr(i) Then SaveFile = True Next ii If SaveFile = False Then Kill FileArr(i) SaveFile = False Next i End Sub Regards, Greg -----Original Message----- Any guidance on how one would write code to have VBA go through a given folder and delete those files that do not appear on a list (the list doesn't have to be an external reference) ? TIA! . . -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Files in Folder
A very minor point, but I note that I failed to declare
the "ii" variable. In the declaration section you should include "Dim ii As Integer". Regards, Greg -----Original Message----- That was web site forced word wrap. That statement should all be in one line. Alternatively, use the line continuation character: SaveList = Array("KillTest1.xls", "KillTest3.xls", _ "KillTest5.xls") Regards, Greg -----Original Message----- Thanks for the reply. Unfortunately, it doesn't like: SaveList = Array ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") Any clue? TIA -----Original Message----- I'm only responding because no one else did. I'm sure someone else can produce better code but they have yet to do so. Also be advised that I'm not a professional programmer. I've never used Kill in practise and using Kill is dangerous. I'm under the impression that files deleted using Kill do not go to the recycle bin but are "just gone". Use with extreme care. For my experiment I created 6 Excel files named "KillTest1.xls" to "KillTest6.xls". The odd file names are listed in the array SaveList. The code will delete all files selected using the GetOpenFileName method except those listed in the SaveList array. The code successfully deleted the even file names in my experiment. Option Base 1 Sub TestKill() Dim FileArr As Variant, SaveList As Variant Dim i As Integer, txt As String, SaveFile As Boolean SaveList = Array ("KillTest1.xls", "KillTest3.xls", "KillTest5.xls") On Error Resume Next FileArr = Application.GetOpenFilename(MultiSelect:=True) For i = 1 To UBound(FileArr) For x = Len(FileArr(i)) To 1 Step -1 txt = Mid(FileArr(i), x, 1) If txt = "\" Then Exit For Next FileArr(i) = Right(FileArr(i), Len(FileArr(i)) - x) For ii = 1 To UBound(SaveList) If SaveList(ii) = FileArr(i) Then SaveFile = True Next ii If SaveFile = False Then Kill FileArr(i) SaveFile = False Next i End Sub Regards, Greg -----Original Message----- Any guidance on how one would write code to have VBA go through a given folder and delete those files that do not appear on a list (the list doesn't have to be an external reference) ? TIA! . . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
| Name of files in a folder | Excel Discussion (Misc queries) | |||
| Copying all files in a folder to new folder | Excel Discussion (Misc queries) | |||
| how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
| open all files in a folder and ... | Excel Programming | |||
Linear Mode
