By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 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
Share this Question
Share on Google+
12 Replies


P: n/a
rkc

"deko" <dj****@hotmail.com> 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"
<dj****@hotmail.com> 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 As
WIN32_FIND_DATA) As Long
Search help for "kill"
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?


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" <dj****@hotmail.com> 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
rkc

"deko" <dj****@hotmail.com> 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"
<dj****@hotmail.com> wrote:
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!


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" <rk*@yabba.dabba.do.rochester.rr.nope> wrote in message
news:QB*******************@twister.nyroc.rr.com...

"deko" <dj****@hotmail.com> 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" <dj****@hotmail.com> 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 <Mi************@Invalid.Com> wrote in
news:Xn*******************@130.133.1.4:
"deko" <dj****@hotmail.com> 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" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"deko" <dj****@hotmail.com> 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
rkc

"deko" <dj****@hotmail.com> 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" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"deko" <dj****@hotmail.com> 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" <c.*******@worldnet.att.net.invalid> wrote in message
news:im********************************@4ax.com...
On Tue, 23 Dec 2003 09:44:29 -0600, "Rick Brandt"
<ri*********@hotmail.com> 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 in
the 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.