Microsoft Access Add-In using VSTO

Question

I'm trying to extend the functionality of MS Access. I want the user to be able to trigger this functionality from within the Access application, ideally through clicking on a button I have introduced. I would like to be able to implement this functionality using C#.

If I was targeting any of MS Word, Excel, Outlook, PowerPoint, Visio, InfoPath, or Project, then Visual Studio Tools for Office (see here) would be ideal for this. However, Access was either never (officially) part of this initiative or was dropped from being officially supported by it quite some time ago. There is a company called Add-In Express that seems to support VSTO with Access, for example, but the license is quite expensive and I would prefer not to have third-party software involved if it can be done directly. There is an MS blog article from 2008 with a hack that takes advantage of the "largely host-agnostic nature of VSTO add-in projects" and converts a Word add-in into an Access add-in. This comes with a caveat:

Note, however, that I'm not encouraging people to use this approach in production – we have not tested this behavior, and it is expressly not supported in any way. What I've done is to explore how VSTO is designed to be optimally host-agnostic, so that the add-in model is as flexible as possible – without going to the extreme of loose typing offered by the old "shared" add-in model.

This approach was suggested in response to this recent SO question and was apparently used successfully by the question asker. While I am concerned by the lack of official support this may suffice for a prototype and I have tried to use it.

After following the steps 1 through 8 without any issues, I hit Debug and MS Access fires up and then throws the following error dialog:

Microsoft Office Customization Installer
There was an error during installation.

Downloading file:///C:/Temp/MyAddIn/bin/Debug/MyAddIn.vsto did not succeed.

Details:

************** Exception Text **************
System.Deployment.Application.DeploymentDownloadException: Downloading 
file:///C:/Temp/MyAddIn/bin/Debug/MyAddIn.vsto did not succeed. ---> 
System.Net.WebException: Could not find a part of the path 
'C:\Temp\MyAddIn\bin\Debug\MyAddIn.vsto'. ---> System.Net.WebException: 
Could not find a part of the path 'C:\Temp\MyAddIn\bin\Debug\MyAddIn.vsto'. 
---> System.IO.DirectoryNotFoundException: Could not find a part of the path 
'C:\Temp\MyAddIn\bin\Debug\MyAddIn.vsto'.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess 
access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, 
FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean 
bFromProxy, Boolean useLongPath, Boolean checkHost)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess 
access, FileShare share, Int32 bufferSize, FileOptions options, String 
msgPath, Boolean bFromProxy)
   at System.Net.FileWebStream..ctor(FileWebRequest request, String path, 
FileMode mode, FileAccess access, FileShare sharing, Int32 length, Boolean 
async)
   at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, 
FileAccess access, Boolean asyncHint)
   --- End of inner exception stack trace ---
   at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, 
FileAccess access, Boolean asyncHint)
   at System.Net.FileWebRequest.GetResponseCallback(Object state)
   --- End of inner exception stack trace ---
   at System.Net.FileWebRequest.EndGetResponse(IAsyncResult asyncResult)
   at System.Net.FileWebRequest.GetResponse()
   at 
System.Deployment.Application.SystemNetDownloader.DownloadSingleFile
(DownloadQueueItem next)
--- End of inner exception stack trace --- 

Googling on "vsto downloading file did not succeed" brought me here and this seemed relevant:

The problem is that the Office application is looking for the deployment manifest (.vsto) and application manifest (.dll.manifest) and is unable to find them.

I searched for a vsto file extension on my machine and the only one found was in my VS solution folder. So I thought perhaps Step 6 from the blog approach was wrong and the last line should be replaced with the path to this vsto file. However this doesn't seem to be the issue.

Then I found this MSDN article on troubleshooting common VSTO issues and Common Error 4 is a match. The "solution" listed is:

This issue is generally seen when the VSTO solution is trying to get certificate information (publisher name and other data) from Domain Controller and it times out. To resolve the issue, please install this hotfix :- KB 981574.

This links here which is a page titled

The splash screen stays open longer than usual when you try to start Excel on a computer that has the .NET Framework 3.5 SP1 installed

which seems compleletly unrelated. Is this a broken link? I'm not sure how to proceed.

Answer 1

I don’t think the VSTO tools are really worth the trouble, and besides if you create a “add-in”, then such a add=in will be loaded upon Access start-up for ALL Access applications, and not limited to your one application. And having to register the add-in with office OFTEN will cause issues for start up in Access. So your add-in will have to load, and load for ANY and ALL cases in which Access is simply launched for any reason.

All the VSTO tools really do is setup a com interface to office, and it is really much less hassle to just build a “simple” class in .net, and then consume that from your VBA button in access.

So a easy approach is to “just” consume a .net class from VBA/office code. Messing with VSTO really does not help much anyway, and by time you get all that VSTO stuff working, you be long done and finished by just writing a simple class in .net.

A few tips: Ensure you check the box in .net “register” for COM interop. Keep in mind this option ONLY executes a regasm for your convenience during the development process on your dev computer.

The other option (which is set by default anyway!!!) is in the assembly area, and you just have to ensure “Make assembly COM-visible).

For distribution you will have to include a small batch file or use an installer. That simple batch file or installer has to execute a “regasm” on target machines (you non dev computer).

And ensure you force the project to x86 (assuming your using office x32, which is most certainly the case). So do NOT use “any” CPU but force project to x86 CPU.

Other than above, that’s about it.

Here is a simple .net class. Assuming .net 4.5 or later, this lets you create zip files from Access VBA.

(you need both system.IO.Compression, and system.io.compression.Filesystem as refs in your .net project. The result is zipping files from Access without any 3rd party tools.

The class in .net is thus this:

Imports System.Runtime.InteropServices
Imports System.IO.Compression
Imports System.IO

<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class AlbertCom1

Private m_Times2 As Integer

Public Sub MsgHello()

    MsgBox("Hello world", MsgBoxStyle.Information, "VB.net example")

End Sub

Public Sub MyZipper(strFileName As String, strZipFile As String)

    Using archive As ZipArchive = ZipFile.Open(strZipFile, ZipArchiveMode.Update)
        archive.CreateEntryFromFile(strFileName, Path.GetFileName(strFileName), CompressionLevel.Fastest)
    End Using

End Sub

Public Function GetConValue(strSetting As String) As String

    ' read a simple value from config file
    Return My.Settings(strSetting).ToString

End Function

Public Property Times2 As Integer
    Get
        Return m_Times2
    End Get
    Set(value As Integer)
        m_Times2 = value * 2
    End Set
End Property

End Class

So the above is rather simple, and short.

In Access VBA, behind our button, we can thus use these code:

Call/use the Hello message box method from above:

Sub TestCOMHello()

  Dim mycom      As Object
  Set mycom = CreateObject("AlbertCom1.AlbertCom1")

  mycom.MsgHello

End Sub

Note how above is LATE binding – this will work without a reference in VBA to the object. (so createObject() is required).

However, the following examples use early binding, but all would work as per above to create the object.

Zip a file from VBA:

Sub TestCOMZip()

  Dim strFromFile      As String
  Dim strToFile        As String

  Dim mycom      As New AlbertCom1.AlbertCom1

  strFromFile = "c:\test\data.txt"
  strToFile = "c:\test\data.zip"

  mycom.MyZipper strFromFile, strToFile


End Sub

A few more tips:

You don’t really have to build a custom interface in your class, and while many “frown” upon using AutoDual, I think such an approach is just fine. The “examples” on the internet that spend all this time creating the custom interface really are just an excuse for world poverty.

One last big tip: Do NOT expose any non-compatible data types in your class. If you expose (by accident or by intention) any .net object TYPE that is not compatible with standard “com” objects, then your .net class will compile just fine, but Access will not see or consume the object correctly. So keep (declare) those variables and routines as private in the actual above class. (The other code and parts don’t matter – just the one class is what I am talking about).

So don’t for example expose a .net long data type. If you stick to strings, standard collections, arrays etc., then you should be just fine. And you actually “can” expose non compatible object types as “object” from .net. (Intel-sense in Access will not show the objects methods, but you can still use them).

And while public functions and even public vars are most simple for your methods of the object, here is use of a get/set as per “standard” approach as to how class methods are created.

So in .net we have:

Public Property Times2 As Integer
    Get
        Return m_Times2
    End Get
    Set(value As Integer)
        m_Times2 = value * 2
    End Set
End Property

And in your VBA code, then we have:

Sub TestTimes()

   Dim mycom     As New AlbertCom1.AlbertCom1

   mycom.Times2 = 40

   Debug.Print mycom.Times2

End Sub

output: 80

Note for early binding, then the Access VBA editor will “spit out” the properties and methods of the class:

Eg this:

All in all use of VSTO is in general a HUGE overkill. Just build a simple .net class to expose what you need to use/call from VBA and you off to the races. And the other BIG bonues is that this COM object can be used from windows scripting, VB6, FoxPro, Excel, Word, power-point etc. In other words you not limited to any one platform or JUST Access to consume + use your simple class you expose as a COM object. I am hard pressed to make a case for VSTO in most cases. So this object can now be used from ANY office application (VBA code) with great ease.