Announcement

Collapse
No announcement yet.

Excel com automation addin

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel com automation addin

    I am trying to get this loaded, it is seen in the automation dialog but it generates an error on selection.

    Code:
    #Compile Dll
    #Dim All
    #Com Name "Excel_Com_Addin", 0.1
    #Com Doc "This is specific information to be used in the Help String"
    #Com Guid Guid$("{92A8F70B-07FE-4CCD-8219-0F59190CD342}")
    #Com TLib On
    
    Class MyFunctions Guid$("{B2D5EE59-287D-4B63-8FBC-EAF999A70982}") As Com
    
        Interface Functions1 Guid$("{8C6F0412-9F48-48B1-80BA-B087F15DA5B1}"): Inherit Dual
    
            Property Get Value Alias "Value"() As String
                Property = Date$ & " - " & Time$
            End Property
    
        End Interface
    
    End Class
    I have added key:
    HKEY_CLASSES_ROOT\CLSID\{B2D5EE59-287D-4B63-8FBC-EAF999A70982}\Programmable

    Therefore Excel seems to be able to find the dll in the list.

    Any ideas?
    hellobasic

  • #2
    A little OT but...

    Seems to me there are a lot of posts here relating to "Using PB with Microsoft Excel."

    Worthy of some kind of special forum dedicated to this topic? Or perhaps "Using PB with Microsoft Office Applications?" This would include Word(r) and PowerPoint(r) as well as Excel(r).

    I'm not terribly interested in it myself, but every now and then I want do 'something" with an Excel spreadsheet... might be nice to know "All THings Excel" are in one place.

    And goodness knows if we garthered up all the posts re interacting with MS-Office applications that should be enough to "fill a forum."

    ???

    MCM
    Michael Mattias
    Tal Systems (retired)
    Port Washington WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      MCM,
      Makes sense especially since Excel breaks a lot of the basic COM rules if my understanding is correct.

      James

      Comment


      • #4
        What does the VBA that you are using look like in Excel. How have you referenced the object. Have you registered the dll using regsvr32.exe

        Comment


        • #5
          Yes , registered.
          I can not add the addin, it's not allowed by the addin manager, so no VBA as well then..
          hellobasic

          Comment


          • #6
            Edwin,

            An addin is a different thing to a dll. A dll as long as t is registered can be used in VBA as long as you have set a reference to it; or you are declaring it with a fully qualified class name.

            for instance you can create an object using
            Code:
            Dim fso As Object
                
            Set fso = CreateObject("Scripting.FileSystemObject")
            or you can set a reference to it on the tools/references menu (the filesystemobject is part of the Microsoft Scripting Runtime therefore I would put a check in that checkbox) Then it can be used as

            Code:
            Dim fso As FileSystemObject
                
            Set fso = New FileSystemObject
            If your dll is registered, then your dll should appear in the list of things that can be referenced.

            Comment


            • #7
              There is nothing wrong with the dll as com server.
              I actually do create them with the tlb stored inside (the tlb was in the same folder as well) but somehow it lacks info for Excel i guess.
              My .NET version works but as com-addin having the IDTExtensibility2 interface and produces a button in the ribbon.

              I need the com automation variant and of course i could try late binding but it would be a workaround.
              We have customers you know..

              So i am focussing on the automation addin part.
              I do have serious misery with my VBA, it will not store the VBA inside the xlsx and i don't know why(!)
              **** configurations!!

              Here is code i also tried a com class from dotnet inside the com- addin.

              This is the working com-addin part (UI)
              Code:
              namespace MyCOMAddin
              {
                  using System;
                  using Extensibility;
                  using System.Runtime.InteropServices;
                  using System.Windows.Forms;
                  using Microsoft.Office.Core;
                  using System.Reflection;
              
                  [GuidAttribute("32D9529F-B358-4F77-AE5E-9691A9ABA816"), ProgId("MyCOMAddin.Connect")]
                  public class Connect : Object, Extensibility.IDTExtensibility2
                  {
                      private CommandBarButton MyButton;
                      private Object applicationObject;
                      private Object addInInstance;
              
                      public void OnBeginShutdown(ref System.Array custom)
                      {
                          object omissing = System.Reflection.Missing.Value;
                          MyButton.Delete(omissing);
                          MyButton = null;
                      }
              
                      public void OnAddInsUpdate(ref System.Array custom)
                      {
                      }
              
                      public void OnStartupComplete(ref System.Array custom)
                      {
                          CommandBars oCommandBars;
                          CommandBar oStandardBar;
              
                          try
                          {
                              oCommandBars = (CommandBars)applicationObject.GetType().InvokeMember("CommandBars", BindingFlags.GetProperty, null, applicationObject, null);
                          }
                          catch (Exception)
                          {
                              // Outlook has the CommandBars collection on the Explorer object.
                              object oActiveExplorer;
                              oActiveExplorer = applicationObject.GetType().InvokeMember("ActiveExplorer", BindingFlags.GetProperty, null, applicationObject, null);
                              oCommandBars = (CommandBars)oActiveExplorer.GetType().InvokeMember("CommandBars", BindingFlags.GetProperty, null, oActiveExplorer, null);
                          }
              
                          try { oStandardBar = oCommandBars["Standard"]; }
                          catch (Exception) { oStandardBar = oCommandBars["Database"]; }
              
                          try { MyButton = (CommandBarButton)oStandardBar.Controls["My Custom Button"]; }
                          catch (Exception)
                          {
                              object omissing = System.Reflection.Missing.Value;
                              MyButton = (CommandBarButton)oStandardBar.Controls.Add(1, omissing, omissing, omissing, omissing);
                              MyButton.Caption = "My Custom Button";
                              MyButton.Style = MsoButtonStyle.msoButtonCaption;
                          }
              
                          MyButton.Tag = "My Custom Button";
                          MyButton.OnAction = "!<MyCOMAddin.Connect>";
                          MyButton.Visible = true;
                          MyButton.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.MyButton_Click);
              
                          object oName = applicationObject.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, applicationObject, null);
              
                          //System.Windows.Forms.MessageBox.Show(applicationObject.GetType().ToString(), "MyCOMAddin");
              
                          // Display a simple message to show which application you started in.
                          System.Windows.Forms.MessageBox.Show("This Addin is loaded by " + oName.ToString(), "MyCOMAddin");
                          oStandardBar = null;
                          oCommandBars = null;
                      }
              
                      public void OnDisconnection(Extensibility.ext_DisconnectMode RemoveMode, ref System.Array custom)
                      {
                          if (RemoveMode != Extensibility.ext_DisconnectMode.ext_dm_HostShutdown)
                          {
                              OnBeginShutdown(ref custom);
                          }
                          applicationObject = null;
              
              
                      }
                      public void OnConnection(
                            object application
                          , ext_ConnectMode connectMode
                          , object addInInst
                          , ref System.Array custom)
                      {
                          applicationObject = application;
                          addInInstance = addInInst;
              
                          if (connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup)
                          {
                              OnStartupComplete(ref custom);
                          }
              
                      }
              
                      private void MyButton_Click(CommandBarButton cmdBarbutton, ref bool cancel)
                      {
                          MessageBox.Show("MyButton was Clicked", "MyCOMAddin");
                      }
              
                  }
              }
              This part inside the same project does not work, i see the reference in the addin's list (MyCOMAddin.MyFunctions) but in the VBA part i can't see anything in the object explorer (MyCOMAddin.MyFunctions.NETTime).

              Here is the part visible in the addins but can not be instantiated:
              Code:
              using System;
              using System.Runtime.InteropServices;
              using Microsoft.Win32;
              
              namespace MyCOMAddin
              {
                  [Guid("5268ABE2-9B09-439d-BE97-2EA60E103EF6")]
                  [ClassInterface(ClassInterfaceType.AutoDual)]
                  [ComVisible(true)]
                  public class MyFunctions
                  {
                      public String NETTime()
                      {
                          return DateTime.Now.ToString();
                      }
              
                      [ComRegisterFunctionAttribute]
                      public static void RegisterFunction(Type type)
                      {
                          Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));
                          RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);
                          key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll", RegistryValueKind.String);
                      }
              
                      [ComUnregisterFunctionAttribute]
                      public static void UnregisterFunction(Type type)
                      {
              
                          Registry.ClassesRoot.DeleteSubKey(
              
                            GetSubKeyName(type, "Programmable"), false);
                      }
              
                      private static string GetSubKeyName(Type type, string subKeyName)
                      {
              
                          System.Text.StringBuilder s = new System.Text.StringBuilder();
                          s.Append(@"CLSID\{");
                          s.Append(type.GUID.ToString().ToUpper());
                          s.Append(@"}\");
                          s.Append(subKeyName);
                          return s.ToString();
                      }
                  }
              }
              So.. i can see my PB version in the browse part but that one can not be selected, some error pops up.
              I hate VBA and Excel with it's restrictions.
              The xll i wrote works very nice but it's old stuff.
              hellobasic

              Comment


              • #8
                Ah, the NETTime() function is working as: =NETTime()
                Now only the PB version of it would be nice to make to work.

                Btw, i have an image of the registry for the .NET version:
                http://www.hellobasic.com/images/image1.png
                hellobasic

                Comment


                • #9
                  Here is a working automation class having a Volatile function:

                  Code:
                  using System;
                  using System.Runtime.InteropServices;
                  using Microsoft.Win32;
                  using System.Reflection;
                  using Microsoft.Office.Core;
                  
                  namespace MyCOMAddin
                  {
                      [Guid("5268ABE2-9B09-439d-BE97-2EA60E103EF6")]
                      [ClassInterface(ClassInterfaceType.AutoDual)]
                      [ComVisible(true)]
                      public class MyFunctions : Extensibility.IDTExtensibility2
                      {
                          Object thisApplication = null;
                          Type thisApplicationType = null;
                          object[] oParamMissing = { Missing.Value };
                  
                          public String NETTime()
                          {
                              thisApplicationType.InvokeMember("Volatile", BindingFlags.InvokeMethod, null, thisApplication, oParamMissing);
                              //System.Windows.Forms.MessageBox.Show("NETTIME", "MyCOMAddin");
                              return DateTime.Now.ToString();
                          }
                  
                          [ComRegisterFunctionAttribute]
                          public static void RegisterFunction(Type type)
                          {
                              Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));
                              RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);
                              key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll", RegistryValueKind.String);
                          }
                  
                          [ComUnregisterFunctionAttribute]
                          public static void UnregisterFunction(Type type)
                          {
                              Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false);
                          }
                  
                          private static string GetSubKeyName(Type type, string subKeyName)
                          {
                              System.Text.StringBuilder s = new System.Text.StringBuilder();
                              s.Append(@"CLSID\{");
                              s.Append(type.GUID.ToString().ToUpper());
                              s.Append(@"}\");
                              s.Append(subKeyName);
                              return s.ToString();
                          }
                  
                          public void OnAddInsUpdate(ref Array custom)
                          {
                              throw new Exception("The method or operation is not implemented.");
                          }
                  
                          public void OnBeginShutdown(ref Array custom)
                          {
                              throw new Exception("The method or operation is not implemented.");
                          }
                  
                          public void OnConnection(object Application, Extensibility.ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
                          {
                              thisApplication = Application;
                              thisApplicationType = thisApplication.GetType();
                              throw new Exception("The method or operation is not implemented.");
                          }
                  
                          public void OnDisconnection(Extensibility.ext_DisconnectMode RemoveMode, ref Array custom)
                          {
                              throw new Exception("The method or operation is not implemented.");
                          }
                  
                          public void OnStartupComplete(ref Array custom)
                          {
                              throw new Exception("The method or operation is not implemented.");
                          }
                      }
                  }
                  hellobasic

                  Comment


                  • #10
                    The good news is that I can get Excel to load and interact with an Excel Automation Add-in that
                    is written using low-level COM. One of the key factors is the registry.
                    I will give PB9 COM a try later on, because right now I am going cycling.
                    Dominic Mitchell
                    Phoenix Visual Designer
                    http://www.phnxthunder.com

                    Comment


                    • #11
                      Ah, so there is a difference he?

                      Oh well, it would be nice if i could make a template for rapid development later on.

                      Thanks,
                      hellobasic

                      Comment


                      • #12
                        Unfortunately, the PB9 COM version is causing Excel to crash as soon as I type

                        =TestDatePB

                        into a cell.

                        The type libary generated by PB is very similar to the handcrafted one I used for low level COM.
                        Excel accepts the DLL as an Automation Add-in and does not see it as a COM Add-in, therefore, it
                        is probably safe to rule out the registry as the source of the problem.

                        The Automation Add-ins use late-binding, therefore, the problem could be what is going on under the
                        hood on the PB side. That requires my debugging code. Maybe I will take another stab at it on the
                        weekend.
                        Dominic Mitchell
                        Phoenix Visual Designer
                        http://www.phnxthunder.com

                        Comment


                        • #13
                          If you want to play with this stuff using PB9 COM, the code below shows the registry
                          entries that are needed for and Excel Automation Add-in.
                          I will upload the low-level COM version later.

                          BASIC code file ExcelAddInPB.bas
                          Code:
                          #DIM ALL
                          #REGISTER NONE
                          #COMPILE DLL "ExcelAddInPB.dll"
                          #OPTION VERSION4
                          
                          $LIBID_EXCELADDINPB     = GUID$("{E930D1D2-6D23-4064-8301-9F81D9CADE13}")
                          $CLSID_CEXCELADDINPB    = GUID$("{F593699A-271C-47F6-908B-CC21998E3098}")
                          $IID_IEXCELADDINPB      = GUID$("{3249B5F5-88F9-44AE-A1D3-05158B465439}")
                          
                          $TITLE = "Prometheus Excel Automation Add-in(PB COM)"
                          
                          ' Version independent programmatic ID
                          $VER_IND_PROGID = "Prometheus.AddInPB"
                          ' Programmatic ID
                          $PROGID  = "Prometheus.AddInPB.1"
                          
                          #COM NAME "ExcelAddInPB", 1.0
                          #COM DOC "Prometheus Excel Automation Add-in(PB COM)"
                          #COM GUID $LIBID_EXCELADDINPB
                          #COM TLIB OFF 'ON
                          
                          #INCLUDE "WIN32API.INC"
                          #INCLUDE "shlwapi.inc"
                          
                          #RESOURCE "ExcelAddInPB.PBR"
                          
                          GLOBAL ghInstance   AS DWORD
                          
                          DECLARE FUNCTION LoadTypeLib LIB "OLEAUT32.DLL" ALIAS "LoadTypeLib" (BYVAL lpwszFile AS DWORD, pptlib AS IUNKNOWN) AS LONG
                          DECLARE FUNCTION RegisterTypeLib LIB "OLEAUT32.DLL" ALIAS "RegisterTypeLib" (BYVAL ptlib AS IUNKNOWN, BYVAL lpwszFullPath AS DWORD, BYVAL lpwszHelpDir AS DWORD) AS LONG
                          
                          '-------------------------------------------------------------------------------
                          
                          CLASS CExcelAddInPB $CLSID_CEXCELADDINPB AS COM
                          
                            INTERFACE IExcelAddInPB $IID_IEXCELADDINPB
                              INHERIT DUAL
                          
                              PROPERTY GET TestDatePB<&H00000001> () AS STRING
                          
                                LOCAL sDate   AS STRING
                          
                                sDate = DATE$ + " - " + TIME$
                          
                                PROPERTY = UCODE$(sDate)
                          
                              END PROPERTY
                          
                            END INTERFACE
                          
                          END CLASS
                          
                          '-------------------------------------------------------------------------------
                          
                          FUNCTION DllRegisterServer ALIAS "DllRegisterServer" () EXPORT AS LONG
                          
                            LOCAL szModule    AS ASCIIZ * 1024
                            LOCAL szValue     AS ASCIIZ * 2048
                            LOCAL szKey       AS ASCIIZ * %MAX_PATH
                            LOCAL szKey2      AS ASCIIZ * %MAX_PATH
                            LOCAL lpwsz       AS DWORD
                            LOCAL cbBuf       AS DWORD
                            LOCAL pITypeLib   AS DWORD
                            LOCAL hKey        AS DWORD
                            LOCAL hr          AS LONG
                          
                            LOCAL sFile       AS STRING
                            LOCAL oTypeLib    AS IUNKNOWN
                          
                            ' Obtain the path to this module's executable file for later use.
                            GetModuleFileName ghInstance, szModule, SIZEOF(szModule)
                          
                            ' CLSID key
                            ' ---------
                            ' Build the key CLSID\{...}
                            szKey = "CLSID\"
                            lstrcat szKey, GUIDTXT$($CLSID_CEXCELADDINPB)
                          
                            szValue = $TITLE
                            SHSetValue %HKEY_CLASSES_ROOT, szKey, BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                          
                          	' Add the ProgID subkey under the CLSID key.
                            szValue = $PROGID
                            SHSetValue %HKEY_CLASSES_ROOT, szKey + "\ProgID", BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                          
                          	' Add the version-independent ProgID subkey under CLSID key.
                            szValue = $VER_IND_PROGID
                            SHSetValue %HKEY_CLASSES_ROOT, szKey + "\VersionIndependentProgID", BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                          
                          	' Add the Programmable subkey
                          	RegCreateKeyEx %HKEY_CLASSES_ROOT, _
                                           szKey + "\Programmable", _
                                           0, _
                                           BYVAL %NULL, _
                                           %REG_OPTION_NON_VOLATILE, _
                                           %KEY_ALL_ACCESS, _
                                           BYVAL %NULL, _
                                           BYVAL VARPTR(hKey),_
                                           BYVAL %NULL
                            RegCloseKey hKey
                            	
                          	' Add the server filename subkey under the CLSID key.
                            SHSetValue %HKEY_CLASSES_ROOT, szKey + "\InprocServer32", BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szModule), LEN(szModule)
                          
                            ' Add the named value, Threading model, to the InprocServer32 subkey.
                            szValue = "Apartment"
                            SHSetValue %HKEY_CLASSES_ROOT, szKey + "\InprocServer32", "ThreadingModel", %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                          
                            ' Add the Typelib subkey under the CLSID key.
                            szValue = GUIDTXT$($LIBID_EXCELADDINPB)
                            SHSetValue %HKEY_CLASSES_ROOT, szKey + "\TypeLib", BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                          
                            ' Add the Version subkey under the CLSID key.
                            szValue = "1.0"
                            SHSetValue %HKEY_CLASSES_ROOT, szKey + "\Version", BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                          
                            ' Add the following ProgID key to avoid an "invalid add-in" error from Excel
                            szKey2  = $PROGID
                            szValue = $TITLE
                            SHSetValue %HKEY_CLASSES_ROOT, szKey2, BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                            szValue = GUIDTXT$($CLSID_CEXCELADDINPB)
                            SHSetValue %HKEY_CLASSES_ROOT, szKey2 + "\CLSID", BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                          
                            ' Register the type library
                            sFile = UCODE$(szModule)
                            hr = LoadTypeLib(STRPTR(sFile), oTypeLib)
                            IF hr = %S_OK THEN
                              hr = RegisterTypeLib(oTypeLib, STRPTR(sFile), STRPTR(sFile))
                              oTypeLib = NOTHING
                            END IF
                          
                            FUNCTION = %S_OK
                            	
                          END FUNCTION
                          
                          '-------------------------------------------------------------------------------
                          
                          FUNCTION DllUnregisterServer ALIAS "DllUnregisterServer" () EXPORT AS LONG
                          
                            LOCAL szKey       AS ASCIIZ * %MAX_PATH
                            LOCAL lResult     AS LONG
                          
                            ' Build the key CLSID\{...}
                            szKey = "CLSID\"
                            lstrcat szKey, GUIDTXT$($CLSID_CEXCELADDINPB)
                          
                            ' Delete the CLSID Key - CLSID\{...}
                            SHDeleteKey %HKEY_CLASSES_ROOT, szKey
                          
                            ' Delete the TypeLib key.
                            szKey = "TypeLib\"
                            lstrcat szKey, GUIDTXT$($LIBID_EXCELADDINPB)
                            SHDeleteKey %HKEY_CLASSES_ROOT, szKey
                          
                            ' Delete the ProgID key.
                            szKey = $PROGID
                            SHDeleteKey %HKEY_CLASSES_ROOT, szKey
                          
                            FUNCTION = %S_OK
                          
                          END FUNCTION
                          
                          '----------------------------------------------------------------------
                          '
                          '   FUNCTION: LibMain()
                          '   PURPOSE:
                          '   RETURNS:
                          '
                          '----------------------------------------------------------------------
                          
                          FUNCTION LIBMAIN _
                            ( _
                            BYVAL hInstance   AS LONG, _  ' DLL instance handle
                            BYVAL fwdReason   AS LONG, _  ' Why the DLL entry-point is being called
                            BYVAL lpvReserved AS LONG _   ' Further aspects of the DLL initialization and cleanup
                            ) EXPORT AS LONG
                          
                            SELECT CASE fwdReason
                              CASE %DLL_PROCESS_ATTACH
                                ghInstance = hInstance
                                ' We don't need to do any thread initialization
                                DisableThreadLibraryCalls hInstance
                          
                              CASE %DLL_THREAD_ATTACH
                          
                              CASE %DLL_PROCESS_DETACH
                          
                              CASE %DLL_THREAD_DETACH
                            END SELECT
                          
                            FUNCTION = %TRUE
                          
                          END FUNCTION
                          Resource script file ExcelAddInPB.rc
                          Code:
                          // Phoenix generated resource script.
                          
                          // NOTE: Code may be modified or placed anywhere in a file except
                          //       inside a >>PHNX_BEGIN_XXX and >>PHNX_END_XXX pair of tags.
                          //       The >>PHNX_XXX_LOAD code block is programmer accessible, and is
                          //       only modified when a project is packaged as a custom control.
                          //       DO NOT remove the >>PHNX_BEGIN_XXX/>>PHNX_END_XXX tags.
                          //       The code generator modifies tagged blocks on every build.
                          
                          // >>PHNX_BEGIN_INCLUDES
                          //========================================================================================
                          #include "resource.h"
                          //========================================================================================
                          // >>PHNX_END_INCLUDES
                          
                          #define IDR_TYPELIB                         1
                          
                          IDR_TYPELIB                 TYPELIB DISCARDABLE "ExcelAddInPB.tlb"
                          
                          // >>PHNX_BEGIN_RESOURCE
                          //========================================================================================
                          //========================================================================================
                          // >>PHNX_END_RESOURCE
                          
                          // >>PHNX_BEGIN_VERSION
                          //========================================================================================
                          
                          ////////////////////////////////////////////////////////////////////////////////
                          //
                          //  Version
                          //
                          
                          VS_VERSION_INFO VERSIONINFO
                          FILEVERSION 1,0,0,0
                          PRODUCTVERSION 1,0,0,0
                          FILEFLAGSMASK VS_FFI_FILEFLAGSMASK
                          FILEFLAGS 0X0L
                          FILESUBTYPE 0X0L
                          BEGIN
                            BLOCK "StringFileInfo"
                            BEGIN
                              BLOCK "040904E4"  // Lang=English (United States), CharSet=Windows, Multilingual
                              BEGIN
                                VALUE "Comments", "Developed by Dominic Mitchell - Prometheus Software\r\nMississauga\0"
                                VALUE "CompanyName", "Prometheus Software\0"
                                VALUE "FileDescription", "Excel Automation Add-in using PowerBASIC COM\0"
                                VALUE "FileVersion", "1.0.0.0\0"
                                VALUE "InternalName", "ExcelAddInPB\0"
                                VALUE "LegalCopyright", "Copyright © 2003 - 2009\0"
                                VALUE "LegalTrademarks", "  \0"
                                VALUE "OLESelfRegister", " \0"
                                VALUE "OriginalFilename", "ExcelAddInPB.dll\0"
                                VALUE "ProductName", "Excel Automation Add-in(PB COM)\0"
                                VALUE "ProductVersion", "1.0.0.0\0"
                                VALUE "Email", "[email protected]\0"
                              END
                            END
                            BLOCK "VarFileInfo"
                            BEGIN
                              VALUE "Translation", 0X409, 1252
                            END
                          END
                          //========================================================================================
                          // >>PHNX_END_VERSION
                          
                          // >>PHNX_BEGIN_MANIFEST
                          //========================================================================================
                          //========================================================================================
                          // >>PHNX_END_MANIFEST
                          Last edited by Dominic Mitchell; 1 Apr 2009, 08:52 AM. Reason: Might as well post the entire thing.
                          Dominic Mitchell
                          Phoenix Visual Designer
                          http://www.phnxthunder.com

                          Comment


                          • #14
                            Hey, thank you for your work.
                            I'll rewrite it to make that work tomorrow or later.
                            (I have a day off )

                            Edwin,
                            hellobasic

                            Comment


                            • #15
                              As promised, the low-level COM version that works. As noted, the PB9-COM version does not
                              work. A preliminary investigation showed that failure occurs during calls to IDispatch::GetIDsOfNames
                              when trying to map the TestDate function to its corresponding DISPID.

                              The zip contains the compiled Automation Add-in, exceladdin.dll, for folks interested in doing a quick test.

                              The MIDL compiler is needed to compile the IDL file.
                              This is a Phoenix project, but Phoenix is not required to view, modify or compile the source code.

                              This Automation Add-in has two functions TestDate and TestCapital.

                              To test:
                              1) Use regsvr32 to register the ExcelAddIn.dll
                              2) Add as an Automation Add-in to Excel

                              To try out TestDate which returns the time and date, type

                              =TestDate()

                              into a cell.

                              TestCapital returns the capital of a country. For example, to return the capital of Grenada, type

                              =TestCapital("Grenada")

                              into a cell.

                              The file attached to this post, exceladdin.zip, contains the source files and a compiled DLL.
                              Code:
                              OleApi.inc         <--- include file to avoid possible conflicts with includes from other sources
                              OleApi.bas         <--- function calls to interfaces
                              
                              ExcelAddIn.inc     <--- ExcelAddIn class
                              ExcelAddIn.bas     <--- ExcelAddIn class
                              
                              IExcelAddIn.inc    <--- IExcelAddIn interface
                              IExcelAddIn.bas    <--- IExcelAddIn interface
                              
                              IClassFactory.inc  <--- IClassFactory
                              IClassFactory.bas  <--- IClassFactory
                              
                              Utility2.inc       <--- useful functions
                              Utility2.bas       <--- useful functions
                              
                              ExcelAddIn.h       <--- resource files
                              ExcelAddIn.rc      <--- resource files
                              ExcelAddIn.res     <--- resource files
                              ExcelAddIn.pbr     <--- resource files
                              
                              ExcelAddIn.idl     <--- IDL file
                              ExcelAddIn.tlb     <--- type library(compiled IDL file)
                              
                              ExcelAddIn.pbp     <--- Phoenix project file
                              
                              ExelAddIn.dll      <--- compiled Automation Add-in
                              Attached Files
                              Dominic Mitchell
                              Phoenix Visual Designer
                              http://www.phnxthunder.com

                              Comment


                              • #16
                                Thanks!

                                It is accepted by Excel (2007)

                                For interested, for this dll you'll need to add the Excel-addin and click the automation button (almost similar as connecting an xll).

                                So not com-...
                                hellobasic

                                Comment


                                • #17
                                  I am still trying to find the difference.
                                  Maybe it's a tlb issue??

                                  My code looks like:
                                  Code:
                                  #Com Name "Excel_Com_Addin", 0.1
                                  #Com Doc "This is specific information to be used in the Help String"
                                  #Com Guid Guid$("{92A8F70B-07FE-4CCD-8219-0F59190CD342}")
                                  #Com Tlib On

                                  And the class:
                                  Code:
                                  Class AAAAAAAAA Guid$("{B2D5EE59-287D-4B63-8FBC-EAF999A70982}") As Com
                                  
                                      Interface Functions1 Guid$("{8C6F0412-9F48-48B1-80BA-B087F15DA5B1}"): Inherit Dual
                                  
                                          Property Get Value Alias "Value"() As String
                                              Property = Date$ & " - " & Time$
                                          End Property
                                  
                                      End Interface
                                  
                                  End Class
                                  Here is the dll with the tlb embedded but also supplied as separate file.
                                  http://www.hellobasic.com/trials/excel_com_addin.zip

                                  After registering please add Programmable to:
                                  HKEY_CLASSES_ROOT\CLSID\{B2D5EE59-287D-4B63-8FBC-EAF999A70982}

                                  This part of the registry looks like:
                                  Code:
                                  Sleutelnaam:          HKEY_CLASSES_ROOT\CLSID\{B2D5EE59-287D-4B63-8FBC-EAF999A70982}
                                  Klassenaam:        <Geen klasse>
                                  Laatste keer geschreven naar dit bestand:    8-4-2009 - 10:14
                                  Waarde 0
                                    Naam:            <Naamloos>
                                    Type:            REG_SZ
                                    Gegevens:            AAAAAAAAA
                                  
                                  
                                  Sleutelnaam:          HKEY_CLASSES_ROOT\CLSID\{B2D5EE59-287D-4B63-8FBC-EAF999A70982}\InProcServer32
                                  Klassenaam:        <Geen klasse>
                                  Laatste keer geschreven naar dit bestand:    8-4-2009 - 10:16
                                  Waarde 0
                                    Naam:            <Naamloos>
                                    Type:            REG_SZ
                                    Gegevens:            V:\Programming\Com\Excel_Com_Addin\EXCEL_COM_ADDIN.DLL
                                  
                                  Waarde 1
                                    Naam:            ThreadingModel
                                    Type:            REG_SZ
                                    Gegevens:            Apartment
                                  
                                  
                                  Sleutelnaam:          HKEY_CLASSES_ROOT\CLSID\{B2D5EE59-287D-4B63-8FBC-EAF999A70982}\ProgID
                                  Klassenaam:        <Geen klasse>
                                  Laatste keer geschreven naar dit bestand:    8-4-2009 - 10:13
                                  Waarde 0
                                    Naam:            <Naamloos>
                                    Type:            REG_SZ
                                    Gegevens:            AAAAAAAAA
                                  
                                  
                                  Sleutelnaam:          HKEY_CLASSES_ROOT\CLSID\{B2D5EE59-287D-4B63-8FBC-EAF999A70982}\Programmable
                                  Klassenaam:        <Geen klasse>
                                  Laatste keer geschreven naar dit bestand:    8-4-2009 - 10:14
                                  Thanks for your help!
                                  hellobasic

                                  Comment


                                  • #18
                                    If by difference you mean the PB9-COM version it is not being accepted, the following registry keys
                                    must also be present:

                                    HKEY_CLASSES_ROOT\<programmatic id>\<title>
                                    HKEY_CLASSES_ROOT\<programmatic id>\CLSID\<class id>

                                    As shown in the following code taken from DllRegisterServer.
                                    Code:
                                      ' Add the following ProgID key to avoid an "invalid add-in" error from Excel
                                      szKey2  = $PROGID
                                      szValue = $TITLE
                                      SHSetValue %HKEY_CLASSES_ROOT, szKey2, BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                                      szValue = GUIDTXT$($CLSID_CEXCELADDINPB)
                                      SHSetValue %HKEY_CLASSES_ROOT, szKey2 + "\CLSID", BYVAL %NULL, %REG_SZ, BYVAL VARPTR(szValue), LEN(szValue)
                                    Simply adding this key

                                    HKEY_CLASSES_ROOT\CLSID\<class id>\Programmable

                                    without the other two shown above is not going to get it accepted.

                                    The type library is fine.
                                    Dominic Mitchell
                                    Phoenix Visual Designer
                                    http://www.phnxthunder.com

                                    Comment


                                    • #19
                                      Still no luck, the progid *was* already registered:

                                      Code:
                                      Sleutelnaam:          HKEY_CLASSES_ROOT\AAAAAAAAA
                                      Klassenaam:        <Geen klasse>
                                      Laatste keer geschreven naar dit bestand:    8-4-2009 - 10:13
                                      Waarde 0
                                        Naam:            <Naamloos>
                                        Type:            REG_SZ
                                        Gegevens:            Excel_Com_Addin
                                      
                                      
                                      Sleutelnaam:          HKEY_CLASSES_ROOT\AAAAAAAAA\CLSID
                                      Klassenaam:        <Geen klasse>
                                      Laatste keer geschreven naar dit bestand:    8-4-2009 - 10:13
                                      Waarde 0
                                        Naam:            <Naamloos>
                                        Type:            REG_SZ
                                        Gegevens:            {B2D5EE59-287D-4B63-8FBC-EAF999A70982}
                                      Maybe the progid needs to be extended like you have: "Prometheus.AddIn"
                                      hellobasic

                                      Comment


                                      • #20
                                        Maybe the progid needs to be extended like you have: "Prometheus.AddIn"
                                        Maybe, however, according to the reference material on Excel Add-in limitations, any text is fine
                                        for the ProgID as long as it does not have underscores or spaces, extended characters(Cyrillic etc.)
                                        and is not more than 39 characters.

                                        If it is not being accepted, then the problem is the registry. The PB9-COM version I posted got
                                        accepted as an Automation Add-in. Excel crashed after I attempted to use the function.

                                        By the way, this line in your code
                                        Code:
                                          Property = Date$ & " - " & Time$
                                        should be converted to unicode.

                                        Did you try your Add-in using the version of DllRegisterServer and DllUnregisterServer I posted?
                                        Dominic Mitchell
                                        Phoenix Visual Designer
                                        http://www.phnxthunder.com

                                        Comment

                                        Working...
                                        X