Add .xll as AddIn to Excel-CodePudding

I got a .xll file that I can easily add to excel by doing this: Options > Addins > Browse > double click .xll file It gets imported activated (and it remains in my excel addins every time I close and open Excel).

This is the manual way I try to replace with a script.

PowerShell

$excel=New-Object -ComObject excel.application
$excel.RegisterXLL("C:\temp\v-0.0.1-20210906\LS-ZmqRtd-AddIn64.xll")
$excel.Visible = "$True"
#$excel.Quit()

This will create an instance of Excel, register the XLL (I get a "true" in my console) and show the created instance. But when I then go to AddIns, the AddIn isn't there.

Python

xl = win32com.client.gencache.EnsureDispatch("Excel.Application")
xl.Visible = True
xl.RegisterXLL(
    "C:/Users/michael.k/Desktop/v-0.0.1-20210906/LS-ZmqRtd-AddIn64.xll"
)
wb = xl.Workbooks.Open("C:/Users/michael.k/Desktop/v-0.0.1-20210906/Test.xlsx")

But this behaves like the Powershell script.

So.. how can I add my .xll file into Excel to stay there permanently? Any suggestions?

Thanks in advance!

CodePudding user response：

I would either:

Add some VBA to the workbook open event to register the XLL, this will work for a specific workbook
Write the registry key for the XLL (note Excel must be closed whilst you write the registry key) Here is a link to a LUA script to do that: this will show you how and you can rewrite in the language you want to use. https://jkp-ads.com/articles/AddinsAndSetupFactory.asp

CodePudding user response：

Thanks to the link Charles Williams gave me, I was able to do it a little bit different.

You can easily create a registry key to let excel know that it should run the .xll-file.

# initializing new variables
$req_path = Get-Item -Path Registry::HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options |
  Select-Object -ExpandProperty Property
$newest_version = (Get-ChildItem -Path I:\Software\LS-ZmqRtd\Test\ -Directory | sort lastwritetime | Select -Last 1).Name
$full_path_new_version = '/R "I:\Software\LS-ZmqRtd\Test\' $newest_version '\LS-ZmqRtd-AddIn64.xll"'
$only_opens = @()
[bool]$ls_zmqrtd_found = $false
[bool]$ls_zmqrtd_updated = $false
$count_opens = 0

# welcome message
echo ">> checking if the LS-ZmqRtd addin is installed and has the newest version.."
Start-Sleep -s 5

# check if there are regkeys that contain 'OPEN' in their name (if yes, add them to the $only_opens array)
foreach ($entry in $req_path)
{
    if ($entry -like "OPEN*")
    {
        $only_opens  = $entry
    }
}

if (!$only_opens) # check if array is empty (if yes, add the new regkey for LS-ZmqRtd)
{
    echo ">> the LS-ZmqRtd addin couldn't be found.. adding it to excel now."
    Start-Sleep -s 2
    New-ItemProperty -Path HKCU:\Software\Microsoft\Office\16.0\Excel\Options -Name OPEN -PropertyType String -Value $full_path_new_version
    echo ">> addin was added to excel successfully - this requires Excel to be fully closed and re-opened."
}
else # if no, check if one of the regkeys have the LS-ZmqRtd path value (if found, set $ls_zmqrtd_found to true - else remain false)
{  
    foreach ($open in $only_opens)
    {
        $value = (Get-ItemProperty -Path "HKCU:\Software\Microsoft\Office\16.0\Excel\Options" -Name $open).$open
        if ($value -eq $full_path_new_version)
        {
            $ls_zmqrtd_found = $true
        }
        else
        {
            echo ">> found an old version of LS-ZmqRtd.. replacing it with the new one now."
            Start-Sleep -s 2
            Set-ItemProperty -Path HKCU:\Software\Microsoft\Office\16.0\Excel\Options -Name $open -Value $full_path_new_version
            $ls_zmqrtd_updated = $true
        }
        $count_opens  = 1
    }


    if ($ls_zmqrtd_found -eq $true) # if $ls_zmqrtd_found is true, there is nothing to do
    {
        echo ">> found that the newest version of LS-ZmqRtd is already installed - nothing to do here."
    }
    elseif ($ls_zmqrtd_updated -eq $true)
    {
        echo ">> updated LS-ZmqRtd to the newest version - an update requires Excel to be fully closed and re-opened."
    }
    else # if $ls_zmqrtd_found is false, increment the last OPEN's number by 1 and add the new reqkey for LS-ZmqRtd
    {
        $new_reg_key = "OPEN"   ($count_opens 1)
        echo ">> the LS-ZmqRtd addin couldn't be found.. adding it to excel now."
        Start-Sleep -s 2
        New-ItemProperty -Path HKCU:\Software\Microsoft\Office\16.0\Excel\Options -Name $new_reg_key -PropertyType String -Value $full_path_new_version
        echo ">> addin was added to excel successfully - this requires Excel to be fully closed and re-opened."
    }
}

This script checks if the .xll-File is already named in a registry key.

If yes and it has our newest provided version -> do nothing
If yes but the version is old -> update the registrey key's value
If no -> create the registry key and set the value to our newest
provided version