×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

excel macro to track changes in a folder

excel macro to track changes in a folder

excel macro to track changes in a folder

(OP)
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

  

RE: excel macro to track changes in a folder

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

RE: excel macro to track changes in a folder

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 ?  

RE: excel macro to track changes in a folder

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.

RE: excel macro to track changes in a folder

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

RE: excel macro to track changes in a folder

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)

RE: excel macro to track changes in a folder

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)

RE: excel macro to track changes in a folder

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

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources