Dear Experts

Is there a way of being able to check a network folder for file names
containing the same piece of text?

I have a spreadsheet which people now automatically save to a specified
folder "W:\Accounts\Site Daily Bankings\To be posted".

However some people are saving their file more than once (a user problem that
unfortunately won't go away) which I need to filter and delete. The file's
are saved with the site name, then a date stamp, i.e. "SiteName Banking
09Mar2006_152600"

Ideally I would like to search on the first piece of text as it is always the
site, but how would I delete the others ?;

Ie, if I have

SiteName Banking 09Mar2006_152600
SiteName Banking 09Mar2006_152599
SiteName Banking 09Mar2006_152598

I would want to keep "........152600" and delete the other two.

Anybody any idea's?,

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

http://support.microsoft.com/kb/185476/EN-US/
How To Search Directories to Find or List Files

http://support.microsoft.com/kb/185601/EN-US/
HOW TO: Recursively Search Directories by Using FileSystemObject

http://support.microsoft.com/kb/186118/EN-US/
How To Use FileSystemObject with Visual Basic

are some articles on listing files in a directory. That should get you
started.

--
Regards,
Tom Ogilvy



"Ozzie via OfficeKB.com" wrote:

Dear Experts

Is there a way of being able to check a network folder for file names
containing the same piece of text?

I have a spreadsheet which people now automatically save to a specified
folder "W:\Accounts\Site Daily Bankings\To be posted".

However some people are saving their file more than once (a user problem that
unfortunately won't go away) which I need to filter and delete. The file's
are saved with the site name, then a date stamp, i.e. "SiteName Banking
09Mar2006_152600"

Ideally I would like to search on the first piece of text as it is always the
site, but how would I delete the others ?;

Ie, if I have

SiteName Banking 09Mar2006_152600
SiteName Banking 09Mar2006_152599
SiteName Banking 09Mar2006_152598

I would want to keep "........152600" and delete the other two.

Anybody any idea's?,

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Thanks for your reply Tom,

The links were useful,

my main problem though is still how to;

extract the first piece of text (being the name of the site) then the last
piece of text (being the date stamp), to sort on the date stamp and then
delete, say the MIN and requery incase there is more than one duplicate file?

I just don't know how to write this code? or if there is a better way ?

Any idea's?



Tom Ogilvy wrote:
http://support.microsoft.com/kb/185476/EN-US/
How To Search Directories to Find or List Files

http://support.microsoft.com/kb/185601/EN-US/
HOW TO: Recursively Search Directories by Using FileSystemObject

http://support.microsoft.com/kb/186118/EN-US/
How To Use FileSystemObject with Visual Basic

are some articles on listing files in a directory. That should get you
started.

Dear Experts

[quoted text clipped - 21 lines]

Anybody any idea's?,

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

as you gather your list of file names, you can parse out the information you
need as shown in the is demo from the immediate window:

s = "SiteName Banking 09Mar2006_152598.xls"
? s
SiteName Banking 09Mar2006_152598.xls
? left(s,17)
SiteName Banking
? mid(s,18,9)
09Mar2006
? Mid(s,27,255)
_152598.xls


to me, however, it would seem easiest to write you filelist to a worksheet.
then sort the worksheet and delete any files you want to keep (from the
list). then loop through the list of names and delete them

for each cell in Range("A1",Range("A1").End(xldown))
Kill cell
Next

--
Regards,
Tom Ogilvy



"Ozzie via OfficeKB.com" wrote:

Thanks for your reply Tom,

The links were useful,

my main problem though is still how to;

extract the first piece of text (being the name of the site) then the last
piece of text (being the date stamp), to sort on the date stamp and then
delete, say the MIN and requery incase there is more than one duplicate file?

I just don't know how to write this code? or if there is a better way ?

Any idea's?



Tom Ogilvy wrote:
http://support.microsoft.com/kb/185476/EN-US/
How To Search Directories to Find or List Files

http://support.microsoft.com/kb/185601/EN-US/
HOW TO: Recursively Search Directories by Using FileSystemObject

http://support.microsoft.com/kb/186118/EN-US/
How To Use FileSystemObject with Visual Basic

are some articles on listing files in a directory. That should get you
started.

Dear Experts

[quoted text clipped - 21 lines]

Anybody any idea's?,

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Tom,

I follow what you say and would probably be an easier way of doing it,
however the users that would be doing this are 'little old ladies' with very
basic skill.

I have attached the code that I have managed to put together with help from
others, which works by going through each workbook and asking the delete
question.

Sub DeleteFiles()

Dim FSO As Object
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
'Dim LastName As String
'Dim FirstName As String

'Dim Temp
'Temp = Split(sFolder)

Set FSO = CreateObject("Scripting.FileSystemObject")
sFolder = "W:\Accounts\A_Site Daily Bankings\Live - to be posted"

If sFolder < "" Then

Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files

For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
If InStr(1, file.Name, findloc) 0 Then
resp = MsgBox("Do you want to delete file " & file.Name,
vbYesNo, "Delete Files")
If resp = vbYes Then file.DELETE
End If
End If
Next file
End If ' sFolder < ""

Set FSO = Nothing

End Sub

but again, I only want to see the oldest duplicate (so it can be deleted) and
I can't seem to write the code to even tell if the file is duplicted ? ie
first name and last name.

Cheers matey

David


Tom Ogilvy wrote:
as you gather your list of file names, you can parse out the information you
need as shown in the is demo from the immediate window:

s = "SiteName Banking 09Mar2006_152598.xls"
? s
SiteName Banking 09Mar2006_152598.xls
? left(s,17)
SiteName Banking
? mid(s,18,9)
09Mar2006
? Mid(s,27,255)
_152598.xls

to me, however, it would seem easiest to write you filelist to a worksheet.
then sort the worksheet and delete any files you want to keep (from the
list). then loop through the list of names and delete them

for each cell in Range("A1",Range("A1").End(xldown))
Kill cell
Next

Thanks for your reply Tom,

[quoted text clipped - 27 lines]

Anybody any idea's?,

--
Message posted via http://www.officekb.com

so the little old ladies can't look at a sorted list of file names and delete
the rows that contain file names that contain the most recent date (where
there are duplicates) or delete the row (where the file name is not
duplicated), but they can look at individual filenames one at a time in a
prompt and determine whether to delete them or not.

anyway, you showed
SiteName Banking 09Mar2006_152600
SiteName Banking 09Mar2006_152599
SiteName Banking 09Mar2006_152598

where all the dates are the same date. What is the number on the end?

then in the latest you spoke of

i.e. First name and last name

Do you actually want the macro to make the decision to delete.

Would need to know specifics about the filenames and how to distinguish what
part of the name would be duplicated and what would indicate which is the one
to retain.

--
Regards,
Tom Ogilvy


"Ozzie via OfficeKB.com" wrote:

Tom,

I follow what you say and would probably be an easier way of doing it,
however the users that would be doing this are 'little old ladies' with very
basic skill.

I have attached the code that I have managed to put together with help from
others, which works by going through each workbook and asking the delete
question.

Sub DeleteFiles()

Dim FSO As Object
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
'Dim LastName As String
'Dim FirstName As String

'Dim Temp
'Temp = Split(sFolder)

Set FSO = CreateObject("Scripting.FileSystemObject")
sFolder = "W:\Accounts\A_Site Daily Bankings\Live - to be posted"

If sFolder < "" Then

Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files

For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
If InStr(1, file.Name, findloc) 0 Then
resp = MsgBox("Do you want to delete file " & file.Name,
vbYesNo, "Delete Files")
If resp = vbYes Then file.DELETE
End If
End If
Next file
End If ' sFolder < ""

Set FSO = Nothing

End Sub

but again, I only want to see the oldest duplicate (so it can be deleted) and
I can't seem to write the code to even tell if the file is duplicted ? ie
first name and last name.

Cheers matey

David


Tom Ogilvy wrote:
as you gather your list of file names, you can parse out the information you
need as shown in the is demo from the immediate window:

s = "SiteName Banking 09Mar2006_152598.xls"
? s
SiteName Banking 09Mar2006_152598.xls
? left(s,17)
SiteName Banking
? mid(s,18,9)
09Mar2006
? Mid(s,27,255)
_152598.xls

to me, however, it would seem easiest to write you filelist to a worksheet.
then sort the worksheet and delete any files you want to keep (from the
list). then loop through the list of names and delete them

for each cell in Range("A1",Range("A1").End(xldown))
Kill cell
Next

Thanks for your reply Tom,

[quoted text clipped - 27 lines]

Anybody any idea's?,

--
Message posted via http://www.officekb.com