Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Remove duplicates in Excel

Status
Not open for further replies.

smurfhell

MIS
Joined
Nov 5, 2004
Messages
45
Location
US
I have a script that scans folders/files for anything that hasn't been accessed in the last 3 months and writes the folder name to an Excel spreadsheet. However, this creates lots of duplicate entries. Would it be easier to remove the duplicates after scanning or limit the scanning under each folder if a certain condition is met?

Here's what I got so far: (paths and dates have been changed for testing purposes)

Code:
Dim objFSO, objFolder, colFiles, objSubFolder, objSubFile, strOld, strDateDiff
Dim objexcel, r, s
strFolder = "c:\fakeproj\"
'strTar = "c:\fakemove\"
strOld = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFolder)
Set colFiles = objFolder.Files
r = 2
set objexcel = createobject("Excel.application")
objexcel.workbooks.add
objexcel.cells(1,1).value = "Folder Name"
objexcel.visible = true
For Each objFile in colFiles
strOld = objfile.DateLastModified
strDateDiff = DateDiff("d", strOld, Now)
If strDateDiff >= strOld Then
objexcel.cells(r,1).value = objFolder
end if
next
scansubfolders(objFolder)
sortsheet()
msgbox "done"
Sub scansubfolders(objfolder)
set colfolders = objfolder.subfolders
for each objsubfolder in colfolders
set colfiles = objsubfolder.files
for each objfile in colfiles
If strDateDiff >= strOld Then
objexcel.cells(r,1).value = objFolder
r = r+1
end if
next
scansubfolders(objSubFolder)
next
end sub
sub sortsheet()
Set objRange = objExcel.ActiveCell.EntireColumn
Set objRange2 = objExcel.Range("A1")
objRange.Sort objRange2,,,,,,,1
End Sub
The root folder contains about 20 subfolders, each of these with about 100 subfolders, and then one last set of subfolders. i.e.
Project -> 1000s, 2000s, 3000s, etc. -> 1124, 1426, 2075, etc. I need just the actual project number listed (2075 instead of 2000s)
I hope this post makes sense.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top