Wednesday, May 28, 2014

How to resolve issue Power Pivot ribbon disappears in Excel 2013?

If you are new to Power Pivot 2013, the most frustration is Power Pivot ribbon frequently disappears in Excel 2013. In some cases, the Power Pivot ribbon will disappear from the menu if Excel determines that the add-in is destabilizing to Excel. It might occur if Excel closes unexpectedly while the Power Pivot window is open. 

To restore the Power Pivot menu, you might do the following three steps in order:

1. Close running Microsoft Excel process

  • Go to Task Manager
  • Right click Microsoft Excel and “End task
  • Open Excel again and verify Power Pivot ribbon
2. If this is not working, verify Add-Ins installed

  • Go to File > Options > Add-Ins.
  • In the Manage box, click Disabled Items > Go.
  • Select Microsoft Office Power Pivot in Microsoft Excel 2013 and then click Enable.
3. If the previous two steps do not restore the Power Pivot ribbon, or if the ribbon disappears when you close and reopen Excel, try the following:
  • Close Excel.
  • Point to Start > Run and then type regedit.
  • In Registry Editor, expand HKEY_CURRENT_USER > Software > Microsoft > Office > 15.0 > User Settings.
  • Right-click PowerPivotExcelAddin and then click Delete.
  • Scroll back up to the top of Registry Editor.
  • Expand HKEY_CURRENT_USER > Software > Microsoft > Office > Excel > Addins.
  • Right-click PowerPivotExcelClientAddIn.NativeEntry.1 and then click Delete.
  • Close Registry Editor.
  • Open Excel.
  • Enable the add-in using the steps at the top of this article.
These three steps should resolve most of the Power Pivot ribbon missing issue.

Monday, May 19, 2014

How to disable SharePoint 2013 App store?

After our SharePoint 2013 environment has been configured for Access Apps user requested, by default the SharePoint Store is also open for all farm administrators and anyone with Site Owner/Full Control access to a SharePoint site. However, our SharePoint team is afraid that SharePoint Store Apps could become another Fab 40 issue in the future upgrade which means that vendor may not support them to work with new SharePoint versions. Before having a governance plan in place, we plan to disable users adding any SharePoint Store App and remove any SharePoint Store App that have been added already.

From Microsoft technet articles and blogs , it seems that there is no way of keeping site owners from of browsing the SharePoint Store. The only thing we could do is to configure the SharePoint so that they must request an app from the store and only specified administrators can approve these requests. We also identified several different ways to remove SharePoint Store App that have been added to SharePoint farm. As a result, any instances of the app that have already been added to sites by users will remain, but the app will no longer be available for users to add to additional sites. Here are the details steps.

1. The first step to disable users adding any SharePoint Store App to SharePoint is to create a new app catalog site for each webapp.

Because an App Catalog is scoped to a web application, all apps that you want to make available for a web application have to be in the App Catalog site collection for that web application. you have to create different app catalog site inside each webapp to manage the Apps for that particular webapp. I would strongly recommended to create separate content DB for app catalog sites for manageability. In order to create inside specific content DB, you would need to user Powershell commands instead of CA. Here is one example to create two app catalog sites for webapp and

# Create a new content DB inside on database avalability group
New-SPContentDatabase WSS_Content_AppCatalog -DatabaseServer spsqldev2 -WebApplication
New-SPContentDatabase PROJECTS_Content_AppCatalog -DatabaseServer spsqldev2 -WebApplication

# Retrieve app catalog site template
$template = Get-SPWebTemplate “APPCATALOG#0”

# Create app catalog site in the new content DB using app catalog site template
New-SPSite -Url “” -OwnerAlias “na\spdev1” -Name "Qualcomm SharePoint App Catalog" -Template $template –ContentDatabase WSS_Content_AppCatalog
New-SPSite -Url “” -OwnerAlias “na\spdev1” -Name "Qualcomm Projects App Catalog" -Template $template –ContentDatabase PROJECTS_Content_AppCatalog

# Update catalog configuration so it can be seen from CA
Update-SPAppCatalogConfiguration -site”
Update-SPAppCatalogConfiguration -site”

2. The second step to configure SharePoint store to disallow user directly adding SharePoint store apps.  
This setting is at the web application scope. If users cannot purchase apps, they can still browse the SharePoint Store, and request an app. Farm administrators and the App Catalog site owner can view and respond to app requests.
  • Verify that the user account that is performing this procedure is a member of the Farm Administrators group. 
  • In Central Administration, on the Apps page, in the SharePoint and Office Store section, click Configure Store Settings. 
  • On the SharePoint Store Settings page, verify that the selected web application is the web application that you want to configure.
  • If you want to switch to a different web application, click the down arrow next to the web application URL to change to a different web application.
  • To allow or prevent purchases, select an option for Should end users be able to get apps from the SharePoint Store? Select No to prevent purchases but allow users to request apps.
  • To allow or prevent apps for Office from the Office Store to be started when a user opens a document in the browser, select an option for Should apps for Office from the store be able to start when documents are opened in the browser? Select No to prevent apps for Office from the Office Store from starting.
  • Click OK.  

3. The third step is to remove all the SharePoint App from the catalog so users will not able to add them as indicated from Microsoft technet.
Removal does not uninstall or remove the app from sites to which it has been added. It merely removes the app from the App Catalog, and users cannot add the app to other sites. Here are steps if you want to remove from the farm scope. 
  • In Central Administration, on the Apps page, click the Manage App Licenses 
  • For each SharePoint App Store app, select remove
  • The app is removed
If you only want to remove from certain Webapps instead of the whole farm, you could follow the following steps.
  • On the App Catalog site, click the Apps for SharePoint list.
  • On the Apps for SharePoint page, select the app that you want to remove.
  • In the ribbon, on the Files tab, click Delete Document to remove the app.
  • In the dialog box, click OK to confirm that you want to send the item to the site Recycle Bin.

Please note there is a way to remove app instance using Powershell the following powershell commands. Here is one example to remove one App named "Project Management" form one webapp.

$installedapps = Get-SPAppInstance  -web

$yourInstalledapp = $installedapps | where {$_.Title -eq ‘Project Management’}
Uninstall-SPAppInstance -Identity $yourInstalledapp 

There is potential issue you might have is the error when the app is not installed using the same account you run the powershell command. This might be a bug since this account can remove apps from SharePoint UI directly.

Uninstall-SPAppInstance : The System Account cannot perform this action.

At line:1 char:1
+ Uninstall-SPAppInstance -Identity $yourInstalledapp
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (Microsoft.Share...pInstanceCmdlet:UninstallSPAppInstanceCmdlet) [Uninstall-SPAppInstance], InvalidOperat
    + FullyQualifiedErrorId : Microsoft.SharePoint.Administration.SPAppCmdlets.UninstallSPAppInstanceCmdlet 

Now, you should have disabled the SharePoint Store to all end users. You should build the App management governance plan before you open to end users again.