Solved

Adding 2 x Excel 2010 Add-Ins available to all users

Posted on 2014-03-19
18
1,334 Views
Last Modified: 2014-03-31
Hiya,

We have 2 x Excel 2010 add-ins. One add-in.xla and one add-in.xlam etc

Is there any way to make these add-ins add and open automatically for all users of a Remote Desktop Services Services Server 2008 R2 server when they open Excel 2010 on this server?

Just as note. I don't know if it makes any difference but the users have roaming profiles
0
Comment
Question by:rhiancohen
  • 10
  • 8
18 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
When you say:
open Excel 2010 on this server?

You mean on the server, Excel 2010 is installed as a software and when the users connect thru remote desktop they will be running the excel that is on the server and not the one on their machine correct ?

gowflow
0
 
LVL 1

Author Comment

by:rhiancohen
Comment Utility
Correct Excel is installed on the Remote Desktop Server and users mstsc into this and use Excel.

I did try running a bat script to put these 2 addins in via the below as when I installed them manually I could see the registry entries for the addins in the paths below

reg add HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN /t REG_SZ /d <"path to add-in">

and

reg add HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options\ /v OPEN1 /t REG_SZ /d <"Path to add-in">

/v = name of key
/t = type of key
/d = Data to go in the key (in my case the path name to the add-in)


But this didn't work and ended up screwing up the opening of Excel which then started saying it couldn't access paths etc. My test user was a local admin so I don't think it was a permission problem. I'm not sure why this didn't work though as I thought this was quite a neat solution but maybe it requires other entries I wasn't aware of.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
you will need to repair Excel on that server thru Add Remove Programs then login as an admin on that server physically and open Excel goto Excel options / Addin / Go / on the list click on the 2 addin (you will probably only see the xlsam .. not sure if you can see the .xla) close excel then try as remote desktop.

gowflow
0
 
LVL 1

Author Comment

by:rhiancohen
Comment Utility
I just removed the 2 entries from the registry and then Excel opened fine
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
so is your issue resolved ?
gowflow
0
 
LVL 1

Author Comment

by:rhiancohen
Comment Utility
No I still need a solution that adds the 2 Excel add-ins automatically for all users

All I was saying that I can get back to normal after running my batch script and it causing the Excel issues by removing the 2 OPEN keys from the registry but now back to square 1 again
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Did you try my suggestion ? as when you add the addin as administrator it should be added automatically to all users.

Here it is, skipping the repair part.

quote
you will need to repair Excel on that server thru Add Remove Programs then login as an admin on that server physically and open Excel goto Excel options / Addin / Go / on the list click on the 2 addin (you will probably only see the xlsam .. not sure if you can see the .xla) close excel then try as remote desktop.
unquote

gowflow
0
 
LVL 1

Author Comment

by:rhiancohen
Comment Utility
Just tried this but this didn't work either sorry.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
what happens ? lets dissect.

1) you login as administrator ? yes/no
2) You opened excel went to addin was able to see the addin? yes/no
3) if you saw the addin you saw both or only 1 ?
4) if you saw both then are they ticked if you go to GO ?
5) if they are ticked then if you go developer then press visual basic can you see the code of these 2 addin on the left pane ?


If you need help you need to provide feedback not just tried but did not work !
gowlfow
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:rhiancohen
Comment Utility
When I test this I do the following

1. Log into the Remote Desktop Server as an Administrator.
2. Open Excel 2010
3. Click on the Developer tab
4. Click on Add-ins
5. Browse for the first add in. Select and hit OK and it installs
6. Click on Add-ins again and browse for second add-in and hit OK
7. Close Excel
8. Open Excel and check Add-ins are there which they are

Next try as test user

1. Log into the Remote Desktop Server as an Test User.
2. Open Excel 2010
3. Click on the Developer tab
4. Click on Add-ins
5. The Add-ins that I added as an Administrator are not there
7. Close Excel

I've also tried putting the RDS Server in Install Mode (change user /Install) and adding the Add-ins into Excel then going back to Execute Mode but this didn't work either

I've tried putting the add-ins into the same folder as the default Excel Add-ins (Analysis/Solver/Eurotool) D:\Program Files (x86)\Microsoft Office\Office14\Library but this didn't work either.

Had a look at saving an Excel 2010 template with these add-ins in and saving it to D:\Program Files (x86)\Microsoft Office\Office14\XLSTART but this didn't work either
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
don't know if it makes a difference but  I said

1. log as administrator
2. open Excel
3. goto Excel OPTIONS / Addin / Go and add the addins there


YOU DID
2. Open Excel 2010
3. Click on the Developer tab
4. Click on Add-ins

!!!
gowflow
0
 
LVL 1

Author Comment

by:rhiancohen
Comment Utility
Hiya,

I tried doing it the way you suggested below as well but the testuser still does not get the add-ins

1. log as administrator
2. open Excel
3. goto Excel OPTIONS / Addin / Go and add the addins there
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok I think I got it, hv tried it on my machine and think this is what needs to be done.

1) You will need to create on the server this path:
C:\Users\Public\Appdata\Roaming\Microsoft\Addin

Usually the addins that you already created are under:
C:\Users\[Administrator]\Appdata\Roaming\Microsoft\Addin

where [Administrator] is the login as asdministrator or the user name you loged in to perform the addins.

What we need to do is to create the path detailed in 1) like you need to create the folders all the way till Addin folder in the sequence described in 1)

2) We will need now to move the Addin from the user Administrator to this public path Addin. Now if they are already activated you need first to de-activate them.

3) To deactivate login as admin open Excel goto Addin and untick these 2 addins close Excel.

4) Now go to the location of the Addin and Cut paste in the public place. They should be under the public place and not the administrator user place.

5) Try now to see if you as administrator or any other user can see them.

gowflow
0
 
LVL 1

Author Comment

by:rhiancohen
Comment Utility
Hiya,

I have tried the above but still no joy sorry :-(

We use roaming profiles and for the Administrator, when the add-ins are manually added, the only place I can see the Add-ins in the profile is

\\Server\Profiles\User\AppData\Roaming\Microsoft\Office\Recent\add-in1
\\:\Server\Profile\User\AppData\Roaming\Microsoft\Office\Recent\add-in2

There is nothing in

\\Server\Profile\User\AppData\Roaming\Microsoft\AddIns
\\Server\Profile\User\AppData\Roaming\Microsoft\Excel

The Add-ins are on the D Drive which is a trusted location in Excel etc and Macros  are enabled

It's weird. Thanks for all your help with this by the way. Much appreciated. Thought it would be far simpler than this!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
in \\Server\Profile\User

pls list the directories you have do you have a Public one ?
I mean under Folder User
gowlfow
0
 
LVL 1

Accepted Solution

by:
rhiancohen earned 0 total points
Comment Utility
Hiya,

I spoke to a few people and came up with 2 alternative solutions to this problem.

The First Solution

The first was from this website http://poshcode.org/1811 which is a PowerShell script. This script will check if the specified Microsoft Office Excel Addins are loaded, and if not load them.. You will need to adjust the name of your add-in and the path to the add-in in the script only

I then did the following in Group Policy to add the PowerShell scripts into a Logon Policy

¿Create a new User Group Policy
¿Navigate to User Configuration > Windows Settings > Scripts (Logon/Logoff)
¿Double click on Logon
¿Click on PowerShell Scripts
¿Click Add
¿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 my domain. I dragged and dropped my 2 .ps1 scripts into the Logon script folder (SysVol share)
¿Click Apply and OK
¿Go to the scope of the Policy and make sure the users or groups are selected for the policy
¿Link the Policy to your relevant OU
¿Open cmd.exe and do gpupdate /force
¿Test logging in as a user

The second solution

This involved creating a macro enabled template with the Add-ins added which could then be put in the users XLSTART Folder which is usually under the local or roaming profile path

Instead of me writing it all out here. I wrote all of the above and this solution in my blog (link below) so it could help others and also be a reminder should I need to come back to this again.

http://www.electricmonk.org.uk/2014/03/24/adding-excel-2010-add-ins-for-all-users-on-an-rds-farm/

@gowflow Thankyou very much for your time and patience in helping me with this issue. it was much appreciated
0
 
LVL 1

Author Comment

by:rhiancohen
Comment Utility
We also found today we could actually do it with reg add commands. Basic batch script below which can then be put into a Group Policy for logon

/f overwrites current key if there and "\" has to be used to enclose paths with spaces in which then puts the path in correctly in the registry key with quotes

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
0
 
LVL 1

Author Closing Comment

by:rhiancohen
Comment Utility
All 3 solutions will work but we found the simple batch script worked the best for us using the reg add commands.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now