|
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! |
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! . |
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! . . |
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! . . . |
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! . . . . |
| All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com