VSTO & .NET & Excel

October 18, 2006

Using Loaders

Filed under: .NET & Excel — Dennis M Wallentin @ 10:01 am

Loaders vs Front Loaders

Both Loaders and Front Loaders are standalone solutions, developed with tools like classic VB and VB.NET, which target to manipulate other files via automation with a predefined number of actions.

The difference here in this context is that while Front Loaders have a visualized user interface Loaders don’t have it and therefore Loaders are always used in silent mode.

Using Loaders and Front Loaders are, at least for me, one of the more powerful solutions when it comes to manipulating Excel workbooks. Up to this date I have delivered more then 80 clients solutions based on different kind of Loaders and Front Loaders where the majority still are in use.

The real power is achieved when using Loaders together with Windows Scheduled Tasks tool on a Windows server.

Creating a Loader by using VB.NET 2005

The case is based on a scenario where we schedule a task to be executed every Sunday night so when the co-workers start their work on Mondays they have the latest update when they open the workbooks in Excel.

The two Excel workbooks in use are located in the same folder and they should be populated with the latest data from a central production’s database. None of the workbooks contain any code.

The Loader should do the following:

  • Start MS Excel
  • Open every workbook in the folder
  • Add data to the workbooks
  • Save and Close the workbooks
  • Close MS Excel
  • Report status after the processing above by writing to a log file.

The case does not require any user interface as all execution will be done via automation, i e the scheduled task starts the Loader which in return manipulate the workbooks as described above.

In order to keep the solution flexible ( i e hard code the generic but avoid to hard code the less general information)  the case require a text file which should contain the following information:

  • The full pathway to the folder
  • The SQL queries

A XML-file may be nice to use here but I still prefer to work with ‘traditional’ text files for two reasons:

  • They usually don’t contain a large amount of information and therefore not require a structure as XML-files offer.
  • I’m both old fashioned and have not yet achieved all the knowledge to fully work with XML-files.

Below is the information that the text file ‘Loader.txt’ contains:

c:\Test\
SELECT * FROM Orders WHERE EmployeeID=‘3’
SELECT * FROM Orders WHERE EmployeeID=‘5’

Using classic ADO or ADO.NET?
The decision in this case is quite easy to do. As long as the Range’s method CopyFromRecordset only support classic ADO (which is also true for Excel 2007!) and we have a larger amount of data classic ADO is the natural choice.

The Code

The code is placed in two modules:

In the Main module:
Module Main
    Sub Main()
        ‘Declare and instantiate a new instance of the class.
        Dim cl As New clLoader
        ‘Call the main function.
        If cl.Update_Workbooks() Then cl.Write_Log (True)
        cl = Nothing
    End Sub
End Module

In a class module:
 ‘Make sure that references are made to the following namespaces:
‘(And also to NET’s ADODB)
Imports System
Imports System.IO
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

Friend Class clLoader
    ‘Implement the interface.
    Implements IDisposable

    Public Sub New()
        ‘Constructors.
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        ‘Important: The Dispose method should never raise any errors.
        ‘In order to avoid that Dispose is called multiply times.
        Static Disposed As Boolean
        If Not Disposed Then
            Disposed = True
            GC.SuppressFinalize (Me)
        End If
    End Sub

    Protected Overrides Sub Finalize()
        ‘Destructors()
        Dispose()
    End Sub

    Friend Function Update_Workbooks() As Boolean
        ‘Make sure that the connection string works properly and
        ‘that You got a running instance of the SQL Server.
        Const Con As String = “Provider=SQLNCLI.1;” & _
                  “Integrated Security=SSPI;” &
                  “Persist Security Info=False;” & _
                  “Initial Catalog=NorthWind;” & _
                  “Data Source=IBM\SQLExpress”

        Dim cnt As New ADODB.Connection
        ‘By setting the following variable to Nothing we eliminate
        ‘the case where it may occur a null reference excepetion for it.
        Dim rst As ADODB.Recordset = Nothing
        ‘Instantiate the Excel variables.
        Dim xlApp As New Excel.Application
        ‘By setting the following variable to Nothing we eliminate
        ‘the case where it may occur a null reference excepetion for it.
        Dim xlWbook As Excel.Workbook = Nothing
        Dim xlWsheet As Excel.Worksheet
        Dim xlData As Excel.Range
        Dim xlDate As Excel.Range
        Dim xlCounter As Short

        Try
            ‘Grab the path to the control file which is the same as for
            ‘the application.
            Dim LoaderFile As String = Environment.CurrentDirectory _
                                                             & “\Loader.txt”
            ‘Check if the control file exist.
            If File.Exists(LoaderFile) Then
                ‘Instantiate a new reader and read the content of the control file.
                Dim Reader As New IO.StreamReader(LoaderFile)
                ‘Split the text per line and populate the array.
                Dim GetValues() As String =  _
                       Split(Reader.ReadToEnd, vbNewLine)
                ‘Close the reader.
                Reader.Close()
                ‘Retrieve the directory where the target workbooks are stored in.
                Dim Dir As DirectoryInfo =  _
                        New DirectoryInfo(GetValues(0))
                ‘We only interested in the Excel files so we create an array which
                ‘will only contain the filtered files.
                Dim xlFiles() As FileInfo = Dir.GetFiles(“*.xls”)
                ‘Control to make sure that it exist
                ‘at least one Excel file in the directory.
                If UBound(xlFiles) <> -1 Then
                    ‘In order to iterate the collection of Excel files.
                    Dim xlFile As FileInfo
                    ‘Iterate through the collection of Excel files.
                    With cnt
                        .CursorLocation = ADODB.CursorLocationEnum.adUseClient
                        .Open (Con)
                    End With
                    For Each xlFile In xlFiles
                        ‘This counter refer to both the collection of Excel files and
                        ‘to each line in the control file that contain the SQL query.
                        xlCounter = CShort(xlCounter + 1)
                        xlWbook = xlApp.Workbooks.Open(xlFile.FullName)
                        xlWsheet =  _
                        CType(xlWbook.Worksheets(2), Excel.Worksheet)
                        With xlWsheet
                            xlData = .Range(“D3”)
                            xlDate = .Range(“B2”)
                        End With
                        ‘Date stamp by using the predefined short date expression.
                        xlDate.Value = Format(Now, “d”)
                        ‘Get the recordset and ‘dump’ it into the open workbook.
                        rst = cnt.Execute(GetValues(xlCounter))
                        rst.MoveFirst()
                        If rst.RecordCount <> -1 Then
                            With xlData
                                ‘Clear the range from previously data.
                                .CurrentRegion.Delete()
                                ”Dump’ the Recordset.
                                .CopyFromRecordset (rst)
                            End With
                        End If
                        ‘Clean up.
                        rst.Close()
                        rst = Nothing
                        ‘Save and close the active workbook.
                        xlWbook.Close(SaveChanges:=True)
                    Next
                Else
                    Write_Log(False, “No Excel files are located in ” _
                                         & Dir.FullName)
                    Return False
                End If
            End If
            xlApp.Quit()
            Return True

        Catch ex As Exception
            ‘Here is just some examples on error handling.
            If ex.Message Like “*BADINDEX” Then
                Write_Log(False, xlWbook.FullName & _
                      ” The required worksheet does not exist.”)
            Else
                Write_Log(False, ex.Message)
            End If
            GC.Collect()
            GC.WaitForPendingFinalizers()
            xlWbook.Close(SaveChanges:=False)
            Marshal.FinalReleaseComObject (xlWbook)
            xlApp.Quit()
            Marshal.FinalReleaseComObject (xlApp)
            Return False
        Finally
            xlData = Nothing
            xlDate = Nothing
            xlWsheet = Nothing
            xlWbook = Nothing
            xlApp = Nothing
            If CBool (rst.State And _
                           
 ADODB.ObjectStateEnum.adStateOpen) =  _
                          True Then rst.Close()
            rst = Nothing
            If CBool (cnt.State And _
                          
ADODB.ObjectStateEnum.adStateOpen) = _
                           True Then cnt.Close()
            cnt = Nothing
        End Try
    End Function

    Friend Sub Write_Log(ByVal Status As Boolean, _
                         Optional ByVal Source As String = “”)
        ‘Instantiate the writer and create the logfile
        ‘(if it doesn’t already exist) and open it to write to it.
        Dim Writer As StreamWriter = File.AppendText(“Log.txt”)
        With Writer
            If Status Then
                .WriteLine (Now & ” – OK “)
            Else
                .WriteLine (Now & ”   **********Error**********   ” & Source)
            End If
            ‘Update the file.
            .Flush()
            .Close()
        End With
        Writer = Nothing
    End Sub
End Class

In general we should split up the code in smaller procedures but I made one procedure. Constructors, destructors and IDispose are general requirements when it comes to classes in VB.NET. In this case they are included but not fully used. What is important is to be aware of that IDispose and the Finalize methods can only handle so called unmanaged resources.

In addition to the main task, i e update the workbooks, the case also shows how we can handle files and directories.

Using .NET Framework in this scenario should not be a problem as it only requires to be installed on the Windows Server. 

For all code generating I use a nice utility by the name of VS.NETcodePrint 2005

Please feel free to comment it and also suggest improvements for the solution. 

Kind regards,
Dennis

Advertisements

46 Comments »

  1. Interesting Dennis,

    I have a front loader in .Net, but one big problem with it is the time it takes to load. Same with the managed com addin I’m working on. I think in your example the load time is not so much of a problem, which is nice.

    With the front loader of mine it’s a real issue becaue I use it to launch a dictor app, by file assiocation. I.e, doubble click a “file.myapp”, and this invokes the front loader which fires up the Excel app, whichs loads the clicked file into the app. VB6 does a much better (faster) job.

    Testing the com addin is the same. Very slow to load up Excel when my addin is loaded, however it works quite fast from there on in, possibly becase of the nature of the code.

    Does anyone know if C++ can be compiled to run in such a way that it does not use a local based .Net framework, and runs more like a com type dll? – either it complies the part of the framework you use, or you just don’t get to use them? I seem to rember having seen this somewhere,. then again I could be dreaming.

    Liked the code to work with the txt file, I need to learn about the settings files in VS though, I guess there XML.

    Comment by Ross — October 18, 2006 @ 11:15 am

  2. Ross,

    The penalty for using .NET Framework is as slower performance (speed execution time) compared with classic VB. A classic VB solution is slower then an old DOS solution 😉

    In Your case it sounds that performance is an important aspect as it involves end users. So if a classic COM add-in is an option then go with it.

    The key point with .NET Framework is security, both in general but perhaps most important aspect is that every solutions are runned in their own Application Domain. This will be even more obvious when Windows Vista is shipped.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 18, 2006 @ 11:41 am

  3. “A classic VB solution is slower then an old DOS solution”

    What’s this DOS thing you talk about, lol!

    I’m gonna look in to this C++ thing, I’m sure i read that somewhere…

    Comment by Ross — October 18, 2006 @ 3:56 pm

  4. Good stuff Dennis…

    The only front-loading I do is to use XLA files as my “front loader”. They can load other XLA’s or even DLLs dynamically, so the first XLA to open can check the Excel.Application.Version and then open the correct XLA. This 2nd XLA would have a reference to a COM DLL that controls Excel. That is, my COM Add-in does *NOT* implement the IDTExtensibility2 interface (unless I need to expose UDFs).

    I think this approach could possibly be even more valuable in .NET because of the incompatibility issues depending on the verison of Excel involved. That said, there are two other easy ways I can think of that are more the “.NET way”:

    (1) The “front loader” could do the Excel.Application.Version checking and then do Assembly.Load() on the appropriate file. I don’t know how this all works security-wise, but I would guess that if the assembly being loaded has the correct permissioning this would work fine.

    (2) Using TlbImp.exe we can create a local IA for whatever version of Excel we want and then bind to that and we should be able to run on that version and higher.

    Anyway, using an XLA “front loader” in a .NET world might not be the best way to go… But I’m pretty sure it could work fine…

    Comment by Mike Rosenblum — October 18, 2006 @ 7:06 pm

  5. Mike,

    Thanks for Your input and especially how You view the use of XLAs.

    Personally I always think in terms of outside (.NET) / inside (Excel) while You put it from inside / out.

    >>I think this approach could possibly be even more valuable in .NET because of the incompatibility issues depending on the version of Excel involved.

    Agreed and to some extend it looks like it’s already an issue among developers.

    Security will be an upcoming question to both discuss and find reasonable solutions for. Using manifests and strong Names will ‘secure’ that assemblies can be ‘accepted’. My concern is how Excel handle security aspects in the long run.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 18, 2006 @ 10:48 pm

  6. Hi Mike,

    On point 2, do you create the IA dynamically after the FL has been launched? I think this might be very slow.
    Also, some might question working with a non PIA for xp and 03? Well I’ve read it best not to work with non PIA if you can, but has anyone had problems with VS generated AIs?, I guess if your targetting 2000, 97 your stuffed!

    Comment by ross — October 18, 2006 @ 10:57 pm

  7. Hey Ross,

    The VS-generated IA’s are not “recommended”, but I think are actually just fine, to be honest. So we should be able to bind to 2000 or ’97 and let VS generiate the IA for us. I think this would occur automatically, I think for 2000 or ’97 — I’ve not tried! For certain we can use TlbImp.exe to do this explicitly, for example, as explained here:

    http://www.devcity.NET/Articles/163/1/Articles.aspx?a=ViewArticle&ArticleID=163&Page=1

    So, no, the idea is not to generate the IA dynamically. (Although, you-know, even that would not be impossible. What you would do is use the Process class to run TlbImp.exe, hmm…) Anyway, I don’t think it’s a good idea to create the IA dynamically. The idea is to make the lowest-bound version that you’d need statically and include it with your project.

    But I’m not sure what happens with security in this case. Dennis, could we strong-name an IA after creating it via TlbImp.exe so that it can go in the GAC? I’m guessing we can’t, but going forward with Vista, etc. this would be critical, right?

    Comment by Mike Rosenblum — October 19, 2006 @ 1:44 am

  8. From Dennis:

    > “Personally I always think in terms of outside (.NET) / inside (Excel) while You put it from inside / out.”

    Heh, yeah, I guess I did turn it inside-out! But I still think of it as a “Front Loader”, but maybe this isn’t the right term…

    Oh, and Ross:

    > “Does anyone know if C++ can be compiled to run in such a way that it does not use a local based .Net framework, and runs more like a com type dll? – either it complies the part of the framework you use, or you just don’t get to use them? I seem to rember having seen this somewhere,. then again I could be dreaming.”

    I think some of the Native-compilers such as RemoteSoft’s Salamander do this. But they are very very expensive. See http://www.remotesoft.com/ for more info on this.

    Mike

    Comment by Mike Rosenblum — October 19, 2006 @ 2:03 am

  9. Thanks for you coments Mike,
    At the risk of going of topic:
    I found this intresting
    >>Hence, Visual Studio will always only find one version to which it can link. In the case of PIAs, VS.NET will find a copy with a strong name, in the GAC, and hence will not even make an Interop assembly. Surprisingly, even if you explicitly link to a particular Type Library, VS.NET will override your reference and use the registered version.

    so to implerment to 4 method, you could unintall any PIA on your PC and develop agaist 97, probelm “solved”… anyway I like idea 3 more, but I’m not sure how this would be implermented… I mark this all on the for later list!

    I’ll look at that C++ thing too, thanks

    thanks agian Mike.

    Regards
    Ross

    Comment by Ross — October 19, 2006 @ 10:40 am

  10. Mike,

    “But I’m not sure what happens with security in this case. Dennis, could we strong-name an IA after creating it via TlbImp.exe so that it can go in the GAC?”

    Actually, strong name signing can be done upon creating the IA with the TlbImp.exe. It has some options that explicit target signing:

    Type Library Importer (Tlbimp.exe)
    http://msdn2.microsoft.com/en-us/library/tt0cf3sx.aspx

    Delay Signing An assembly
    http://msdn2.microsoft.com/en-us/library/t07a3dye.aspx

    We can also do it after creating the IA with the command based tool Sn.exe:

    Strong Name Tool (Sn.exe)
    http://msdn2.microsoft.com/en-us/library/k5b5tt23.aspx

    In order to really understand what’s going on ‘under the hood’ we should use the tool ILDASM.exe to view the created IA.

    If we want to place the created IA in GAC we need to use the Gacutil.exe

    I’m not sure if strong naming is always a must (we can check it when using the regasm.exe) but in view of the general security aspect I believe it’s the best way to go and therefore highly recommended.

    What annoys me is that we can’t expect that creative workaround may work with future releases of .NET Framework and Windows. All solutions should be labeled with info about versions in use.

    I’ve been exploring the subject with Excel XP (2002) and except for the annoying message in the IDE that ‘There are updated custom wrappers available for the following referenced components: Excel, Microsoft.Office.Core.’ it works OK.

    Anyway, I hope the above make any sense 😉

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 19, 2006 @ 11:21 am

  11. Thanks Dennis, that was exceptionally clear. And I will give those links a read…

    There was one thing that I was not quite so clear about. Are you saying that if we provide our own IA, when it is loaded on a newer version it will complain ‘There are updated custom wrappers available for the following referenced components: Excel, Microsoft.Office.Core.’ ?

    I suppose, then, that the only solution to this is some sort of “Front Loader” approach, where the inially-loaded assembly checks the Excel version number and then loads the correct assembly for that version.

    This sounds like a real *pain* but I think it’s no that bad. The code that we’d have to write would be the same for all verisions, but we’d have to build to, say, a ’97 version, 2000 version, 2002, 2003, and now 2007. Ok, that’s a lot, but all you have to do is change the Excel reference and then build to a new assembly name like ‘MyAddin97.dll’ or ‘MyAddin2000.dll’, etc. Once we have and deploy these 5 builds, each with their own IA or PIA reference, then the Front Loader would load when Excel starts up, check the version number, and then load the correct assembly for the particular version of Excel that is running.

    This “Front Loader” could either be an XLA file — because it would load smoothly no matter which version of Excel is running — or possibly be a .NET Assembly. If using a .NET Asembly, it should implement IDTExtensibility2, but NOT reference any Excel Object Model at all. Instead, it would work under Late Binding, and basically only have to make one Late-Bound call to the Excel Object Model: checking the .Version of the ‘Application As Object’ passed in. Based on that, a Select Case statement would check that value and then load the correct Early-Bound add-in assembly. (Using an XLA for this role, however, is even easier.)

    Just some thoughts!
    Mike

    Comment by Mike Rosenblum — October 19, 2006 @ 4:45 pm

  12. Mike,

    My apologize for being unclear on the XP version. Since it exist officially supported PIA for XP this message is being viewed in the IDE but only for the XP version and it runs smooth when executing it within Excel.

    The more I think of Your suggested approach to use XLAs as ‘Front Loaders’ the more I like it. I’ve added it to my list of ‘stuff I will make a future writes up about 🙂

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 19, 2006 @ 5:09 pm

  13. > “It runs smooth when executing it within Excel.”

    Ah, ok! Very good to know. 🙂

    > “The more I think of Your suggested approach to use XLAs as ‘Front Loaders’ the more I like it…”

    What’s great about using an XLA as the loader, is that it can load the correct COM DLL and the close itself out. So the XLA is not even present within the VBA IDE. It’s strange, but the COM Add-in “sticks” and continues running, even though the XLA that held the library reference is now gone.

    I have not read about this approach anywhere, it’s just been in my own personal “bag of tricks” that I use. As a bonus, to temporarily dis-able the add-in, you do not need to unregister the add-in — you just need to remove the XLA (or the shortcut to the XLA) from the XLStart folder. To re-enable it, you just put it back. This is great for debugging purposes if you need to turn it off temporarily (or have a client turn it off temporarily).

    Comment by Mike Rosenblum — October 19, 2006 @ 5:45 pm

  14. Mike,

    That’s indeed very interesting!

    “It’s strange, but the COM Add-in “sticks” and continues running, even though the XLA that held the library reference is now gone.”

    Have You discovered if there exist any entries in the registry that make it possible?

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 19, 2006 @ 8:53 pm

  15. Doesn’t need any special Registrations, this is just “normal behaviour”.

    You create a COM DLL that does *NOT* implement IDTExtensibility2. This way it does NOT automatically load when Excel does. However, this COM DLL needs to have a public method that can accept an Excel.Application instance. I usually create a public class called ‘Project’ and then within that have a public method called ‘Initialize’. Then in a GlobalMultiUse class I have a variable named ‘Project’ that holds a reference to an instance of the ‘Project class’.

    Then you have an XLA that has a reference to your COM DLL, so your COM DLL will automatically load if the XLA is opened within Excel. Within the ThisWorkbook.Workbook_Open() event you call:

    MyComAddin.Project.Intialize(ThisWorkbook.Application)
    ThisWorkbook.Close()

    That’s it!

    If you put the XLA Add-in (or a shortcut to it) within the XLStart directory, then the XLA will load at startup. When it loads, it automatically loads all of its DLLs that it references as well. You would not even need to explicitly call your DLL, except that your DLL needs a reference to the Excel.Application so that it can do what it needs to. Lastly, when the XLA then closes, the COM Add-in will keep running — it has been loaded into Excel’s memory space and will stay loaded until Excel closes.

    Pretty cool. 🙂

    Comment by Mike Rosenblum — October 19, 2006 @ 9:17 pm

  16. I would say it’s an excellent approach and indeed is cool 😉

    Now You have convinced me that I should test it I will spend the weekend with it.

    Thanks for sharing it – highly appreciated!

    Dennis

    Comment by Dennis Wallentin — October 19, 2006 @ 9:34 pm

  17. Interesting!
    I think I followed all of that.
    My gut feeling would have been that when the XLA closes you lose the reference to the com addin, it must be something like the scope of the com reference is to the appliction _
    (MyComAddin.Project.Intialize(ThisWorkbook.Application) ?

    What happens if you open a new instance of XL – (guess it has the referance), or if you have 2 instances open and open it in only one, (guess only one gets it).

    Interesting though, I’ll look back after the weekend, I got to sort out my com addin as it’s stopped loading!!!!!

    very good stuff.

    Comment by Ross — October 20, 2006 @ 6:06 pm

  18. > “Interesting!
    I think I followed all of that.”

    🙂

    > “My gut feeling would have been that when the XLA closes you lose the reference to the com addin”

    Yeah, it “kind of” feels like it should be like that, right? But we should not think of a library (a DLL) as we think of an object. An object is an instance of a class. When the reference counter for that object = 0 then it is out of scope and released. A DLL, however, is loaded code, with Classes (aka “Types”), Methods, etc. placed in memory locations. To unload the DLL, the container must close (the App must exit) or there must be an explicit call to Unload the DLL.

    My guess is that the LoadLibrary API could be used to do the same thing, but I’m not familiar with that API at all. So I do it via Excel constructs only, where I am more comfortable: the XLA runs because it (or a shortcut to it) is in the XLStart directory. The XLA has a library reference to ‘MyAddin2003.dll’ (or whatever) and so automatically loads this DLL. Now the DLL *could* use GetObject() to try to get the Excel.Application instance, but this is error prone if there is more than one Excel.Application running… So it is best if the XLA istelf passes in the relevant reference. From that point onward, the COM DLL has what it needs and the XLA is no longer needed. The XLA can now be closed and the COM DLL will still hold a reference to the Excel.Application instance and run 100% fine.

    > “What happens if you open a new instance of XL – (guess it has the referance), or if you have 2 instances open and open it in only one, (guess only one gets it).”

    No! All running applications that refernce the DLL get their own copy. 🙂 This is the whole idea behind shared code. Don’t think in terms of automation, or objects here. (This is a DLL, a code library, not a ActiveX Server). A COM Add-in it will load into multiple Excel instances without any trouble. In this case we are merely removing IDTExtensiblitiy2 and instead using an XLA to be our loading mechanism.

    > “Interesting though, I’ll look back after the weekend, I got to sort out my com addin as it’s stopped loading!!!!!”

    😦

    I’ve used this XLA-loader approach on and off for a few years now (although I’ve never told anyone about it until now!), but I switched to using it full-time when one of my COM Add-ins got some sort of registry corruption that I could not fix. Really drove me crazy — and scared me! Switching to the XLA-loader approach fixed it and I’ve never looked back…

    Comment by Mike Rosenblum — October 20, 2006 @ 8:29 pm

  19. Hmmm… After writing the above post and then driving in the car right now I thought of a much better way. I can’t believe I didn’t think of this before now…

    Ok, the approach we’ve been discussing so far is to use an XLA as the loading mechanism for a DLL which is included as a Library Reference within the XLA. This locks the XLA-to-DLL in a one-to-one relationship. Using this approach, if we wished to dynamically load a given DLL according to the Excel.Application.Version (say to load a Managed COM DLL that binds to a particular IA or PIA) then we would need:

    (a) An XLA add-in for *EACH* of our COM or Managed COM DLLs, and

    (b) An extra XLA Add-in, called, say, ‘AddinLoader.xla’ which checks the Excel.Application.Version and then opens up the correct XLA depending on the version.

    Ok, the above works, but this is a lot of XLAs. We can do better. We can do the same with just *one* XLA and it’s not hard:

    (1) Create a COM or Managed COM DLL that has a class that *DOES* implement IDTExtensibility2, but does *NOT* use the standard Add-in ‘Connect’ class, or whatever they call it. In short, we do not want this addin to load automatically when Excel loads. But we do want to have one class, we’ll call it the ‘Project’ class, which implements IDTExtensibility2. Normally we would do this to expose UDFs, and we could actually put our UDFs in here if we want (if the Add-in has any).

    (2) Recompile/rebuild this COM Add-in multiple times, but each time with a different library reference to a different IA or PIA as required. Call each version, say, ‘MyAddin97.dll’, ‘MyAddin2000.dll’, etc. The truth is that we might only need one “lowest” version, but hard-binding to the correct version (particularly when dealing with Excel ’97 or 2000 on the low end and 2007 on the high end) could be a good idea. This could also allow us to conditionally-compile certain areas of code. Ok, so now we have up to five different DLLs that are ready to load depending on which version of Excel is run.

    (3) Create a new XLA that has no library references to any of these DLLs. We will load them using Late Binding only. So within the ThisWorkbook.Workbook_Open() event, we put:

    Private Sub Workbook_Open()
    Select Case Val(Application.Version)
    Case 8: CreateObject (“MyAddin97.Project”)
    Case 9: CreateObject (“MyAddin2000.Project”)
    Case 10: CreateObject (“MyAddin2002.Project”)
    Case 11: CreateObject (“MyAddin2003.Project”)
    Case 12: CreateObject (“MyAddin2007.Project”)
    Case Else

    End Select

    ThisWorkbook.Close ‘

    Comment by Mike Rosenblum — October 21, 2006 @ 1:36 am

  20. [Strange, the end of the post got cut off. It continues…]

    ThisWorkbook.Close ‘ The XLA closes when done!
    End Sub

    Now, it looks strange to not actually store the reference to the ‘Projdect’ class anywhere, but I don’t think that we need to. Since the Project class implements IDTExtensibility2, it will have it’s IDTExtensibility2.OnConnection() method called, and a reference to the running Excel.Application instance passed into it. The code to the IDTExtensibility2_OnConnection() event must store this reference in a global variable, of course, but the XLA should be able to exit after creating the project class, without having to store the reference to the ‘project’ class instance. That is, I’m pretty sure that Excel will be holding a reference to the ‘Project’ class instance via the IDTExtensibility2 interface. How do we know? Because Excel will later call IDTExtensibility2.OnBeginShutdown() and IDTExtensibility2.OnDisconnection() when Excel is closing down. So it must be holding a reference to this ‘Project’ class in order to be able to call it later.

    Therefore, since Excel is holding a reference to the ‘Project’ class instance for us, we do not have to and the XLA can shut down after doing its initialization.

    There’s just one last issue, to which I don’t know the answer. But I suspect that Dennis does. Unfortunately, making a Managed COM Add-in for MS Office requires a bit more than merely exposing the class to COM via Attributes and implementing IDTExtensibility2. The problem is that if there is more than one Managed COM Add-in running (which is not *that* likely yet, but the odds are increasing) then these add-ins can step on each other’s toes, notably by calls to Marshal.FinalReleaseComObject(). Actually, my guess is that a COM Add-in should NEVER call this (unlike via Automation where you pretty much always should if using CreateObject(), but not if using GetObject() — ugh, why-oh-why is this so complicated? LOL.)

    Anyway, the point is that in order to protect yourself from *other* Managed COM Add-ins potentially sloppy code and can causes *your* Add-in to crash (yep, not your fault!), the solution is to use… Drum roll please…

    A ‘Shim’.

    Ugh.

    Now Dennis has written 10 articles on Shims, I have read them all, and I *still* don’t feel comfortable with them yet. So here’s the question for Dennis:

    Dennis, can this approach work if using a Shim? Does a Shim load via IDTExtensibility2, or is there a different, special mechanism that would require us to adjust this approach — or possibly make this approach impossible?

    What do you think?
    Mike

    Comment by Mike Rosenblum — October 21, 2006 @ 1:39 am

  21. Ok, sorry for so much text, but I think I was wrong about CreateObject(), that wouldn’t tell Excel to call IDTExtensibility.OnConnect(), etc. Instead, we’d have to use Application.Addins.Add(). Something like this:

    Private Sub Workbook_Open()
    Select Case Val(Application.Version)
    Case 8: Application.Addins.Add(“MyAddin97.Project”)
    Case 9: Application.Addins.Add(“MyAddin2000.Project”)
    Case 10: Application.Addins.Add(“MyAddin2002.Project”)
    Case 11: Application.Addins.Add(“MyAddin2003.Project”)
    Case 12: Application.Addins.Add(“MyAddin2007.Project”)
    Case Else
    ‘ God help us!
    End Select

    ThisWorkbook.Close ‘ The XLA closes when done!
    End Sub

    Ok, that should do it! Sorry about being so verbose. 😦

    Mike

    Comment by Mike Rosenblum — October 21, 2006 @ 1:45 am

  22. Hmmm…

    Since Excel ’97 and Excel 2000 do not support Automation Addins, I’m not sure if this part works:

    Case 8: Application.Addins.Add(”MyAddin97.Project”)
    Case 9: Application.Addins.Add(”MyAddin2000.Project”)

    We might need all those XLA’s afterall. Not sure…

    (Ok, I’ll shut up now. :))

    Mike

    Comment by Mike Rosenblum — October 21, 2006 @ 2:53 pm

  23. Mike,

    No reasons to apologize for being devoted and I’m very greatful that You take Your time and explain it in detail.

    I’ve printed the whole thread. I will carefully read it enabling me to get back with an answer about shimming and the consequences of it in view of what You point out.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 21, 2006 @ 4:43 pm

  24. Shimming is about isolation, i e the output of the shim process is another standard COM DLL that make sure that the associated managed COM add-in is loaded in its own AppDomain.

    Here we have a scenario where a Front Loader will decide which managed COM add-in to be loaded depending on the available Excel version. So the Front Loader, i e the XLA, should ‘load’ the related ‘shim’ which in return load the version specific managed COM add-in.

    In the scenario with 4 available Excel versions (Edit:COM is not supported in Excel 97 and therefore I changed the number of versions from 5 to 4) it would then require 4 ‘shim’s. *But* the Front Loader should only load one version specific at the time and therefore the questions would be:

    Is it possible to ‘shim’ 4 managed COM add-ins with only one shim?

    Per se it would defeat the basic idea with ‘shimming’ but here it’s a relevant question.

    If yes, would it be possible to control which version specific managed COM add-in to be loaded?

    An important aspect here is that the generated COM DLL implement the target interface of IDTExtensibility2.

    I’m not sure if it possible or not because I simple lack the knowledge how to manipulate the generated C++ Active Template Library (ATL) COM DLL.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 21, 2006 @ 7:37 pm

  25. Oh, it’s ATL, huh. Bummer.

    We’d need to be able to turn off IDTExtensibility, so that it did not load automatically. (Now this might even be dangerous here, but this is essentially what we’d need to be able to do.)

    Is IDTExtensibility built into the ATL DLL? Or is it implemented by the Managed COM DLL? It’s possible that it’s both, btw. Excel might call the ATL DLL which then calls the Managed COM addin. I have no clue about this.

    Is there any IA or PIA limitation on a shimmed solution? Could we shim a solution that binds to the Excel ‘2000 IA, for example? (I’m guessing yes, right?)

    This all would take a little looking into… although right now I’m just slammed with work so I don’t think I’ll be ablet to look at this for a while. But I think that an frontloader that can load the correct shim could be very interesting.

    On the other hand, simply binding to the lowest numbered IA or PIA is more-or-less fine too. However, it would be nice to have different versions so that we could conditionally compile to take advantage of later versions of Excel, esp. for 2007 Ribbon vs. 2003 CommandBars. So the idea of having the front-loader load the correct shim really is an interesting thought…

    … Although, even here, one assembly could have a reference to *ALL FOUR* IA’s or PIA’s and simply cast to the correct type as needed. If Excel.Application.Version = 12.0 then cast to Excel12 and then call Ribbon code. If Version = 11.0, then cast to Excel11 and then call CommandBar code.

    Still, I think I like this bind-to-the-correct shim at load-time approach. It has a nice appeal to it…

    Comment by Mike Rosenblum — October 21, 2006 @ 8:08 pm

  26. Yeah the more I think about it, the best way is to have only one shim. Anything else is too complicated. Recompiling 5 times really is not that much fun.

    I think we could get away with only 2 different IA’s installed. Let’s say we have a solution that we wish to operate on Excel 2000 and higher. Then I think we would want to create reference to an IA for Excel 9.0 and I think we’d also want a reference to the PIA for Excel 12.0 so that we can call RibbonX code.

    That should cover it. Of course we could also have references to Excel 10.0, 11.0 and/or 8.0 as well but other than Worksheet.Protection there is not a lot of differences between these versions. (But we could.)

    Oh well, so much for the front-loader idea here. I still use the XLA front-loader approach for my COM DLL’s because I find it easier to move a shortcut into or out of the XLStart directory than Registering or Unregistering a DLL.

    — Mike

    Comment by Mike Rosenblum — October 21, 2006 @ 8:24 pm

  27. Two important aspect of standard shim COM DLLs is that they implement IDTExtensibility and control to 100 % the underlying managed COM Add-ins (the GUIDs of the managed COM Add-ins are part of the ‘instructions’ in the shims).

    It’s correct that Excel call the shim COM DLLs and the shim COM DLLs then call the managed COM Add-ins.

    “Is there any IA or PIA limitation on a shimmed solution? Could we shim a solution that binds to the Excel ‘2000 IA, for example? (I’m guessing yes, right?)”

    If I understand it correctly it works only with 2003 and later (i e 2007) and require early binding however I have not test it with 2000 (yet!).

    From my point of view I can’t see any advantage when it comes to using managed COM add-ins in this context.

    I’ve been moving along with the XLA based front loader approach and why not do the following:

    – Create unmanaged (i e classic) COM Add-in(s) without references to IDTExtensibility or the IRibbonExtensibility
    – Create our own type libraries for IDTExtensibility and IRibbonExtensibility
    – Create the XLA Front Loader

    In that way we can cover 2000 to 2007 and use the suggested approach as Mike points out.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 22, 2006 @ 11:32 am

  28. Yes, indeed, classic COM still seems to be the easier/better way to be sure. I have yet to do a *real* .NET Excel project… and we cannot before doing extensive testing across multiple versions of Excel and potentially multiple Windows OS versions.

    Another thought might be to “manually shim”. For instance, just create a Managed COM DLL, but without implementing IDTExtensibility. That is, simply make a .NET dll assembly and exose the classes to COM via attributes. Then use a standard COM DLL made in VB6 that does inmplement IDTExtensibility2 to be the “front loader”. Or use an XLA as the “front loader”.

    Now, so far this is *NOT* a shimmed solution. So the question is: what is shimming doing, exactly? Is it merely creating another AppDomain? I know so little about this, it’s ridiculous, but my guess is that if we knew more about this, we could probably replicate the Shim mechanism and therefore have more flexibility in how we implement it.

    Not an easy project to be sure!!

    But something to think about…

    Thanks for some great thoughts guys, and especially Dennis. This has been a really interesting discussion.

    — Mike

    Comment by Mike Rosenblum — October 22, 2006 @ 4:05 pm

  29. The shim COM DLLs are wrappers and proxy ‘servers’ for the managed COM Add-ins.

    This solution is more of a shortcoming with the present Excel’s architecture then with .NET so all in all it’s a ‘workaround’ and we all know what that means.

    Many thanks Mike for an indeed interesting discussion 🙂

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 22, 2006 @ 4:40 pm

  30. You guys! 😦

    I read that exchange 5 times and still I am lost! I had a Dentist called Pete Shimmin once but that’s all I know on the topic. I’m gonna print this out though I see if I can understand it in a quiet room somewhere 🙂

    Here’s hoping,

    Will

    Comment by Will Riley — October 23, 2006 @ 1:34 pm

  31. Will,

    Sorry and I believe that we got carried away.

    Take Your time and let us know what parts we can explain better. A good start is to avoid to read the stuff about managed COM Add-ins (that is COM Add-ins created with VB 2005 or C#) and ‘shimming’ as is it can be quite confusing.

    For an introduction to managed COM Add-ins please see:
    http://www.excelkb.com/article.aspx?id=10204

    For an introduction to ‘shimming’ please see:
    http://www.excelkb.com/article.aspx?id=10202

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 23, 2006 @ 1:56 pm

  32. Hey Will,

    The basics is to use an XLA Addin written in VBA as your “front loader”. The key reason is that it will load on no matter what platform it is run: XL’97 through 2007.

    The VBA code within ThisWorkbook.Workbook_Open() event handler of the XLA would then have to (a) determine the current Excel.Application.Version and then (b) load the correct COM DLL or Managed COM DLL.

    It’s the “(b)” part in the above that’s a little tricky. It’s not hard with a COM DLL or Managed COM DLL, but if you want to use a Shimmed Managed COM DLL — which is what we should be using if doing a Managed COM DLL — then, well, this is going to be hard…

    Mike

    Comment by Mike Rosenblum — October 23, 2006 @ 3:13 pm

  33. Thanks to you both. Dennis’ links were excellent reading material 🙂

    Mike, when you put it like that it becomes much clearer. I remember Simon Murphy trying to explain shimming to me at the XL conference in the summer. For one I was hung over due to many beers too many with Andy Pope & Carl Mackinder and for another it was too hot to concentrate… but this conversation is making it clearer.

    Time to dust off VS2005 and have a play me thinks..

    Regards,

    Will

    Comment by Will Riley — October 23, 2006 @ 10:30 pm

  34. Yep sure makes intresting reading, I think I just about kept up with it, well the concepts anyway, I need to get a better understanding on DTExtensibility2, but yeah. Agian it shows the pain involved in using .Net with Com servers.

    It’s intresting, when you read some books and sites, they say things like, “Luckily, working with com from .Net is easy… blah blah balh vs makes an AI… blah, blah b..”

    hummmm, Not if you actaully have to do somthing usefull, and deploy in the real world!

    Comment by Ross — October 24, 2006 @ 10:48 am

  35. Ross,

    From my point of view it’s more of a shortcoming of the present Excel architecture then with .NET.

    As for applying different technologies on real world needs I believe that some developers, especially within large corporates, don’t have any other choices then to use .NET.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 24, 2006 @ 2:46 pm

  36. I saw 35 comments and though maybe there was a flame war going on!

    I see deployment as the biggest pain point with this stuff, being ‘forced’ to use .net for my big corporate clients would suit me fine – most of them wouldn’t even let me install a vb6 com dll.

    I agree with Dennis, programming Excel with .net is like working in a time warp, once they get into the same decade then hopefully some of this misery will go away. I’ve only used C# and it just seems to fight against you at every turn – a bit like trying to do some complex API stuff with VB – it can do it, but you get the feeling you are are really straining. (and I’m just talking about reading a writing cell values nothing unusual)

    Ross, I would encourage you to look at C++, I think there is plenty of mileage in it yet. VS 6.0 is easier than 2003/2005 in many ways as it doesn’t have any .net noise distracting you. MFC isn’t that much harder than VB, and a great foundation for understanding the .net framework.

    Will, I noticed a few blank stares at the conference – I’ll need to re-think that presentation if I ever do it again.

    cheers
    Simon

    Comment by Simon Murphy — October 25, 2006 @ 12:05 am

  37. Simon,

    Sorry to make You disappointed about the flame war 😉

    “MFC isn’t that much harder than VB, and a great foundation for understanding the .net framework.”

    That’s an interesting point which I’ve never seen anyone else mention before.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 25, 2006 @ 9:06 am

  38. Hi Simon,

    Strange you should bring up the C thing, I remember talking about this over at DDOE with you.

    I know this is a bit off topic, but… I got a few C,C++,C++.net, C#.net books out a few weeks ago, It looks ok. I’d like to look at C++ more. I was reading a bit on the codeproject site saying how MS C++ (eps in VS05) is coming closed to ISO stanards. I’ve heard of MFC, but I’m not to sure what it really is.

    My plans right now are to write a VB managed com addin, then have a go at a C# one.

    I think i might try to have a look at C++ (with Xll’s) after that, I know it’s in PED, i did start to look before, but usual got side tracked!

    Cheers All,
    Ross

    Comment by Ross — October 25, 2006 @ 2:00 pm

  39. Hi Simon,

    Don’t get me wrong. The presentation was fine, it was just when you went off about shimming that you lost me! 🙂 I really don’t see that you need to alter the presentation much at all. It was better pitched at that level – if I understood “everything” the presenters told me there’d be little point going along….

    Anyway, Ross, PED?? Are you referring to Bullen et al’s book ? I must dig that out for this little project & look at C++ as well. Gotta crack this nut – may not end up doing a whole lot with XL, but I need more exposure to VS05 in order to get the best out of my new SQL 2005 reporting environment – XL seems to be a good place to start given my familiarity with the Object Model etc..

    Cheers,

    Will

    Comment by Will Riley — October 25, 2006 @ 8:46 pm

  40. PED yes ProXLdev ;-).

    C++ Xll’s is somthing that appeals to me. All we need is another 10 hours a day.

    Comment by Ross — October 26, 2006 @ 10:31 am

  41. Sorry to spoil the tone of this discussion with the mundane but I can not seem to reference Microsoft.Office.Interop.Excel.

    I am using Office 2003 with VS 2005 (Framework 2.0) Do I need VSTO ?
    I have had a look around but and even found an Office 2003 “named space” for Framework 1.1 but it does not seem to help. When I try to add a reference I get a list of named spaces (not including Microsoft.Office.Interop.Excel) and when I try to “add” I just get a projects tab with no ability to browse.

    Carl

    Comment by Carl Mackinder — October 29, 2006 @ 5:21 pm

  42. Carl,

    Have You installed the PIAs for Office 2003? If yes then You should be able to set a reference to Excel 11.0 on the COM tab (via the command Project | Add references…)

    If no then You can either use the installation CD to install them or download them from MSFT: http://www.microsoft.com/downloads/details.aspx?familyid=3c9a983a-ac14-4125-8ba0-d36d67e0f4ad&displaylang=en

    BTW, You don’t ‘spoil’ anything here 😉

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 29, 2006 @ 5:48 pm

  43. Many Thanks Dennis.

    It is working now and I have learnt an incredable amount about VB.NET in just a few hours thanks to you !!!

    I changed the loadfile to
    Dim LoaderFile As String = “c:\test\Loader.txt”
    and the log file to
    Dim Writer As StreamWriter = File.AppendText(“c:\test\Log.txt”)
    because I was getting confused about where they were going.

    Just one small issue the CopyFormRecordset gives me a Exception from HRESULT: 0x800A01A8 error. I can loop through the recordset and drop the data into the sheet but can not dump it in one go ? I tried Google and nothing came up.

    Anyway thanks again for a fantastic article.

    Cheers
    Carl

    Comment by Carl Mackinder — October 29, 2006 @ 7:57 pm

  44. Carl,

    You’re most welcome 🙂

    “Just one small issue the CopyFormRecordset gives me a Exception from HRESULT: 0×800A01A8 error.”

    I’m not sure why it appears… Will it make any change if You switch to the COM Library Microsoft ActiveX Data Objects Library version 2.8 instead of .NET’s ADODB library?

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 29, 2006 @ 9:00 pm

  45. Dennis,

    I was using the COM ADO Library. For some reason when I extended the xlData range it started working. When I change back to a single cell the error returns ? Anyway all is well now.

    I think I understand most of the code now apart from the Implements command (as in Implements IDisposable) but I will do some reading on that.

    Thanks again and I am looking forward to the rest in the series. Great blog !

    Carl

    Comment by Carl Mackinder — October 30, 2006 @ 12:15 pm

  46. Carl,

    Thanks for the update but I’m not sure why it error with one cell in the xlData range.
    Anyway, my next blogpost later this week will cover recommended books in the fields of .NET and VSTO.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — October 30, 2006 @ 12:21 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: