435,197 Members | 1,057 Online
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

# How to use VBA to delete files?

 P: n/a Is there a quick and dirty way to delete all files in a given directory? perhaps something like this: Set objFile = CreateObject("Scripting.FileSystemObject") objFile.DeleteFile "all files in C:\Temp" 'how to specify directory? Do I need to use the Windows API? Private Declare Function FindFirstFile Lib "kernel32 Alias "FindFirstFileA" (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long What I'm trying to do is maintain a backup directory of documents that are hyperlinked to various records in my Access 2003 mdb. All the hyperlinks are stored in a table, so it's just a matter of using FileCopy to copy the documents to the backup directory. But first I want to purge the backup directory of any existing files... how to get a list of files in the directory? Thanks in advance... Nov 12 '05 #1
12 Replies

 P: n/a "deko" wrote in message news:ST*****************@newssvr25.news.prodigy.co m... Is there a quick and dirty way to delete all files in a given directory? perhaps something like this: Set objFile = CreateObject("Scripting.FileSystemObject") objFile.DeleteFile "all files in C:\Temp" 'how to specify directory? Do I need to use the Windows API? Private Declare Function FindFirstFile Lib "kernel32 Alias "FindFirstFileA" (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long What I'm trying to do is maintain a backup directory of documents that are hyperlinked to various records in my Access 2003 mdb. All the hyperlinks are stored in a table, so it's just a matter of using FileCopy to copy the documents to the backup directory. But first I want to purge the backup directory of any existing files... how to get a list of files in the directory? Thanks in advance... Kill ("D:\NewFolder\*.*") Nov 12 '05 #2

 P: n/a On Tue, 23 Dec 2003 01:29:22 GMT in comp.databases.ms-access, "deko" wrote: Is there a quick and dirty way to delete all files in a given directory?perhaps something like this: Set objFile = CreateObject("Scripting.FileSystemObject") objFile.DeleteFile "all files in C:\Temp" 'how to specify directory?Do I need to use the Windows API? Private Declare Function FindFirstFile Lib "kernel32 Alias"FindFirstFileA" (ByVal lpFileName As String, lpFindFileData AsWIN32_FIND_DATA) As Long Search help for "kill" What I'm trying to do is maintain a backup directory of documents that arehyperlinked to various records in my Access 2003 mdb. All the hyperlinksare stored in a table, so it's just a matter of using FileCopy to copy thedocuments to the backup directory. But first I want to purge the backupdirectory of any existing files... how to get a list of files in thedirectory? Dim strTmp as String Dim strFile() as String Dim lngNumFiles as Long Dim i As long strTmp = Dir$("c:\foo\bar\*.*") Do while len(strTmp) lngNumFiles = lngNumFiles + 1 Redim preserve strFile(1 to lngNumfiles) strFile(lngNumFiles) = strTmp strFile = Dir$() Loop For i = 1 to lngNumFiles DoSomethingWith strFile(i) Next -- A)bort, R)etry, I)nfluence with large hammer. Nov 12 '05 #3

 P: n/a thanks Trevor and rkc... Kill C:\BackupDir\*.* seems easy enough, but how about comparing the files and copying only those that have changed ? Here's a first crack at it... Public Sub sync loops through each record in the recordset, calling Public Function HasFileAttrib which looks for the current record (from sync) in the specified BackUp directory, and then gets the attributes of that file, if found, and compares the attributes of the found file to the attributes of the current record - if the attributes (e.g. modification time) are different, then copy (overwrite) to Backup directory. I know this code need a lot of work... any suggestions welcome! Option Compare Database Option Explicit Type WIN32_FIND_DATA lngFileAttributes As Long ' File attributes ftCreationTime As FILETIME ' Creation time ftLastAccessTime As FILETIME ' Last access time ftLastWriteTime As FILETIME ' Last modified time lngFileSizeHigh As Long ' Size (high word) lngFileSizeLow As Long ' Size (low word) lngReserved0 As Long ' reserved lngReserved1 As Long ' reserved strFilename As String * MAX_PATH ' File name strAlternate As String * 14 ' 8.3 name End Type Private Declare Function FindFirstFile Lib "kernel32" _ Alias "FindFirstFileA" (ByVal lpFileName As String, _ lpFindFileData As WIN32_FIND_DATA) As Long Private Declare Function FindNextFile Lib "kernel32" _ Alias "FindNextFileA" (ByVal hFindFile As Long, _ lpFindFileData As WIN32_FIND_DATA) As Long Private Declare Function FindClose Lib "kernel32" _ (ByVal hFindFile As Long) As Long Public Sub sync() Dim lngErrNum As Long Dim varHlk As Variant Dim strDt As String Dim j As String Dim strDoc As String Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("SELECT Document FROM tblDocuments WHERE Document Is Not Null") rst.MoveFirst Do Until rst.EOF DoEvents varHlk = rst!Document Debug.Print "varHlk = " & varHlk strDt = HyperlinkPart(varHlk, 0) Debug.Print "strDt = " & strDt j = (InStr(1, varHlk, "#", 1)) + 7 strDoc = Right(varHlk, Len(varHlk) - j) Debug.Print "strDoc = " & strDoc modCompare.HasFileAttrib (strDoc) rst.MoveNext Loop End Sub Public Function HasFileAttrib(ByVal strDoc As String, _ Optional ByVal enmFlags As VbFileAttribute) As Boolean Dim lngErrNum As Long 'Find file that matches strDoc in C:\Test 'GetAttr from matching strDoc and put in array? 'Compare Attr's between two files, return boolean if match GetAttr strDoc lngErrNum = Err If lngErrNum > 0 Then HasFileAttrib = False Exit Function End If 'define enmFlags by other file ? If ((GetAttr(strDoc) And enmFlags) = enmFlags) Then HasFileAttrib = True Else HasFileAttrib = False End If End Function "deko" wrote in message news:ST*****************@newssvr25.news.prodigy.co m... Is there a quick and dirty way to delete all files in a given directory? perhaps something like this: Set objFile = CreateObject("Scripting.FileSystemObject") objFile.DeleteFile "all files in C:\Temp" 'how to specify directory? Do I need to use the Windows API? Private Declare Function FindFirstFile Lib "kernel32 Alias "FindFirstFileA" (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long What I'm trying to do is maintain a backup directory of documents that are hyperlinked to various records in my Access 2003 mdb. All the hyperlinks are stored in a table, so it's just a matter of using FileCopy to copy the documents to the backup directory. But first I want to purge the backup directory of any existing files... how to get a list of files in the directory? Thanks in advance... Nov 12 '05 #4

 P: n/a "deko" wrote in message news:9h****************@newssvr27.news.prodigy.com ... thanks Trevor and rkc... Kill C:\BackupDir\*.* seems easy enough, but how about comparing the files and copying only those that have changed ? Here's a first crack at it... Public Sub sync loops through each record in the recordset, calling Public Function HasFileAttrib which looks for the current record (from sync) in the specified BackUp directory, and then gets the attributes of that file, if found, and compares the attributes of the found file to the attributes of the current record - if the attributes (e.g. modification time) are different, then copy (overwrite) to Backup directory. I'm not sure what kind of comments you're looking for so here's all I have to offer at the moment. FileDateTime is a VBA function. path has to be the full path (folder and file name) to the target file. ex: "D:\NewFolder\AccessStuff.txt" An example of the value returned: 12/23/2003 5:08:22 AM Or an empty string if the file is not found Function GetLastModified(path As String) As String Dim f As String f = Dir(path) If Len(f) > 0 Then GetLastModified = FileDateTime(path) End If End Function Nov 12 '05 #5

 P: n/a On Tue, 23 Dec 2003 03:04:37 GMT in comp.databases.ms-access, "deko" wrote: thanks Trevor and rkc...Kill C:\BackupDir\*.* seems easy enough, but how about comparing the filesand copying only those that have changed ?Here's a first crack at it...Public Sub sync loops through each record in the recordset, calling PublicFunction HasFileAttrib which looks for the current record (from sync) in thespecified BackUp directory, and then gets the attributes of that file, iffound, and compares the attributes of the found file to the attributes ofthe current record - if the attributes (e.g. modification time) aredifferent, then copy (overwrite) to Backup directory.I know this code need a lot of work... any suggestions welcome! You are making a lot of work for yourself with those API calls, Access VBA has the FileDateTime() function to get the date of a file. -- A)bort, R)etry, I)nfluence with large hammer. Nov 12 '05 #6

 P: n/a Thanks for the tip. As the number of files grows, I'll want to explore something more robust than Kill - and this seems like a good project to learn how to work with the API.... one problem I'm having is that the code won't compile - What references do I need checked for this kind of code to work properly? thanks again! "rkc" wrote in message news:QB*******************@twister.nyroc.rr.com... "deko" wrote in message news:9h****************@newssvr27.news.prodigy.com ... thanks Trevor and rkc... Kill C:\BackupDir\*.* seems easy enough, but how about comparing the files and copying only those that have changed ? Here's a first crack at it... Public Sub sync loops through each record in the recordset, calling Public Function HasFileAttrib which looks for the current record (from sync) in the specified BackUp directory, and then gets the attributes of that file, if found, and compares the attributes of the found file to the attributes of the current record - if the attributes (e.g. modification time) are different, then copy (overwrite) to Backup directory. I'm not sure what kind of comments you're looking for so here's all I have to offer at the moment. FileDateTime is a VBA function. path has to be the full path (folder and file name) to the target file. ex: "D:\NewFolder\AccessStuff.txt" An example of the value returned: 12/23/2003 5:08:22 AM Or an empty string if the file is not found Function GetLastModified(path As String) As String Dim f As String f = Dir(path) If Len(f) > 0 Then GetLastModified = FileDateTime(path) End If End Function Nov 12 '05 #7

 P: n/a "deko" wrote in news:YO****************@newssvr27.news.prodigy.com : Thanks for the tip. As the number of files grows, I'll want to explore something more robust than Kill - and this seems like a good project to learn how to work with the API I can't imagine what could be more robust than Kill. IMO it's way too robust and, as a result, dangerous. I look at it as a remnant of VB's distant past, something that is continued to preserve compatibility with ancient applications, but not really modern in it's nature. You might be able to set a reference to "Microsoft Shell Controls and Automation" and get a number of options for file manipulation by creating an instance of the Shell32 object. But the interface here is rather obscure, perhaps arcane. I have written quite a bit of code using declared API functions, and I may say that I am happy I did not begin with file manipulation; it seems to me that this area is one of the more challenging parts of the API. -- Lyle (for e-mail refer to http://ffdba.com/contacts.htm) Nov 12 '05 #8

 P: n/a Lyle Fairfield wrote in news:Xn*******************@130.133.1.4: "deko" wrote in news:YO****************@newssvr27.news.prodigy.com : Thanks for the tip. As the number of files grows, I'll want to explore something more robust than Kill - and this seems like a good project to learn how to work with the API I can't imagine what could be more robust than Kill. IMO it's way too robust and, as a result, dangerous. I look at it as a remnant of VB's distant past, something that is continued to preserve compatibility with ancient applications, but not really modern in it's nature. You might be able to set a reference to "Microsoft Shell Controls and Automation" and get a number of options for file manipulation by creating an instance of the Shell32 object. But the interface here is rather obscure, perhaps arcane. I have written quite a bit of code using declared API functions, and I may say that I am happy I did not begin with file manipulation; it seems to me that this area is one of the more challenging parts of the API. arghhhhhhh its nature ... I hate that ... I'll be even more Scroogey today than is usual at this time of year. -- Lyle (for e-mail refer to http://ffdba.com/contacts.htm) Nov 12 '05 #9

 P: n/a "Lyle Fairfield" wrote in message news:Xn*******************@130.133.1.4... "deko" wrote in news:YO****************@newssvr27.news.prodigy.com : Thanks for the tip. As the number of files grows, I'll want to explore something more robust than Kill - and this seems like a good project to learn how to work with the API I can't imagine what could be more robust than Kill. IMO it's way too robust and, as a result, dangerous. I look at it as a remnant of VB's distant past, something that is continued to preserve compatibility with ancient applications, but not really modern in it's nature. [snip] This reminds me of a question that I had a while back. Has anyone ever come up with an API call that would allow a custom Kill function that would result in the deleted files being moved to the recycle bin? I had an occasion where I was bitten by a bug in a little maintenance routine I had written that resulted in a Kill that I regretted and thought it would be nice if a file deletion could be rolled backed via the recycle bin. I poked around a bit with the APIs for locating Windows "special" folders but didn't find anything related to the recycle bin. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Nov 12 '05 #10

 P: n/a "deko" wrote in message news:YO****************@newssvr27.news.prodigy.com ... Thanks for the tip. As the number of files grows, I'll want to explore something more robust than Kill - and this seems like a good project to learn how to work with the API.... one problem I'm having is that the code won't compile - What references do I need checked for this kind of code to work properly? I'm not sure what code you are refering to when you say that the code won't compile. Nov 12 '05 #11

 P: n/a I appreciate the feedback. perhaps I have too much time on my hands... Kill seem to be working pretty well... "Lyle Fairfield" wrote in message news:Xn*******************@130.133.1.4... "deko" wrote in news:YO****************@newssvr27.news.prodigy.com : Thanks for the tip. As the number of files grows, I'll want to explore something more robust than Kill - and this seems like a good project to learn how to work with the API I can't imagine what could be more robust than Kill. IMO it's way too robust and, as a result, dangerous. I look at it as a remnant of VB's distant past, something that is continued to preserve compatibility with ancient applications, but not really modern in it's nature. You might be able to set a reference to "Microsoft Shell Controls and Automation" and get a number of options for file manipulation by creating an instance of the Shell32 object. But the interface here is rather obscure, perhaps arcane. I have written quite a bit of code using declared API functions, and I may say that I am happy I did not begin with file manipulation; it seems to me that this area is one of the more challenging parts of the API. -- Lyle (for e-mail refer to http://ffdba.com/contacts.htm) Nov 12 '05 #12

 P: n/a "Chuck Grimsby" wrote in message news:im********************************@4ax.com... On Tue, 23 Dec 2003 09:44:29 -0600, "Rick Brandt" wrote:This reminds me of a question that I had a while back. Has anyone ever come upwith an API call that would allow a custom Kill function that would result inthe deleted files being moved to the recycle bin? Private Type SHFILEOPSTRUCT hwnd As Long wFunc As Long pFrom As String pTo As String fFlags As Integer fAnyOperationsAborted As Boolean hNameMappings As Long lpszProgressTitle As String End Type Private Declare Function SHFileOperation Lib "shell32.dll" Alias _ "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long Private Const FO_DELETE = &H3 Private Const FOF_ALLOWUNDO = &H40 Private Const FOF_NOCONFIRMATION = &H10 Private Const FOF_SILENT = &H4 Public Sub Recycle(File As String, _ Optional Confirm As Boolean = False, _ Optional SilentOnError As Boolean = True) Dim FileOperation As SHFILEOPSTRUCT Dim lReturn As Long Dim sFileToDelete As String Dim lFlags As Integer ' Set File to delete. sFileToDelete = Trim(File) & vbNullChar ' Set flags. lFlags = FOF_ALLOWUNDO If Not Confirm Then lFlags = lFlags Or FOF_NOCONFIRMATION End If If SilentOnError Then lFlags = lFlags Or FOF_SILENT End If With FileOperation .wFunc = FO_DELETE .pFrom = sFileToDelete .fFlags = lFlags End With ' Send to recycle bin. lReturn = SHFileOperation(FileOperation) End Sub Excellent! Thanks. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Nov 12 '05 #13

### This discussion thread is closed

Replies have been disabled for this discussion.