locked
Can VBA Project have a way to constantly check a server directory RRS feed

  • Question

  • Set up watch??? Is there a way for vba code to watch a directory and do something when a new file is added or an existing file is changed? In detail, I want to have a program that will constantly watch a director for any changes and send a Lotus Notes email notice.

    Jim Neely

    Sunday, January 13, 2013 12:03 AM

Answers

  • Hi Starfalls

    Not sure if this is a task you should do in VBA. I for myself would do this with a scheduled task that is running a Visual Basic Script file instead using the FileSystemObject. You can find tons of examples how to do this in the Web. Just search vor VBS FileSystemObject and Windows Script Host. After you found a file changed or was added/deleted you can start an application to do the rest or do it in VBScript as well for example with OLE automatisation of your Office Application of choice.

    The reason why I recommend to not to do it in VBA is: VBA is embedded in an (Office) application. To run VBA you have to run this application and Office isn't exactly the kind of application with small foodprint (ressources). So if you want to run this on a server you will loose ressouces (memory, CPU) that are just idling but are blocked to be used for the server itself.

    If you want to do it for example in Access you can do it in a hidden form with a timer. As soon as the timer event occurs (you can set it in steps of milliseconds) then you can run your VBA code that is scaning a directory with Dir() and then compare the content/timestamps with an existing list of files and find differences.

    HTH
    Henry
     "STARFALLS" schrieb im Newsbeitrag news:37b3237c-14de-4b03-a39c-d58bfef1b134@communitybridge.codeplex.com...

    Set up watch??? Is there a way for vba code to watch a directory and do
    something when a new file is added or an existing file is changed? In
    detail, I want to have a program that will constantly watch a director
    for any changes and send a Lotus Notes email notice.

    • Marked as answer by STARFALLS Wednesday, January 16, 2013 4:21 AM
    Tuesday, January 15, 2013 8:14 AM
  • if you let something run in VBA constantly, the program will get stuck, because you make an infinite loop.
    • Marked as answer by STARFALLS Thursday, January 17, 2013 12:02 AM
    Wednesday, January 16, 2013 8:48 PM

All replies

  • Hi Starfalls

    Not sure if this is a task you should do in VBA. I for myself would do this with a scheduled task that is running a Visual Basic Script file instead using the FileSystemObject. You can find tons of examples how to do this in the Web. Just search vor VBS FileSystemObject and Windows Script Host. After you found a file changed or was added/deleted you can start an application to do the rest or do it in VBScript as well for example with OLE automatisation of your Office Application of choice.

    The reason why I recommend to not to do it in VBA is: VBA is embedded in an (Office) application. To run VBA you have to run this application and Office isn't exactly the kind of application with small foodprint (ressources). So if you want to run this on a server you will loose ressouces (memory, CPU) that are just idling but are blocked to be used for the server itself.

    If you want to do it for example in Access you can do it in a hidden form with a timer. As soon as the timer event occurs (you can set it in steps of milliseconds) then you can run your VBA code that is scaning a directory with Dir() and then compare the content/timestamps with an existing list of files and find differences.

    HTH
    Henry
     "STARFALLS" schrieb im Newsbeitrag news:37b3237c-14de-4b03-a39c-d58bfef1b134@communitybridge.codeplex.com...

    Set up watch??? Is there a way for vba code to watch a directory and do
    something when a new file is added or an existing file is changed? In
    detail, I want to have a program that will constantly watch a director
    for any changes and send a Lotus Notes email notice.

    • Marked as answer by STARFALLS Wednesday, January 16, 2013 4:21 AM
    Tuesday, January 15, 2013 8:14 AM
  • Too bad, I was hopeing for something like my UNIX has. Thanks for the info I will check it out


    Jim Neely

    Wednesday, January 16, 2013 4:21 AM
  • Hi Jim

    I think you mix here something up: Unix is an operating system, VBA is an application development environment that runs under Windows in an (Office) Application.

    If you are looking for something like your UNIX has then you should not ask for VBA functionality but ask in a Windows community. Maybe the Windows Filesystem supports similar functionality or is able to raise events when any changes are made in a particular folder. At least all Antivirus system get informed if files are added/changed. In this case probably you would have to write an application that is running as service and then is doing the actions you want to do. But again: It would be a very silly idea to use VBA or Office applications to run as service under Windows. You probably would better write such things in Visual Studio using C++, C#, VB.Net or what ever.

    Henry

    "STARFALLS" schrieb im Newsbeitrag news:0b9a8ee9-fbbb-41ee-b172-6d2df36ddef5@communitybridge.codeplex.com...

    Too bad, I was hopeing for something like my UNIX has. Thanks for the
    info I will check it out


    Jim Neely

    Wednesday, January 16, 2013 7:53 AM
  • Hi again

    I quickly searched in MSDN (even this is the wrong forum for this question) and found following article:
    http://msdn.microsoft.com/en-us/library/system.io.filesystemwatcher.changed.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
    This should exactly fit your needs and it will probably be based on events of the file system.

    You could now write .NET code and run it and would get informed about changes in the directory and files in this directory.
    In addition: If you want to use it in VBA you would have to write a COM wrapper for it and could then use it also in VBA.

    HTH
    Henry

    "Henry Habermacher"  schrieb im Newsbeitrag news:465af247-e380-4fe8-9fd9-f831af3d417e@communitybridge.codeplex.com...

    I think you mix here something up: Unix is an operating system, VBA is
    an application development environment that runs under Windows in an
    (Office) Application.

    Wednesday, January 16, 2013 8:06 AM
  • if you let something run in VBA constantly, the program will get stuck, because you make an infinite loop.
    • Marked as answer by STARFALLS Thursday, January 17, 2013 12:02 AM
    Wednesday, January 16, 2013 8:48 PM
  • Yeah, I was hoping you could run something in the background like you can in UNIX. It's not all that important, there are other ways.


    Jim Neely

    Thursday, January 17, 2013 12:03 AM
  • That's not true. Depends on where you let it run. In Access for example you have timers to trigger events based on elapsed time (in the meantime nothing will run or be stuck) and you have the DoEvents() command.

    In addition: VBA can register for events that may be triggered by other objects and also in this case nothing is running and will get suck.

    Henry

    Thursday, January 17, 2013 3:14 AM