none
Use FileSystemWatcher in Access RRS feed

  • Question

  • I would like to incorporate the FileSystemWatcher class into an Excel or Access 2010 application.  Ideally, I would like to create a class in .NET and use this in VBA code.  What is the best method to incorporate this functionality?

    My end goal is to watch for CSV file changes in 8 different directories.  When the FileChanged event fires, I want to import the CSV file into an Access database.

    Any help would be greatly appreciated.

     

    Sunday, April 10, 2011 2:20 PM

Answers

  • Hi Quackerbackers,

    After reading your post, I knew your problem. I think we can do the following steps to use the FileSystemWatcher for VBA:

    1. Create the C# Class Library and write the code which uses FileSystemWatcher

    2. Register the Class Library as COM visible (Right Click project->click properties->Build tab->check Register for COM interop) , below is my code snippet of C# Class Library:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Runtime.InteropServices;
    using System.Security.Permissions;
    using System.IO;
    using System.Windows.Forms;
     
    namespace CSFileSystemWatcherForVBA
    {
        [ClassInterface(ClassInterfaceType.AutoDual)]
        [ComVisible(true)]
        public class FileWatcher
        {
            [PermissionSet(SecurityAction.Demand, Name = "FullTrust")]
            public void Run(string path)
            {
                // Create a new FileSystemWatcher and set its properties.
                FileSystemWatcher watcher = new FileSystemWatcher();
                watcher.Path = path;
                /* Watch for changes in LastAccess and LastWrite times, and
                   the renaming of files or directories. */
                watcher.NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite
                   | NotifyFilters.FileName | NotifyFilters.DirectoryName;
                // Only watch text files.
                watcher.Filter = "*.txt";
     
                // Add event handlers.
                watcher.Changed += new FileSystemEventHandler(OnChanged);
                watcher.Created += new FileSystemEventHandler(OnChanged);
                watcher.Deleted += new FileSystemEventHandler(OnChanged);
                watcher.Renamed += new RenamedEventHandler(OnRenamed);
     
                // Begin watching.
                watcher.EnableRaisingEvents = true;
            }
     
            // Define the event handlers.
            private static void OnChanged(object source, FileSystemEventArgs e)
            {
                // Specify what is done when a file is changed, created, or deleted.
               // Console.WriteLine("File: " + e.FullPath + " " + e.ChangeType);
                MessageBox.Show("File: " + e.FullPath + " " + e.ChangeType);
            }
     
            private static void OnRenamed(object source, RenamedEventArgs e)
            {
                // Specify what is done when a file is renamed.
                // Console.WriteLine("File: {0} renamed to {1}", e.OldFullPath, e.FullPath);
                MessageBox.Show("File: " + e.OldFullPath + " renamed to " + e.FullPath);
     
            }
        }
    }
    

    3. Open VBA environment, add the reference CSFileSystemWatcherForVBA, then we can call the method like this:

    Dim o As New CSFileSystemWatcherForVBA.FileWatcher

    Sub a()
    o.Run ("C:\Users\v-bpeng\Desktop\TestFolder")
    End Sub

    When running the VBA macro, if I changed text file content and save it, a message box will pop up. Here you can adapt the program to return the value whether the cvs file is changed, then decide to import the cvs file to the Access database.

    I hope this can help you and just feel free to follow up after you have tried.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Bruce Song Thursday, April 14, 2011 7:50 AM
    • Marked as answer by Bruce Song Wednesday, April 20, 2011 9:39 AM
    Monday, April 11, 2011 2:35 AM

All replies

  • Hi Quackerbackers,

    After reading your post, I knew your problem. I think we can do the following steps to use the FileSystemWatcher for VBA:

    1. Create the C# Class Library and write the code which uses FileSystemWatcher

    2. Register the Class Library as COM visible (Right Click project->click properties->Build tab->check Register for COM interop) , below is my code snippet of C# Class Library:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Runtime.InteropServices;
    using System.Security.Permissions;
    using System.IO;
    using System.Windows.Forms;
     
    namespace CSFileSystemWatcherForVBA
    {
        [ClassInterface(ClassInterfaceType.AutoDual)]
        [ComVisible(true)]
        public class FileWatcher
        {
            [PermissionSet(SecurityAction.Demand, Name = "FullTrust")]
            public void Run(string path)
            {
                // Create a new FileSystemWatcher and set its properties.
                FileSystemWatcher watcher = new FileSystemWatcher();
                watcher.Path = path;
                /* Watch for changes in LastAccess and LastWrite times, and
                   the renaming of files or directories. */
                watcher.NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite
                   | NotifyFilters.FileName | NotifyFilters.DirectoryName;
                // Only watch text files.
                watcher.Filter = "*.txt";
     
                // Add event handlers.
                watcher.Changed += new FileSystemEventHandler(OnChanged);
                watcher.Created += new FileSystemEventHandler(OnChanged);
                watcher.Deleted += new FileSystemEventHandler(OnChanged);
                watcher.Renamed += new RenamedEventHandler(OnRenamed);
     
                // Begin watching.
                watcher.EnableRaisingEvents = true;
            }
     
            // Define the event handlers.
            private static void OnChanged(object source, FileSystemEventArgs e)
            {
                // Specify what is done when a file is changed, created, or deleted.
               // Console.WriteLine("File: " + e.FullPath + " " + e.ChangeType);
                MessageBox.Show("File: " + e.FullPath + " " + e.ChangeType);
            }
     
            private static void OnRenamed(object source, RenamedEventArgs e)
            {
                // Specify what is done when a file is renamed.
                // Console.WriteLine("File: {0} renamed to {1}", e.OldFullPath, e.FullPath);
                MessageBox.Show("File: " + e.OldFullPath + " renamed to " + e.FullPath);
     
            }
        }
    }
    

    3. Open VBA environment, add the reference CSFileSystemWatcherForVBA, then we can call the method like this:

    Dim o As New CSFileSystemWatcherForVBA.FileWatcher

    Sub a()
    o.Run ("C:\Users\v-bpeng\Desktop\TestFolder")
    End Sub

    When running the VBA macro, if I changed text file content and save it, a message box will pop up. Here you can adapt the program to return the value whether the cvs file is changed, then decide to import the cvs file to the Access database.

    I hope this can help you and just feel free to follow up after you have tried.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Bruce Song Thursday, April 14, 2011 7:50 AM
    • Marked as answer by Bruce Song Wednesday, April 20, 2011 9:39 AM
    Monday, April 11, 2011 2:35 AM
  • Thank you for the quick response Bruce.

     

    I converted the above code to VB (see below), set the Register for COM Interop, and built the project successfully.  I opened up Access 2010, created a new module and attempted to add a reference to the generated .dll file.  A message box states "Can't add a reference to the specified file."

     

    Any additional help would be appreciated.

    John

     

    Imports System.Collections.Generic
    Imports System.Linq
    Imports System.Text
    Imports System.Runtime.InteropServices
    Imports System.Security.Permissions
    Imports System.IO
    
    
    <ClassInterface(ClassInterfaceType.AutoDual)> _
    <ComVisible(True)> _
    Public Class FileWatcher
      <PermissionSet(SecurityAction.Demand, Name:="FullTrust")> _
      Public Sub Run(ByVal path As String)
        ' Create a new FileSystemWatcher and set its properties.
        Dim watcher As New FileSystemWatcher()
        'watcher.Path = path
        watcher.Path = My.Computer.FileSystem.SpecialDirectories.Desktop
        ' Watch for changes in LastAccess and LastWrite times, and
        '        the renaming of files or directories. 
    
        watcher.NotifyFilter = NotifyFilters.LastAccess Or NotifyFilters.LastWrite Or NotifyFilters.FileName Or NotifyFilters.DirectoryName
        ' Only watch text files.
        watcher.Filter = "*.txt"
    
        ' Add event handlers.
        AddHandler watcher.Changed, New FileSystemEventHandler(AddressOf OnChanged)
        AddHandler watcher.Created, New FileSystemEventHandler(AddressOf OnChanged)
        AddHandler watcher.Deleted, New FileSystemEventHandler(AddressOf OnChanged)
        AddHandler watcher.Renamed, New RenamedEventHandler(AddressOf OnRenamed)
    
        ' Begin watching.
        watcher.EnableRaisingEvents = True
      End Sub
    
      ' Define the event handlers.
      Private Shared Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
        ' Specify what is done when a file is changed, created, or deleted.
        ' Console.WriteLine("File: " + e.FullPath + " " + e.ChangeType);
        MsgBox("File: " & e.FullPath & " " & Convert.ToString(e.ChangeType))
      End Sub
    
      Private Shared Sub OnRenamed(ByVal source As Object, ByVal e As RenamedEventArgs)
        ' Specify what is done when a file is renamed.
        ' Console.WriteLine("File: {0} renamed to {1}", e.OldFullPath, e.FullPath);
        MsgBox("File: " & e.OldFullPath & " renamed to " & e.FullPath)
    
      End Sub
    End Class
    

    Wednesday, April 13, 2011 1:26 AM
  • Hi Quackerbackers,

    I also tested with the VB.NET code. However, it builds well and the dll can be referenced in Access 2010. It is strange that you encountered the error, how did you reference it? Did you choose it directly from the Reference dialog like this:

    You needn't open the dll file, it will be automaticlly added into the reference dialog.

     Hope this can help you to resolve your problem.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 13, 2011 6:46 AM
  • There's also a lot of suggestions posted in a duplicate of this question in the Access forum (where the question is more on-target, as Access is not part of the VSTO technology):

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/34cb5ff7-3f74-4362-bb30-3324d195a991


    Cindy Meister, VSTO/Word MVP
    Wednesday, April 13, 2011 7:51 AM
    Moderator
  • Bruce,

    I clicked on the Browse button to add the reference rather than looking for this in the existing reference list.

    Thanks!

    Wednesday, April 13, 2011 8:02 PM