Excel com automation addin

[Edwin Knoppert]

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

#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?

[Michael Mattias]

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

[jcfuller]

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

James

[Trevor Lane]

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

[Edwin Knoppert]

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

[Trevor Lane]

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

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

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.

[Edwin Knoppert]

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)

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:

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.

[Edwin Knoppert]

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

[Edwin Knoppert]

Here is a working automation class having a Volatile function:

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.");
        }
    }
}

[Dominic Mitchell]

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.

[Edwin Knoppert]

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,

[Dominic Mitchell]

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]

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

#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

// 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

[Edwin Knoppert]

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

Edwin,

[Dominic Mitchell]

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.

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

[Edwin Knoppert]

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-...

[Edwin Knoppert]

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

My code looks like:

#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:

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:

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!

[Dominic Mitchell]

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.

  ' 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.

[Edwin Knoppert]

Still no luck, the progid *was* already registered:

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"

[Dominic Mitchell]

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

  Property = Date$ & " - " & Time$

should be converted to unicode.

Did you try your Add-in using the version of DllRegisterServer and DllUnregisterServer I posted?