Deploy personal macro to XLSTART across all Excel versions on all PCs in a domain

Question

BACKGROUND: We've got a personal macro workbook that is shared among all people in a certain job role. It makes their jobs much easier and over time many processes have become dependent upon these macros. The macros have evolved over time and I am seeking to control the deployed version across all systems. I have an update, for example, that I would like to push out now so that everyone has the same, new, version of the macro workbook.

PROBLEM: The destination of XLSTART is not reliable across versions and even within a single version of Excel I have found that different editions could have different paths. I need to deploy this to a large number of computers and the installed version could be anything from 2003-2016

QUESTION: Is there a reliable way to find the XLSTART directory without listing every possibility of every version of Excel ever?

NOTES:

Someone used to do this manually but we are getting too big for that. Currently, I have been building a batch file that lists every possible path to XLSTART and it feels stupid. I am hoping that there is a better way that I am unaware of.

I thought it would be nice if there was an %XLSTART% variable but we have a couple of users who have multiple versions of Excel installed. (I know, don't ask). So on these systems I need to install the macro workbook in to multiple paths so that the macros are available in multiple versions of Excel installed on the same PC.

Answer 1

Thank to @DavidPostill for the idea. Here is the final solution based on his idea of getting the path from the registry. It's a deployment script written in PowerShell:

<#  Deploy-XLSTART.ps1  #>
param(
  [Parameter(Mandatory=$true, Position=0)]
  [string]$script:DeployFile,
  [Parameter(Mandatory=$false, Position=1)]
  [string]$script:LogFile = "$env:TEMP\XLSTARTDeploymentLog.csv"
)

# Only copy to a folder that exists and return false if it doesn't.
function Copy-Results {param( $source, $destination )
    if (Test-Path $destination) {
        Write-Output "Copy to: $($destination)" 
        Copy-Item -path $source -destination $destination -force
        return $true
    }

    return $false
}



# Main
$script:XLKey = "Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe"
$script:XLValue = "Path"
$script:XLSTARTPath = "$((Get-ItemProperty -Path $script:XLKey -Name $script:XLValue).$script:XLValue)XLSTART\"

if  (Copy-Results -source $script:DeployFile -destination $script:XLSTARTPath) {
    $script:ResultsFile = Get-Item -Path "$script:DeployFile" -Force
    Add-Content -path $script:LogFile "$Env:Computername,$script:XLSTARTPath,$script:ResultsFile.LastWriteTime"
} else {
    Add-Content -path $script:LogFile "$Env:Computername,File Not Found"
    exit 3
}

Answer 2

Write your macros code in the Excel sheet means in VB part and save the file as

Excel Add-In(*.xlam) Or

Excel 97-2003 Add-In(*.xla)

And import them in other systems via File -> Options -> Add-Ins Under Manage Label Select Excel Add-Ins and click go

In the next dialog Click browse button and browse your created Add-In file.

All functions will be available without creating macros in all systems.

Note: .xla files are supported in all versions prior to the software version.

Hope it help.