excel macro to track changes in a folder

[youngEIT]

I am trying to use excel to track changes in a folder, if possible. every time someone updates a file, copy-pastes a new file or does any kind of change in that folder,excel would track it to generate an email and send it saying that a chance occured. my starting point is the code below, which I had used to track changes in a spreadsheet; wonder if i can build on it. any help is appreciated.
regards..

CODE:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim OLook As Object 'Outlook.Application
Dim Mitem As Object 'Outlook.Mailitem
Dim SendAnEmail As Boolean
Dim strBody As String

strBody = "Address = " & Target.Address & vbNewLine

strBody = strBody & Range("$a$" & Target.Row) & " = " & Target

'MsgBox strBody



'Exit Sub




If Not Intersect(Target, Range("A1:AK2000")) Is Nothing Then
Set OLook = CreateObject("Outlook.Application")
Set Mitem = OLook.createitem(0)
Mitem.to = "add email address here"
Mitem.Subject = "add subject here"
Mitem.body = strBody
Mitem.send
End If

End Sub

 

[pmover]

how would excel know that a file has been changed within a folder?

think about this . . . basically, the program would need to be executed periodically to check all files in a folder.

anytime a file is saved, the computer's date/time is used to identify when the file was modified or created. what is wrong with checking the folder itself and sorting the files by date/time properties. of course, the computer's time needs to be confirmed.

good luck!
-pmover

 

[rb1957]

i know we all use the programs we like/know to do things (our donut schedule here is drawn in AutoCad), but surely there's a specific software that does what you want to do ?

i mean you could schedule a run of Windows Explorer, output to a file, and run "difference".

is there a way to have Windows tell you that it's updated a file, maybe within Windows security settings ?

 

[electricpete]

The VBA forum at Tek-tips is very active with people knowledgeable in VBA. I have gotten some good help there. Here is the link:

http://www.tek-tips.com/threadminder.cfm?pid=707

You will find the interface at that forum is identical to eng-tips.... it's run by the same people.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[youngEIT]

rb1957, your idea of scheduling a windows explorer run sounds interesting. you mind walking me through it? i don't know where to start..

 

[handleman]

Why would you need to do this in Excel? There are scads of programs out there that are written just to monitor folders. Seems like it would be more cost-effective to plunk down about 40 bucks for one of those rather than trying to write your own.

http://www.topshareware.com/Total-Folder-Monitor-download-56652.htm

-handleman, CSWP (The new, easy test)

 

[handleman]

Probably should have mentioned that the link I posted is just one of the ones that showed up on a Google search. I've never used it or any like it myself.

-handleman, CSWP (The new, easy test)

 

[joerd]

There are also freebies out there, it won't even have to cost you 40 bucks. Check in Soureforge.net and

http://directory.fsf.org/category/mon/

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[rb1957]

i've no idea, and handleman has picked up on my suggestion ...