Register XLL using VBA

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Register XLL using VBA

    I want to register Palo.xll manualy in VBA code, that means that I don't want Palo to start every time Excel starts. I'm using this code:

    Application.AddIns.Add (ThisWorkbook.Path & "\Palo.xll")
    Application.RegisterXLL "PALO.XLL"

    At first, first code line: I was able to make this work if I explicitly use predefined path to palo.xll, to its install diretory. But if I'd like to install my program to another computer I cannot be sure, that the Palo is installed to same directory. Therefore I'd like the palo.xll to be the part of program (and so I'm looking at the actuall path of xla). But with 'ThisWorkbook.Path' I only get an error message.

    But the main problem with this is that these two code lines doesnt add palo interface(such as menus, functions aso.) So can anyone help me throught this?

    The post was edited 1 time, last by vesi ().

  • RE: Register XLL using VBA

    You can’t use and redistribute Palo.xll without using the whole Palo Add-in for Excel. XLL depends on rest of the installation and vice-versa. Your client has to run Palo setup on his machine to be able to use XLL as such.
    Mit freundlichen Gruessen/ With kind Regards / Meilleures salutations

    Vladislav Malicevic
    VP Development & Support
    Jedox AG
  • RE: Register XLL using VBA

    Hello again,

    I understand that I've got to install whole Palo to run it on another computer. But I'm still wondering about another thing. I do not want to load palo everytime I start excel, but only with application that uses palo, so - manually. If I set this in the Palo Setting panel and then try to load palo by using code lines shown in previous topic, I'm not able to get the Palo menu neither functions and so on. So please could you desribe how to start palo with all its features using vba code ? Thanks a lot.
  • RE: Register XLL using VBA

    We didn’t evaluate this situation before; therefore I can’t propose solution to it. I’m not sure if “late loading” is even possible from VBA. Maybe some of our community members have done something similar. All I can tell is that Palo Excel Add-in consists of two components: GUI part (.NET framework dependant shared COM Add-in) and XLL (Excel functions). Like I said earlier, these components are interdependent. You need both of them loaded for Palo Excel Add-in to work properly. GUI part tries to load XLL from current directory. So if you manage to get Shared COM Add-in loaded than you are on good way to succeed. Our future versions will have better VBA support, but the premise will probably stay the same: Add-in has to be loaded before it can be used. Hope this helps.
    Mit freundlichen Gruessen/ With kind Regards / Meilleures salutations

    Vladislav Malicevic
    VP Development & Support
    Jedox AG
  • Hi,

    the solution is quite interesting and I tried it also, but if the user doesn't have enough system rights (especially to write into registry HKEY_LOCAL_MACHINE) the Code will generate a runtime error :(

    I doubt that for this problem exist a workaround but maybe some of you experienced a similar problem?

  • hi h_decker,

    but the method is provided to load/unload COMAddins and it should not be too old. It works fine with Palo 2.x in the case that you have enough rights to write in the registry. What you are using instead?

    The reason for using load/unload Palo is that:

    VBAProject passwort prompt after quiting Excel caused by Palo

    Thanks a lot

    The post was edited 1 time, last by ChristianS ().

  • This is function for endable/disable behavior palo add-in ver.2 using VB/VBA
    Public Declare Function RegCreateKey Lib "advapi32.dll" Alias "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
    Public Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
    Public Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal cbData As Long) As Long
    Public Const REG_NONE As Long = 0
    Public Const REG_SZ As Long = 1
    Public Const REG_BINARY As Long = 3
    Public Const REG_DWORD As Long = 4
    Public Const HKEY_CLASSES_ROOT As Long = &H80000000
    Public Const HKEY_CURRENT_USER As Long = &H80000001
    Public Const HKEY_LOCAL_MACHINE As Long = &H80000002
    Public Const HKEY_USERS As Long = &H80000003
    Public Const HKEY_PERFORMANCE_DATA As Long = &H80000004
    Public Const HKEY_CURRENT_CONFIG As Long = &H80000005
    Sub SaveRegKey(hKey As Long, strPath As String, strValue As String, strData As String)
    Dim hndREG
    RegCreateKey hKey, strPath, hndREG
    RegSetValueEx hndREG, strValue, 0, REG_DWORD, ByVal strData, 4
    RegCloseKey hndREG
    End Sub
    Call SaveRegKey(HKEY_LOCAL_MACHINE, "SOFTWARE\Microsoft\Office\Excel\Addins\Jedox.Palo.XlAddin.Connect", "LoadBehavior", Chr(3))
    Call SaveRegKey(HKEY_LOCAL_MACHINE, "SOFTWARE\Microsoft\Office\Excel\Addins\Jedox.Palo.XlAddin.Connect", "LoadBehavior", Chr(0))

    The post was edited 3 times, last by vesi ().

  • Hi Vesi,

    thanks for your solution, but by this way I can only load/unload the Addin for the next excel application startup, no?!

    What I need is a possibilty to clean the memory (unload Palo) immediately when closing the workbook. The old solution (mentioned above) is working fine, but with the problem that normal windows users without access to the HKEY_LOCAL_MACHINE key recieved a runtime error (error code 80004005 - in German: "Der verbundene Zustand von Office Add-Ins registriert in HKEY_LOCaL_MACHINE kann nicht geändert werden"). Do you know any possibilty to load/unload palo when starting/closing the workbook, avoiding this runtime error?


  • Yes, I have exe program (writing in VB6) that editing registry and runnig .xla file. You have to enabled "Start automatically with Excel" in palo setting as default ;) For editing registry you have to loged with administrator rights on local computer.

    The post was edited 5 times, last by vesi ().

  • Vesi's solution only controls the loading behavior of the Palo COM-Addin. If you want to load/unload the Addin within a Excel session you have to use application.comaddins.item("jedox.palo.xladdin.connect").connect!

    The problem with missing rights for HKEY_LOCAL_MACHINE occurs if Palo is installed for all users on the machine and the executing user only has normal user rights.

    The post was edited 1 time, last by ChristianS ().