Tag Archive for macro

Adding Excel 2010 Add-ins for all users on an RDS Farm

March 24, 2014 PowerShell 5 comments

plus sign

The Requirements

To make 2 x Excel 2010 Add-ins load automatically whenever anyone logged into Excel 2010 on any RDS Server in the farm using their roaming profile

  • 4 x Windows 2008 R2 Remote Desktop Servers in an RDS Farm
  • 1 x Excel 2010 installed on each RDS Server
  • 2 x Excel 2010 Add-ins
  • Roaming Profiles
  • For testing purposes the D Drive was a trusted location in Excel (Via Group Policy)
  • For testing purposes, macros were enabled without prompting (Via Group Policy)

This was actually more complicated than it first looked. We found that on a per user basis when they manually add the add-ins through Excel then it retained the settings through the roaming profile no matter what server in the farm they were on which was great but we wanted to take the user aspect out of it

Adding Excel Add-ins (Unautomated)

  • Open Excel
  • Click File > Options > Add-ins

Excel Add-ins2

  • Click Go and you will see the default add-ins which come with Excel

Excel Add-ins3

  • Browse to your Add-ins and select them
  • They should then show up

Excel Add-ins5

  • If you have a look in the registry then you will see the add-ins under the following paths. Note the first Add-in is called OPEN, the second is called OPEN1 and so on
  • HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\OPEN
  • HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\OPEN1

Excel Add-ins1

What we tested to start with

We thought it might be worth a try putting the RDS Server into Install mode and installing the Add-ins

Excel Add-ins6

  • Open Excel > File > Options > Add-ins > Go Install the Add-ins under an Admin account and then close Excel
  • Come out of Install Mode

Excel Add-ins7

  • But this didn’t work for any new users logging into the server either. The Add-ins did not appear but apparently this did work for someone who posted in a forum
  • Next we had a look to see if we could use Group Policy. Note you have to download and install the relevant Microsoft Office Templates so you can see them in GPME. See below pic

Excel Add-ins8

  • But lo and behold there was not anything relating to Excel Add-ins or exactly what we needed. Apparently you are meant to be able to customise every aspect of Excel through GP but it sometime requires specialist knowledge and programming experience which we didn’t particularly want to get into.
  • Just as a note, we searched the web for a simple resolution to this and although you can find people with the same problem, we couldn’t find anyone who had this working
  • Next we will show you how we got this working via 3 methods. There may be other better solutions but these worked for us!

Working Solution 1 (Write a batch script containing reg syntax)

Bear in mind that this will be a brand new user logging into a server and opening Excel so we need to take into account that some of the necessary Office registry keys will not exist and we are using Office 2010.

The first thing we thought we could would be to write a short batch script which added the keys into the registry as per next 6 lines.

Note: If writing the below into Notepad then DO NOT USE WORD WRAP and note the “\” which enclose any path which has spaces

  • REG ADD HKEY_CURRENT_USER\Software\Microsoft\Office\ /f
  • REG ADD HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\ /f
  • REG ADD HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\ /f
  • REG ADD “HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\ \Options\ /f
  • REG ADD HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN /t REG_SZ /d “\”D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xla”\” /f
  • REG ADD HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN1 /t REG_SZ /d “\”D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv2.xlam”\” /f
  • Here is what it looks like

regadd script

  • You can the save this as a .bat file and load it into a Group Policy to run at Startup or User Logon

The Second solution (PowerShell Script)

I found this PowerShell Script which adds Excel Addins. Thanks to Jan Egil Ring

http://poshcode.org/1811

  • Copy the script into a notepad file
  • Change the name of your Add-in (Circled below)
  • Change the Path to your Add-in (Circled below)
  • Save script with a .ps1 suffix

PowerShellexcel

  • Next go to Group Policy
  • Create a new User Group Policy
  • Navigate to User Configuration > Windows Settings > Scripts (Logon/Logoff)

powershell02

  • Double click on Logon
  • Click on PowerShell Scripts

powershell03

  • Click Add

powershell04

  • Click Browse
  • From here, I click Add, and click Browse. The Add a Script dialog appears. The Browse button opens a Windows Explorer window that is centered on the SysVol share for the domain. I then dragged and dropped my tm1p.ps1 script into the Logon script folder (SysVol Share)

powershell05

  • You should then see your script as per below screenprint

powershell06

  • Click Apply and OK
  • Go to the scope of the Policy and make sure the users or groups are selected for the policy
  • Open cmd.exe and type gpupdate /force
  • Try logging on as a brand new user to the server and see if the script runs and adds your Excel Add-in

The third Solution (Creating a Macro enabled Excel Template)

Thanks to Nicholas Cohen for providing this information

  • Open Excel 2010 on one of your RDS Servers
  • Press ALT F11 to open Visual Basic for Applications
  • Right click on VBAProject (Book1) and select Insert > Module

Excel Add-ins9

  • You will then need to copy and paste the following code into the module box

Sub InstallAddIn(strFileName As String)
Dim AI As Excel.AddIn
Set AI = Application.AddIns.Add(Filename:=strFileName)
AI.Installed = True
End Sub

Sub Auto_Open()

‘replace stuff in quotes with the network path to the add-in
‘this path obviously must be accessible by the user
InstallAddIn (“D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xla”)
InstallAddIn (“D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv2.xlam”)

‘replace below code with the name of the macro that you want to run when it’s open. You need to know names of what’s inside the Add-in to put here which is why this is commented out as an example but it will run anyway with the below path
‘code/routines in add-in tm1p.xla
‘code/routines in add-in Mancalcv2.xlam
‘repeat ad-infinitum….

End Sub

  • It should look like the below

Excel

  • Now go back to Excel and do File > Save As >
  • Give it a name and save it as an Excel Macro-Enabled Template to the desktop or wherever convenient

Exceladdins21

  • Now the next task will be to put this Excel Template into the users roaming profile path under the XLSTART Folder which in most people’s case will be either of these 2 paths
  • \\server\Profiles\AppData\Roaming\Microsoft\Excel\XLSTART
  • \\server\Home\AppData\Roaming\Microsoft\Excel\XLSTART
  • Note: We redirect our users AppData folder to their home folder
  • This can then be scripted and put in a Group Policy

Exceladdins22

  • Next I’ll look at a script to do this but I haven’t got round to it yet as the PowerShell script seemed neater.

The Fourth Solution

This next solution was an added requirement to adding Excel Add-ins in a Terminal Server environment.

We had developers who logged into 2 different Terminal Server Farms and used different add-ins on each one. What we found is that the above scripts would sometimes overwrite existing add-ins when swapping from one farm to the next so we needed a script which checked if any of the Excel OPEN keys had values to start with, ignore them and add the Excel add-ins to the next available OPEN Key

Thanks very much to oBda of Experts Exchange for this script.

@echo off
setlocal enabledelayedexpansion
set AddinList[1]="D:\Program Files (x86)\ibm\cognos\tm1\bin\tm1p.xlam"
set AddinList[2]="D:\Program Files (x86)\ibm\cognos\tm1\bin\ManCalcv.xlam"
set Key=HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
set Quiet=0
set LastIndex=10
for /f "tokens=1* delims==" %%a in ('set AddinList[') do (
	if %Quiet%==0 echo Processing '%%b'
	set Value=
	set FoundAt=
	set Data=%%b
	set CompareData=!Data:"=!
	for /l %%i in (0, 1, %LastIndex%) do (
		if %%i==0 (set TestValue=OPEN) else (set TestValue=OPEN%%i)
		reg.exe query "%Key%" /v "!TestValue!" >NUL 2>&1
		if errorlevel 1 (
			if "!Value!"=="" (
				if %Quiet%==0 echo !TestValue! ... free.
				set Value=!TestValue!
			)
		) else (
			if %Quiet%==0 echo !TestValue! ... already used.
			for /f "tokens=2*" %%o in ('reg.exe query "%Key%" /v "!TestValue!"') do set CompareDataExisting=%%p
			set CompareDataExisting=!CompareDataExisting:"=!
			if /i "!CompareData!"=="!CompareDataExisting!" set FoundAt=!TestValue!
		)
	)
	if "!Value!"=="" (
		echo ERROR: Unable to find an empty index in the range of 0..%LastIndex%
		goto :eof
	)
	if "!FoundAt!"=="" (
		reg.exe add "%Key%" /v "!Value!" /t REG_SZ /d "!Data:"=\"!" /f >NUL
		if errorlevel 1 (
			echo ERROR: Could not create value '!Value!' with data '!Data!' at '%Key%'
		) else (
			if %Quiet%==0 echo Value successfully added.
		)
	) else (
		if %Quiet%==0 echo Found data in '!FoundAt!', nothing added.
	)
)