How to monitor file changes?

Question

Microsoft

There is an excel file in the network folder that records user orders. Is there any way to automate notifying users that their order has been completed? Ideally, when the status changes, a letter is sent to the addressee immediately (well, or at the click of a mouse) (via the corporate Microsoft Exchange). I suspect that with the help of a VBS macro this can be done, but I myself am not strong in it.

Answer 1

Here is a similar task for nncron :

#( Задача_151321082013
WatchFile: "\\path\file.ext"
SingleInstance
Action:
START-APP: <любимая программа для отправки почты>
)#

There are also options for periodically checking the last date of writing to a file and sending a dynamically generated letter via SMTP - but this is already an additional plugin.
Under seven, you can install it as an application (an option in the installer), under XP you can install it as a service, but to launch applications you will need to use authorization options ( AsLoggedUser and others). Link to the latest version.
If you are interested in details, then here is the manual , and here is the forum.
If you have any questions - ask, I will answer.

Answer 2

You can do it with VBA. But it is not very clear how the table is arranged.
I can assume that in a certain cell of each line there is a status. Then we create the Worksheet_Change procedure and analyze whether the status cell has changed and how it has changed. If it contains, say, the word "Done", we send a letter to the address taken (or determined in another way) from a certain cell of the same line.
The letter can be sent via MS Outlook, or using some third-party program (eg, blat).

Answer 3

In the general case, nothing can be done just by the presence of such a file.
In order for status changes to cause an email to be sent, you need to change the program that writes to this file. And I suspect it's Microsoft Excel :) Do you have a problem with only one user being able to open such a file?
The algorithm is as follows:
In the excel file, you need to embed a macro to send a message by mail on the event "Change of data in a cell".
Let the excel have columns “Email”, “Order Status”
When the user changes the status in the “Order Status” column, VBA looks at the values ​​in the “Email” cell and sends an email to this email
I don’t remember if it is possible to “bind” an event in VBA to change the value of any cell in a given column.
There is another option:
Need a cron task to regularly check for changes in the excel file.
Let the excel have columns “Email”, “Order status”, “Old order status”
At the same time, “Old order status” is hidden from users, but it can be accessed through VBA
1. The user changed the status in the “Order Status” column, saved and closed the file
2. The cron task opens the file, looks for all lines where "Order Status" IS NOT EQUAL TO "Old Order Status"
2.1. For each such line sends a separate letter to the address specified in the same line in the cell "Email"
2.2. Inserts the value from “Order status” into the “Old order status” cell
3. The cron task closes the file
Disadvantage – not very fast and you have to wait until the cron finishes running
Third option:
Let the excel have columns “Unique order number”, “Email”, “Order status”
1. The user changed the status in the “Order status” column, saved and closed the file
2. Cron periodically copies the excel file to another folder with a new one unique name and knows what name the same file had the last time cron was run.
For example, the original file is c:/sales.xls, and the copies are named c:/temp/sales_1.xls, c:/temp/sales_2.xls , c:/temp/sales_3.xls etc.
3. After copying cron compares the last two files with each other
3.1. For each value in the "Unique Order Number" cell of the most recent file, the same value is searched for in the "Unique Order Number" column of the penultimate file.
3.2. The values ​​of the "Order Status" cells of the found lines from the old and new files are
compared. 3.3. If the value has changed, then an email is sent to the address specified in the "Email" cell of the changed line of the new file
4. Cron closes the file
5. Cron deletes all unnecessary files from the temporary folder, leaving only the latest
one end, but they are required to enter unique order numbers