PDA

View Full Version : Macro that compares directory contents to...



toonz
06-10-2011, 06:14 AM
First, here is the current macro Im using in excel

Sub fileInfo()
Dim fso As Scripting.FileSystemObject
Dim folder As Scripting.folder
Dim file As Scripting.file
Dim rngEntry As Range
Set rngEntry = Range("a1")
Set fso = New FileSystemObject
Set folder = fso.GetFolder("H:\DCDM\UL1")
For Each file In folder.Files
If LCase(Right(file.Name, 4)) = ".pdf" Then
rngEntry = file.Name
rngEntry.Offset(0, 1) = file.Size
rngEntry.Offset(0, 2) = file.DateCreated
Set rngEntry = rngEntry.Offset(1)
End If
Next file
End Sub


All it does at this point is list all of the contents from a directory and inserts the filenames in column 1. What Id like it to do is compare the data in column 1, search the directory, and insert all the filenames that dont currently appear on the list starting at the cell right below the last record. How can I do this? Your help is greatly appreciated!

Kenneth Hobs
06-10-2011, 07:20 AM
Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Sub fileInfo()
Dim fso As Scripting.FileSystemObject
Dim folder As Scripting.folder
Dim file As Scripting.file
Dim rngEntry As Range

Set rngEntry = Range("A" & Rows.Count).End(xlUp)
Set fso = New FileSystemObject
'Set folder = fso.GetFolder("H:\DCDM\UL1")
Set folder = fso.GetFolder("X:\pdf")
For Each file In folder.Files
'If file.type="Adobe Acrobat Document" then
If LCase(Right(file.Name, 4)) = ".pdf" Then
If Not FindInRange(file.Name, Range("A1", Range("A" & Rows.Count).End(xlUp))) Then
rngEntry = file.Name
rngEntry.Offset(0, 1) = file.Size
rngEntry.Offset(0, 2) = file.DateCreated
Set rngEntry = rngEntry.Offset(1)
End If
End If
Next file

Set file = Nothing
Set folder = Nothing
Set fso = Nothing
End Sub

Function FindInRange(var As Variant, aRange As Range, Optional caseSensitve As Boolean = False) As Boolean
If caseSensitve Then
If Not aRange.Find(var, LookAt:=xlWhole, MatchCase:=True) Is Nothing Then FindInRange = True
Else
If Not aRange.Find(var, LookAt:=xlWhole) Is Nothing Then FindInRange = True
End If
End Function

toonz
06-10-2011, 07:32 AM
Thanks. Ill try it out