Monitoring an Excel File in VB.NET

Recently I needed to monitor an Excel document for a program at work. The program needed to monitor when the spreadsheet was saved, and then read data from the spreadsheet.

The problems faced include:

  • Excel does a fancy way of saving changes, so a FileSystemWatcher won’t simply say, “The file changed.”
  • Excel won’t let other programs access the file. (I haven’t checked if ClosedXML can open a file as read-only.)

The way Excel handles a file is something like as follows. Consider a file named “Report.xlsx”.

  1. User opens Report.xlsx in Excel.
  2. Excel creates ~$Report.xlsx in the same folder as Report.xlsx.
  3. Excel copies Report.xlsx to ~$Report.xlsx
  4. User makes changes to spreadsheet, then saves the document.
  5. Excel creates a temporary file, let’s say ABCDEF.tmp.
  6. Excel copies ~$Report.xlsx to ABCDEF.tmp
  7. Excel renames Report.xlsx to a different temporary file, let’s say GHIJKL.tmp.
  8. Excel renames ABCDEF.tmp to Report.xlsx.
  9. Excel deletes GHIJKL.tmp.
  10. User closes Excel.
  11. Excel deletes ~$Report.xlsx.

For a previous project, I had a FileSystemWatcher look for when a file renames to Report.xlsx, then start a Timer. Every tick of the Timer, a sub would try to open Report.xlsx in ClosedXML. This would fail repeatedly as long as the file is open in Excel. Once the file is closed, the sub will succeed, and the Timer is turned off.

This latest project, I developed a much more elegant solution:

  1. Create a FileSystemWatcher to watch for ~$Report.xlsx.
  2. When ~$Report.xlsx is created, update the program accordingly. In my case, this disables an “Archive” button. The archive function moves various files into other folders, but this cannot be done if Excel has a lock on the report file.
  3. When ~$Report.xlsx is deleted, open the file Report.xlsx in ClosedXML and check to see if all the required fields have been filled out. If so, enable the “Archive” button.

Much, much simpler.