'Access denied' error received when trying to save documents in Excel

[Eruditio]

I'm struggling to think of a reason why I should be getting an 'Access denied' error when trying to save an Excel document opened through a program written in VB 2010 Express.

The program is essentially glorified Excel automation. It converts information from hundreds of Excel documents into a few small text files in order to improve operation speed. The software is designed for use by no more than a dozen or so people in the office who are all editing these Excel documents (through the software), and saving changes. They edit a given document by selecting it in a list in the main form, which then changes the document to writeable and opens it for them. A FileSystemWatcher detects changes in the folder containing the Excel documents so that the aforementioned text files can be updated when the user saves the Excel document.

The problem arises when the software has multiple simultaneous users. If two users are simultaneously editing different Excel documents (it won't allow them to open the same one), the user that opened their Excel document first is able to save, but the other is given the 'Access denied' error. The document can be saved to a different location through Save As.

I suspect this has something to do with accessing the shared text files. In all cases where information needs to be written to these files, the I/O operations are contained within a While loop so that the program continues to try and write to the document until it is available.

A couple of other things of note: first is that if I and my colleague both open the program and, say, he opens an Excel document through the software and THEN I do, he will see a window flash up quickly (too quickly to see whether it has content, or what the content might be), and vice versa. Secondly, after saving (or being denied access), we will both notice our cursors flashing with an egg timer next to it. It seems that the software continues to work on something after we've tried saving, but I can't quite figure it out as an infinite loop would crash the software, but this just causes it to lag.

I know it's a bit of a long shot asking if anyone can help with this because I can't imagine that the exact circumstances are often replicated around the world. There are about a dozen or so long methods involved in this process so I'm not sure what code would be helpful to post and I'm not even sure where the issue is occurring. I should note that I don't receive any error messages from my software, only from Excel, and my software only catches IOExceptions from these methods.

I've tried to make this as concise as possible, but realise that I may well have done a poor job of describing the problem. If you need any further explanation or do require some supporting code, then please let me know. I appreciate any and all help!

[Reverend Jim]

It converts information from hundreds of Excel documents into a few small text files

Let's assume your Excel files are named ss-0001.xls, ss-0002.xls, etc.

I'm guessing you have two processes at work here. One process runs independently of the GUI app and renders many Excel files into a few text files. This process runs whenever an Excel file is updated.

The second process is run by the user(s) and is used to load and update the Excel files. Let's assume two users modifying files ss-0001.xls and ss-0002.xls. User1 saves ss-0001.xls causing the generation of text files. User2 tries to save ss-0002.xls while the generator is reading it. This could be one source of the interlock.

One suggestion is that when user1 selects an Excel file for editing, that files is copied into a temporary file with a name like

ss-0001-user1.xls

All edits are done on the user specific version of the files. That way the generator can use only the non-user specific files. When the user wants to save the file, the changes get saved to the temp file. Once the file has been written, the save code then enters a loop that repeatedly tries to delete the original file. Once successful it then renames the temp file to the original name.

save temporary spreadsheet

do until original file deleted
    sleep for a short time
loop

rename temp file to original name

The file system watcher would be triggered on the appearance of the newly renamed file and would regenerate the text files. The code that allows a user to open an Excel file could be written as

get user-selected filename

if there is a user-specific version of this file
    notify the user that the file is in use
else
    copy the file to ss-####-thisuser.xls
    open the file for editing in Excel
end if

As a side benefit, you could have another process running which periodically checks for temporary files that have been in existence for too long a time and are possibly orphaned (perhaps a user forgot to save before leaving his/her desk).

I had to deal with similar scenarios when managing several thousand files, a few dozen users and several score of automated processes.

[Eruditio]

This seems like it could work. I was thinking something similar over the weekend. You have nicely solidified those thoughts (and I appreciate the well-organised pseudo-code). I'll give this a shot and reply with the result.

Many thanks!

[Reverend Jim]

Let me know how it goes. I had to avoid interlocks on many different automated processes that could not respond to pop-ups. It can get tricky.

[Eruditio]

I've tried to implement this today. After implementing it, I continued to receive errors but the message is no longer coming from Excel, but from the VB software. It seemed that the FileSystemWatcher was being triggered by file changes made by other users. The FileSystemWatcher is only turned on when users open an Excel document for editing through the software. Since both users have Excel documents open, both are watching the directory containing all the Excel documents.

I've tried to rectify this problem by changing the filter for the FileSystemWatcher on a per-user basis. On startup of the program (in the New() constructor), the filter is changed to include the user's username so that only their temporary documents trigger the FileSystemWatcher (which triggers after the edited temporary document, with the -username suffix, is closed).

Doing some debugging with this, I've ecountered another problem. Those windows that were flashing too quickly to be see are now remaining on-screen (although I've tested with two colleagues other than the one I was testing with previously). The window is empty (transparent) so I still can't see a message but the title is in reference to Microsoft .NET. This message prevents further use of the software and closing it will cause the software to crash (familiar send/don't send dialogue appears). If user1 first opens some document 001.xlsx (which manifests itself as 001-user1.xlsx according to the process you outlined previously) and then user2 tries to open document 002.xlsx, then both will be able to open those documents (001-user1.xlsx and 002-user2.xlsx will be open on their respective computers), but user1 will receive this mysterious .NET message and will have to close their software, with 001-user1.xlsx remaining on-screen.

I never receive this message box on a single-user basis. I don't understand what could be causing it because all IOErrors are caught and the FileSystemWatcher is definitely not triggering until after the Excel documents are CLOSED due to careful control of its EnableRaisingEvents property throughout the code. Even in the case when it does trigger, it can only do so because of one's own actions and not because another user is opening an Excel document.

I will try and outline, simply, the process that is occurring when one of these Excel documents is opened for editing.

A button's click event is handled by the following method:

Private Sub EditDocument()
    'Ensure the FileSystemWatcher does't raise spurious events
    FileSystemWatcher.EnableRaisingEvents = False
    'Checks three directories to ensure they exist. If they don't, it creates them. 
    'This checks the directory containing the Excel files.
    CheckDirectories()

    If FileInUse Then
        'Inform user that the file is already open elsewhere and cannot be edited
    Else
        'Makes a copy of the document before editing
        File.Copy(originalDocument, temporaryDocument)
        'Ensures the temporaryDocument is writeable
        File.SetAttributes(temporaryDocument, FileAttributes.Normal)
        'Opens Excel with the temporaryDocument loaded
        Process.Start(temporaryDocument)
    End If

    'Now allow the FileSystemWatcher to raise events; the document is opened for
    'editing and the software is awaiting changes to save
    FileSystemWatcher.EnableRaisingEvents = True
End Sub

The FileSystemWatcher event handler then looks like this:

Private Sub FileSystemWatcher_Changed(ByVal sender As System.Object, _
                                      ByVal e As System.IO.FileSystemEventArgs) _
                                      Handles LaunchedDocumentsWatcher.Changed
    'Retrieves the name of the document being changed from the method arguments
    Dim changedDocument As String = e.Name.Substring(2)
    'Variable used later when deleting a file
    Dim deleted As Boolean = False
    'Retrieves the original document name from the changedDocument (i.e., retrieves the
    'original document name from the temporary document, undoing renaming operations in 
    'the above method)
    Dim originalDocumentName As String = 'Some string manipulations

    'Continue trying to delete the original document until successful
    While Not deleted
        If Not FileInUse(originalDocumentName) Then
            File.SetAttributes(originalDocumentName, FileAttributes.Normal)
            File.Delete(originalDocumentName)
            If Not File.Exists(originalDocumentName) Then
                deleted = True
            End If
        End If
    End While

    'Now rename the temp document (changedDocument) to the original document name
    File.Move(changedDocument, originalDocumentName)
    'Performs some I/O operations with text files containing Excel document info
    AutoUpdateDocument(originalDocumentName)
    'Ensures the Excel document is ReadOnly to discourage changes outside of the program
    File.SetAttributes(originalDocumentName, FileAttributes.ReadOnly)
End Sub

I won't include an outline of the AutoUpdateDocument method because I don't believe it is causing the problem. As I said, the FileSystemWatcher shouldn't even be triggering until an Excel document is closed (and I've tested that it isn't triggering), and the error arises before then.

[Eruditio]

I made a small, but potentially important, edit to the above post. Please see the emboldened 'user1' in the third paragraph.

[Reverend Jim]

Lets try this setup as a possibility. I'm going to make a few assumptions:

• Excel files are in \server\excel\prod
• Working files are in \server\excel\work
• generated text files are in \server\excel\text
• prod files named datafile-###.xls
• work files named datafile-###-user.xls

You want only one copy of the FileSystemWatcher (FSW) running. I'm assuming this would be on the server hosting the files. FSW could be set to monitor FileName events and could ignore everything but Rename events. The Filter would be set to "*.xls" in the prod folder. The reason you want to use Rename events only is because you can get multiple Change events for one change and you don't want to trigger multiple times for one change. Also, a Rename is virtually instantaneous. By the time the action causes the trigger the file is available for use.

When a user selects an Excel file via the FileOpenDialog, your code would check to see if there is already a working file for that Excel file. If there is, then the user is told so, and possibly also which user has that file and for how long. If not then a working file is created from that Excel file and opened for editing by the user.

If the user abandons his edits then the working file is deleted. If the user saves the changes, then after the changes are written to the working file, the original (in prod) is deleted and the temp file is renamed to the original name. This triggers a FileName event in FSW. FSW then processes the Excel file to generate the text file(s). Actually, FSW should spawn another process to do the generation because you don't want to delay any folder watch events.

If the generation takes any significant time then you may want to use the same type of interlock to prevent a user from saving the Excel file while it is being processed.

So here are the interlocks you want to prevent:

Two users editing a file at the same time

This can be prevented by having the code check for an existing work file for the requested Excel file.

User trying to save a file that is currently being used to generate a text file

This can be prevented by having the generator create a prod file named datafile-###.gen. The user app could go into a wait loop until the file is safe for writing. Or you could just go ahead and copy the xls file. There shouldn't be a problem with this as the generator would just get retriggered on the next save.

The user process would look like

if the user selected an Excel file
    if there exists a file of the form work\datafile-###-username.xls then
        tell user the file is in use, by who and for how long
    else
        copy datafile-###.xls to datafile-###-username.xls
    end if
end if

When the user wants to save his changes

save working file
copy working file to prod\datafile-###.tmp

do while there is a file named datafile-###.gen
    sleep briefly
loop

delete prod\datafile-###.xls
rename prod\datafile-###.tmp to datafile-###.xls
delete work\datafile-###-user

You could include a test in the loop for a file-in-use for too long a period. You should also write a monitoring process to inform you if the generator gets stuck. All of my automated process setn an alert to one of the System Control ops (manned 24x7) as well as to the on-call cell phone.

The generator could keep a list of the last saved dates of each file and process only those for which the dates are newer. The generator process could look like

do

    didfile = false

    for all xls files in prod
        if this file has been updated then
            create a file prod\datafile-###.gen
            generate \text\files
            delete prod\datafile-###.gen
            update date/time for this file 
            didfile = true
        end if
    next

loop until not didfile

[Reverend Jim]

Another thought on the server process. You could bundle the FileSystemWatcher and the generation process in one app and just make the generator a separate thread. They could share a common structure which would be a list of files that were renamed. As long as you also share a variable that can be used as a lock so that both threads can not update the list at the same time everything should be fine. I've never programmed a multi-threaded app in vb but the basics seem pretty, well, basic. It would be easier for two threads in one task to communicate than for two separate tasks.

[Eruditio]

I'm having some trouble with this implementation. You say that I want only one copy of FSW running through some monitoring application, and that it should trigger through a Renamed rather Changed event.

In my current implementation, I use a Changed event on my FSW with a LastWrite NotifyFilter. As mentioned, the EnableRaisingEvents property is set from False to True and back again throughout the code and carefully defined points so that it doesn't trigger spuriously. As an example, it would be triggered on Excel launch, but I only want it on Excel close. At the moment, I get that to happen, but with all of the trouble already outlined.

In your post above, you suggest that I rename the working document from datafile-###-user back to datafile-### after saving (and presumably closing) the working document and that it is this rename that triggers my FSW. The problem I am having is in detecting this save and close. If I make my sole FSW, running separately from my main application, responsive to Changed events, then I don't get the fine control I have currently because it's a separate app. It's either on or off. That means it triggers when the Excel document is launched, which is too early, because 1) the file is in use, obviously, and 2) no changes have been made yet! On the other hand, if I make my sole FSW respond to name changes, I don't know how to trigger the name change in the first place because I can't think of a way to detect that the Excel document has been saved and closed without another FSW.

I hope that makes sense. It seems like I'm missing something simple here. I don't know why it's causing so many problems! Two users can presumably open and edit Excel documents manually from the shared folder. Are you certain this is a problem with multiple FSWs running simultaneously? Of course, if there's even a chance, I'm willing to try and fix it this way, but it has me stumped at the moment.

[Eruditio]

I've given this some more thought and realised I hadn't properly considered the implications of you separating your working and final documents into two folders.

I'm going to try this now and get back to you. It seems possible to be watching a local (non-shared) directory with a local FSW that would trigger, as before, after an Excel document has been saved and closed. This would perform the file movements/renames, as you've outlined, in the 'prod' folder, which is being watched by a sole, overarching FSW ran on the separate app. I'll let you know how it goes.

[Reverend Jim]

Before I retired I always found it useful, when I got a request, to rewrite the request in my own words and send it back to the person making the request with the question, "is this what you are asking for?". I found that in most cases what I thought the user wanted was not in sync with what the user said he wanted. So let me restate the problem to make sure we are in agreement.

• You have hundreds of Excel files in a shared folder.

• These files can be edited by multiple users.

• The information in these files is used to generate a smaller number of text files.

• When an Excel file changes, the text files must be recreated

You can't have more than one file watcher active at a time. If the file watcher was part of the user app then every time any user save his changes, ALL copies of the file watcher would trigger the same event. Therefore you must have only one file watcher running and logically it should be on the server.

Also, a server is generally much more powerful than any client PC. Because the files are stored on the server there is no network latency when processing the files. Therefore it should take much less time to generate the text files on the server than on a client. It is also less likely that the server will be interrupted while processing. Client machines can be subject to power outages and other interruptions (user shutdown, user spills coffee on keyboard, user accidentally kicks power cord, etc).

A rename is a virtually instantaneous event. A file either has the old name or the new name. There is no transition period. A save, on the other hand, has a period during which the file is open and being written. During that period the file is neither in the old state or the new state. Various factors can affect the length of this transition period, such as the current load on the client CPU, the amount of network traffic, the size of the file, etc. Because of this you do not want to trigger the file watcher until the action is complete. I have found that renaming the file as the very last step is the most reliable way to say "I am completely done with this file".

As a test I created a file watch on D:\Temp*.txt with only "Last Write" enabled. I loaded swap.txt into TextPad then did a save. The "modified" event was triggered three times. I then changed the event filter to "File Name". When I renamed swap.txt to swap.log I got one trigger. When I renamed it back I got another single trigger. By using a rename rather than a change event you avoid having to filter spurious events.

[Eruditio]

Yes, you're more or less right. The text files are edited, rather than recreated.

There are some limitations as to what I can do with share files and folders. I've been told previously that I'm not allowed to run programs on the same server that is hosting the files. We will have a computer for my department soon that will be able to run the monitoring process continuously, but that's the best I've got. So I will deal with the latency. I don't believe it will be a problem due to the small number of users.

I understand everything you're saying about the differences between LastWrite and FileName. But I can't rename a file until it is closed. I suppose there are ways to monitor that beyond using the Changed event. I should also reiterate that a user's FSW will trigger only for changes they made as the FSW filter is modified at run time to track documents containing their username. I thought that perhaps there was an inherent problem with having multiple FSWs watching a particular directory. Maybe I haven't got it all quite right and a user's FSW is triggering due to another's actions and that is what is causing this mysterious error message.

I appreciate all of your advice so far. I'm still trying to implement what I mentioned in my previous message. If this doesn't work, then perhaps I need to reconsider how the software is keeping track files opening and closing.

[Reverend Jim]

The text files are edited, rather than recreated.

I'm confused. Do the users edit the text files or the Excel files?

It occurred to me as I was coding up a shell example that because you are rendering many Excel files down into a few text files, you are likely going to have to process all of the Excel files every time any Excel file is modified. This might not be the case, but because I do not know the nature of the files I can't really make an assumption either way. Depending on the situation this may simplify the processing. If you have to process all of the Excel files then it is not necessary to know which file has changed, only that a change has been made.

[Reverend Jim]

We had a similar situation where we had a client computer set up to continuously run a river flow optimization program. It was pretty CPU intensive so we decided it would be better to not have it running on the server.

[Eruditio]

The user edits the Excel files. All of the hundreds of Excel files have their information converted into a single line, and all of these lines are amalgamated into one text file. When a user changes an Excel file, the FSW waited for the save and close, and then it found the line containing the information for that particular text file and changed it according to the changes made to the text file. There is a method in the software, called SpreadsheetToTextLine(), which converts the changed Excel file to a single line of text with all of the relevant information. In the case of edits, it only needs to convert the changed Excel file. Once per day, overnight, there is a separate program which converts the whole lot fresh. This is in case anything goes wrong in the day due to, for example, edits of the Excel documents outside of the software. A fail-safe, if you like.

Still working on your previous implementation. Having a few problems, but I'm hoping I'll resolve it soon.

[Eruditio]

The problem I'm having with the latest implementation is this: when saving a file in a local folder, about four extensionless files are created before finally saving the Excel document. They have a number code as their Name property. In a shared directory, a temporary file with the name ~$datafile-user.xlsx is created first. This is what was triggering my event beforehand, but not in a local folder. Why is this file only created in a shared directory?

[Reverend Jim]

That simplifies things. With a little luck I'll be able to post some skeleton code for the user and control apps showing the interlock logic. This will be my first multi-threaded app under VB, but I am optimistic it will work.

[Eruditio]

Just so that you are aware, I have continued to try and fix this problem. I'm yet to test with multiple users as my colleagues are currently using an old version of the software (bugs and all), but it is at least functional on one computer (my own) with separate user and control apps.

[TnTinMN]

The problem I'm having with the latest implementation is this: when saving a file in a local folder, about four extensionless files are created before finally saving the Excel document.

You may find this article to educational on this issue.
Click Here

[Reverend Jim]

I wrote up two skeleton apps. The first is the server that runs the folder watch and the generator. The second is the client app. Both are missing the code to load and process the Excel files (hard to do if you don't know what the processing or data are). I've also attached the zipped project files.

Server App

Imports System
Imports System.Collections

'                                                                                                   
'  Name:                                                                                            
'                                                                                                   
'    EruditioServer (catchy name, don't you think?)                                                 
'                                                                                                   
'  Description:                                                                                     
'                                                                                                   
'    This app monitors the folder given in PRODFILES and watches for rename events on xls files.    
'    It assumes that this will only ever happen when a .tmp file is renamed to .xls upon the        
'    completion of an edit by a user.                                                               
'                                                                                                   
'    When a .tmp is renamed to .xls, this app starts a background thread which takes the newly      
'    edited .xls file, opens it to create a one line summary, then updates a summary file with      
'    that summary.                                                                                  
'                                                                                                   
'  Notes:                                                                                           
'                                                                                                   
'    This code is just a skeleton. It includes no extra code to actually process the Excel files.   
'    That code is left to the implementing programmer to fill in.                                   
'                                                                                                   
'    I understand that all of the .xls files are processed automatically during the night to        
'    ensure that no summaries were missed. It should be an easy matter to add that functionality    
'    into this code by creating a timer which would scan the PRODFILES folder and add all .xls      
'    files into the queue. This would cause the background thread to regenerate all of the summary  
'    lines without requiring a separate task. You also might add a button which would trigger the   
'    same "mass" generation in case it needed to be done on demand or for testing purposes.         
'                                                                                                   
'    While the background (generator) thread is active the start/stop button will be coloured       
'    green. When inactive it will be red. You can fancy this up as you want. I also included a      
'    progress bar which is currently unused. If you decide to go with the timer or on-demand        
'    option you may decide to use the ProgressChanged event of the background thread to update      
'    the progress bar as the files are processed.                                                   
'                                                                                                   
'    I also strongly recommend that you create a log file which can be updated from the AddLog      
'    sub. Log files are indispensible in diagnosing problems with applications like this.           
'                                                                                                   
'  Audit:                                                                                           
'                                                                                                   
'    2012-07-10  RevJ  Original code (for which no liability is assumed)                            
'                                                                                                   

Public Class Form1

    'changeQueue is a queue (FIFO) which contains the names of files that have been renamed.        
    'I don't know if such a structure is thread-safe so in order to prevent simultaneous            
    'update attempts by two threads, each thread will ise queueLocked in order to grab              
    'exclusive access to the queue.                                                                 

    Private changeQueue As New Queue(Of String)     'queue of renamed files                         
    Private queueLocked As Boolean                  'lock to coordinate access to queue             
    Private bgRunning As Boolean                    'indicates status of background thread          

    Const PRODFILES = "D:\script\dani\eruditio\prod"        'folder containing Excel files          
    Const WORKFILES = "D:\script\dani\eruditio\prod\work"   'folder containing Excel files in use   
    Const TEXTFILES = "D:\script\dani\eruditio\prod"        'folder containing text summary file    

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        queueLocked = False
        bgRunning = False
    End Sub

    Private Sub watcher_Renamed(sender As System.Object, e As System.IO.RenamedEventArgs) Handles watcher.Renamed

        'This event triggers when an xls file is renamed. Be careful playing with files in the watched
        'folder because you really only want this to trigger when a tmp file is renamed to xls.

        AddLog("File" & e.OldName & " has been renamed to " & e.Name)

        'wait for access to the queue

        Do While queueLocked
            Application.DoEvents()
            System.Threading.Thread.Sleep(50)
        Loop

        'add the renamed file to the queue

        queueLocked = True
        changeQueue.Enqueue(e.Name)
        queueLocked = False

        'start the generator if it isn't already running

        If Not bgRunning Then
            bgRunning = True
            btnStartStop.BackColor = Color.Green
            bgwGenerator.RunWorkerAsync()
        End If

    End Sub

    Private Sub AddLog(text As String)

        ' add the given text to the event log display

        lbxLog.Items.Add(Now() & " " & text)

        'scroll to the bottom to keep the most recent entry in view

        lbxLog.SelectedIndex = lbxLog.Items.Count - 1
        lbxLog.SelectedIndex = -1

    End Sub

    Private Sub btnStartStop_Click(sender As System.Object, e As System.EventArgs) Handles btnStartStop.Click

        Dim btn As Button = sender

        If btn.Text = "Stop Monitoring" Then
            watcher.EnableRaisingEvents = False
            txtWatchedFolder.Enabled = True
            btn.Text = "Start Monitoring"
        Else
            watcher.Path = txtWatchedFolder.Text
            txtWatchedFolder.Enabled = False
            btn.Text = "Stop Monitoring"
        End If

    End Sub

The client app allows the user toselect, edit and update the production files

Client App

'                                                                                                   
'  Name:                                                                                            
'                                                                                                   
'    EruditioClient                                                                                 
'                                                                                                   
'  Description:                                                                                     
'                                                                                                   
'    This app is the user interface to a set of Excel files residing in a shared folder. A user     
'    requests a file for editing through a file dialog. The user is only allowed to edit the file   
'    if it is not in use by another user.                                                           
'                                                                                                   
'  Notes:                                                                                           
'                                                                                                   
'    The user may save the applied changes at any time during the edit process, however, the        
'    changes are saved only to the user's working copy. In order to update the production file,     
'    the user must close the working copy, then click the "Save" button. If the production copy     
'    of the file is currently in use by the server app, the actual save may be delayed by several   
'    seconds, however, patience IS a virtue. The user's working copy is first moved into the        
'    production folder as a tmp file, then renamed to an xls file at the earliest possible time.    
'                                                                                                   
'    This is skeleton code only. It is up the the implementing programmer to provide the code to    
'    actually open and display the Excel file.                                                      
'                                                                                                   
'  Audit:                                                                                           
'                                                                                                   
'    2012-07-10  RevJ  Original code (for which no liability is assumed)                            
'                                                                                                   

Public Class Form1

    Const PRODFILES = "D:\script\dani\eruditio\prod"        'folder containing Excel files          
    Const WORKFILES = "D:\script\dani\eruditio\prod\work"   'folder containing Excel files in use   
    Const TEXTFILES = "D:\script\dani\eruditio\prod"        'folder containing text summary file    

    Private editfile As String
    Private workfile As String
    Private username As String

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        OpenFileDialog1.DefaultExt = ".xls"
        OpenFileDialog1.InitialDirectory = PRODFILES
    End Sub

    Private Sub btnEdit_Click(sender As System.Object, e As System.EventArgs) Handles btnEdit.Click

        'Selecting a file to edit requires ensuring that the file is not currently being edited by  
        'another user. If a file is being edited then a working copy of that file must exist in the 
        'WORKFILES folder and have a name like datafile-###-username.xls. If there is no file of    
        'that form then the file is available for editing.                                          

        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            'user has selected a file for editing
            'editfile is the Excel file name (eg \PROD\datafile-001.xls)
            'workbase is the base working file name (eg \WORK\datafile-001)

            editfile = OpenFileDialog1.FileName
            Dim workbase As String = IO.Path.GetFileNameWithoutExtension(editfile) & "-"

            'check if there is a working file of the form datafile-###-*.xls

            workfile = ""

            For Each file In My.Computer.FileSystem.GetFiles(WORKFILES, FileIO.SearchOption.SearchTopLevelOnly, workbase & "*.xls")
                workfile = file
                Exit For
            Next

            If workfile <> "" Then
                MsgBox("Working file is " & workfile, MsgBoxStyle.OkOnly, "File is in use by another user")
            Else

                'create the working file for this user

                username = My.User.Name.Replace("\", "-")
                workfile = IO.Path.Combine(WORKFILES, workbase & username & ".xls")
                IO.File.Copy(editfile, workfile)

                'Add the code here to edit through Excel. Presumably the user can save the working copy
                'of the Excel file periodically.

            End If

        End If

    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click

        'Saving the file involves not clobbering the oroginal if it is being used by the generator.
        'If datafile-###.xls is currently being used by the generator then there will be a file named
        'datafile-###.gen. If this file exists then wait until it disappears.

        Dim genfile As String = editfile.Replace(IO.Path.GetExtension(editfile), ".gen")
        Dim tmpfile As String = editfile.Replace(IO.Path.GetExtension(editfile), ".tmp")

        IO.File.Move(workfile, tmpfile)

        'you might add some sort of user feedback indicating the length of the wait

        Do While IO.File.Exists(genfile)
            Application.DoEvents()
            System.Threading.Thread.Sleep(500)
        Loop

        'Once we get here we should be safe to rename tmp to xls.

        IO.File.Delete(editfile)
        IO.File.Move(tmpfile, editfile)

    End Sub

End Class




    Private Sub bgwGenerator_RunWorkerCompleted(sender As System.Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgwGenerator.RunWorkerCompleted
        bgRunning = False
        btnStartStop.BackColor = Color.Red
    End Sub

#Region "Generator background thread"

    Private Sub Generator(sender As System.Object, e As System.ComponentModel.DoWorkEventArgs) Handles bgwGenerator.DoWork

        'This sub handles the generation of text files from Excel files. It runs on a separate thread
        'and coordinates with the main thread through the changedFiles queue.

        Do While changeQueue.Count > 0

            'wait for access to the queue

            Do While queueLocked
                System.Threading.Thread.Sleep(50)
            Loop

            queueLocked = True
            Dim filename As String = changeQueue.Dequeue
            queueLocked = False

            Dim flagfile As String = IO.Path.Combine(PRODFILES, IO.Path.GetFileNameWithoutExtension(filename) & ".gen")

            'By creating filename.gen we indicate that the Excel file is currently
            'in use by the generator and cannot be replaced by a user-modified copy.
            'The user app will sit in a wait loop until the gen file is deleted.

            IO.File.Create(flagfile).Close()
            Dim summary As String = CreateSummary(filename)
            IO.File.Delete(flagfile)

            WriteSummary(filename, summary)

        Loop

        'When we leave this sub, the RunWorkerCompleted event for the generator task will be
        'fired letting the main thread know that the background task is done.

    End Sub

    Private Function CreateSummary(filename As String) As String

        'this sub reads an Excel file and creates a one-line string

        Dim summary As String = ""

        'add code here to generate the summary for this file. For now I'm simulating
        'some processing with a thread wait

        Threading.Thread.Sleep(5000)
        summary = Now & " " & filename

        Return summary

    End Function

    Private Sub WriteSummary(filename As String, summary As String)

        'this sub updates the correct line of the text file with the new summary
        Dim fw As New System.IO.StreamWriter(PRODFILES & "\summary.txt", IO.FileMode.Append)
        fw.WriteLine(Now & " " & filename)
        fw.Close()

    End Sub

#End Region

    Private Sub bgwGenerator_ProgressChanged(sender As System.Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles bgwGenerator.ProgressChanged
        'the background proces can indicate its progress via this event.
    End Sub

End Class

[Reverend Jim]

I could only do so much testing here so I hope this helps.

[Eruditio]

This is absolutely brilliant. I never expected such a detailed and dedicated response from someone. I really appreciate it.

On a positive note, the problem now seems to have been solved. While you were throwing together that skeleton code, I was actually editing my own app in a very similar manner. It's nice to see that we were both on the same page and our ideas seemed to have lined up nicely.

There are a few features you've implemented in your code that seem like they would be quite useful, so I may streamline my own software in the future.

Original Problem

We've managed to identify the problem as being multiple FileSystemWatchers watching the same shared folders for Changed events under a LastWrite NotifyFilter. Individual users had their FileSystemWatcher triggered by edits they did not make themselves. This caused a mysterious error message (with no error text) to appear and crash the software. This was due, in part, to multiple FSWs trying to move/rename files that had already been moved/renamed by a different FSW.

Solution

The solution is quite simple despite the rather chunky code snippets present throughout the thread. Don't have multiple FSWs watching the same folder. Individual users now have their own 'working' folder which is watched by their own FSW for LastWrite Changed events. This allows the software to identify Excel documents that are saved then closed. The shared folder containing all of the shared Excel documents is then monitored by a separate app with an FSW watching for FileName Renamed events.

Once more let me express my gratitude. This would have undoubtedly taken me much longer to solve without your help and your constant responses kept me motivated to keep trying!

[Reverend Jim]

I remember how much "fun" I had doing all those multi-user/multi-process apps for the first time. I hope I saved you from some of the grief. Thanks for the interesting problem. It gave me a reason to learn how to code a multi-threaded app. Feel free to come back here often.

[Reverend Jim]

Upon further reading I found that there is a better way to prevent two (or more) threads from modifying the shared data.

Dim filename As String

SyncLock changeQueue
    filename = changeQueue.Dequeue
End SyncLock

You can use the SyncLock to block all other threads from modifying changeQueue (or other structures). It is important to restrict the time that your code spends inside the Sync/End Sync so as to block other threads for as short a time as possible. Note that this only works as long as everyone plays by the rules. In other words, this only blocks access to other threads that also enclose access to the data in Sync/End Sync blocks.