Solution: “Feature requires a browser that supports ActiveX controls” when importing spreadsheet to SharePoint

If you’re trying to import a spreadsheet as a new list in SharePoint, you’ll need to use a browser that supports ActiveX controls or you’ll get the error:

“This feature requires a browser that supports ActiveX controls.”

Chances are you’re running a browser other than IE, or you’re running IE version 11. ActiveX controls are not supported in IE11, or most browsers. We can work around this error message by having IE pretend it’s version 10 momentarily.

  1. Open SharePoint in Internet Explorer (IE)
    • Quick access: Hit Windows key, type IE, hit enter
  2. Press F12 to open Developer Tools and select the Emulation Tab
  3. Change Document mode from 11 to 10 (supports ActiveX). Page will reload so you can try again in version 10.

Now try to import the spreadsheet again and it will work fine, opening your spreadsheet and prompting you to select the table or relevant data for import.

Preserve Excel hyperlinks when saving as PDF

If you have hyperlinks in your excel files and need to save your file as a PDF, you’ve probably run into the error in which your hyperlinks in the PDF output are inactive.

To be able to maintain hyperlinks you will need Adobe Acrobat. If you don’t have it, please skip down to the bottom of this post to the “Don’t have Adobe Acrobat?” section.

If you do have Adobe, it’s quite simple unless you’re using the HYPERLINK() formula (see below). Just use the Acrobat add-in to save as Adobe PDF.

File –> Save as Adobe PDF –> Convert to PDF. You can also use the Acrobat tab in the ribbon and click “Create PDF”.

Using HYPERLINK() formula

If you’re using the hyperlink formula, as seen below, we’ll need to do some manipulation to our sheet first. Printing to PDF will require that your hyperlinks are properly written before conversion (http… or https…).

To get just the hyperlinks from our formula, we can copy values from Excel and paste into Word, then copy from Word and paste back into Excel (keeping source formatting).

Paste into blank Word document, then copy all values (not the whole table, just the column)
Back in Excel, select the original column values, and paste the new values copied from Word in their place

Now when you save as Adobe PDF, your links will remain active:

Don’t have Adobe Acrobat?

Save the Excel Sheet as a web page and links will work. This isn’t ideal but in a pinch will save you some stress.

Power BI refresh error “could not load file or assembly…provided impersonation level is invalid”

Short version

Run Power BI Desktop as administrator before opening the report.

Explanation/Scenario

Recently, when opening a report from Power BI Report Server that used a SQL database as a data source I received the following error.

An error happened while reading data from the provider: ‘Could not load file or assembly ‘System.EnterpriseServices, Version=4.0.0.0, Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0X80070542)’

So I clicked “Edit Queries” to see what was going on and received almost the same error but referencing a specific table and getting me thinking about SQL specifically.

Error seen after clicking “Edit Queries”

So I checked that table in the query editor and received yet again the same error.

Error seen after clicking the specific table referenced in the previous error message

Simple solution. It’s likely that your credentials and queries are just fine.

The solution for my particular issue here, and the meaning behind the “impersonation level” part of the error, is just that you need to run Power BI Desktop as administrator before accessing the report. After opening the report in Power BI Desktop being ran as an administrator, everything worked as expected.

“Unable to connect” error when accessing SQL database in Power BI Desktop

When attempting to access a SQL server/database in Power BI, you might get the following error message:

Unable to connect

We encountered an error while trying to connect.

Details: “An error happened while reading data from the provider: ‘Could not load file or assembly ‘System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=…..

Error message received when trying to get a SQL database as a data source in Power BI

Solution

You just need to run Power BI as administrator before attempting to access the SQL database as a data source.

  1. Save your work and close Power BI Desktop
  2. Right click Power BI Desktop and select “Run as administrator”

3. Attempt to access your database again – you shouldn’t have an issue.

“Sync” button greyed out on SharePoint library

If the “Sync” option is not available for a library on SharePoint, it could be a couple different things.

Note: Media and Form/InfoPath Libraries will not sync but you can still open them in Windows Explorer and pin to Quick Access so they will function as a mapped drive would.

Sync button is available in one library but no another on the same site

Go to the library for which the sync button is disabled

Go to library settings

Click “Advanced Settings”

Make sure “Offline Client Availability” is set to “Yes”

Sync button is not available on ANY library on a site

Go to Site Settings and select “Search and offline availability” from the “Search” section

Make sure “Offline Client Availability” is set to “Yes”

Content and script editor web parts missing in SharePoint

Missing two of your favorite web parts? Don’t fret.

Make sure the site collection feature “SharePoint Server Publishing Infrastructure” is activated

Make sure the site feature “SharePoint Server Publishing” is activated

Still not there? Check “Settings” in the admin center to see if custom scripts are allowed on personal and self-service created sites (allow both). This can take up to 24 hours to take effect.

If you can’t wait (and you’re using SharePoint Online), use PowerShell to make the change immediately, changing my site details to your own

$adminUPN="nate@sharepointlibrarian.com"
$orgName="sharepointlibrarian"
$UserCredential = Get-Credential -UserName $adminUPN -Message "Enter password"
Connect-SPOService -URL https://sharepointlibrarian-admin.sharepoint.com -Credential $userCredential
Set-SPOsite https://sharepointlibrarian.sharepoint.com -DenyAddAndCustomizePages 0

Repeat the last line for each site on which you need to quickly activate this setting. For example, I would run it again on https://sharepointlibrarian1.sharepoint.com/sites/AnotherSite

Your web parts should have now returned! Good luck.

“We’ve run into a problem with your Office 365 subscription” solution

I recently moved from one tenant to another and even though I’d signed out of my client Office applications and signed in with my new tenant, I was still getting this error message when opening any Office app.

“We’ve run into a problem with your Office 365 subscription, and we need your help to fix it.”

“Go to My Account” wasn’t an option, as that account no longer existed. “Remind Me Later” only delays the inevitable temper tantrum you know must be coming.

Thankfully, there were two great posts out there that helped me solve this by removing the product keys associated with the de-activated license via command prompt. Just two notes before you begin:

  1. Make sure you run PS/Command prompt as administrator (right-click command prompt, run as administrator).
  2. Your path to the OSPP.VBS file might be different than what’s in their posts. For example, mine was actually located here: C:\Program Files\Microsoft Office\Office16\OSPP.VBS

Microsoft Surface Pro 4 “plugged in, not charging”

Alas, I reached a day where I thought I was done with my Surface. It powered on when plugged in just fine but was at 0% battery and would, of course, instantly shut off when disconnected from AC power. When I hovered over the battery indicator in the task bar, it told me it was “plugged in, not charging.” The nerve!

After some troubleshooting and trying:

  • Making sure devices, particularly under “Batteries”, were all updated
  • Uninstalling and reinstalling power related devices via Device manager
  • Running anti-virus checks
  • Installing latest Windows updates

I found out that if I plugged my surface dock into the surface “upside down” it worked fine and charged as usual. So, long story short, if your Surface isn’t charging just try connecting the power source in the opposite direction.

Also, once it “remembers how to charge itself,” you can reverse this and put the charger in the normal direction (just double-check that it still says charging). Think of it as a quick “refresh” of the surface power connector.

SharePoint calendar all-day events showing as previous day in content search web parts

Despite your regional settings being correct, all-day events for some reason are using UTC time when they’re stored and are likely showing as the wrong day in content search web parts and similar web parts.

Though they look correct as an individual item or on a calendar, the way they’re stored doesn’t acknowledge your regional settings and, when pulled through a search web part, render in UTC as beginning 6 (or other) hours earlier than they actually do.

For example, I have a content search web part that pulls all events in our organization and show’s “today’s events.” If there’s an all day event, it shows as starting 6:00 PM the day prior to its actual day.

I could not find a straightforward solution to fix all affected events. And my solution is not ideal, but it accomplishes a need. You could instead explore the possibility of creating a calculated column that adds hours to fix the alignment. But please share if you’ve encountered the same issue and have resolved it a better way.

To get around this issue, I created a SharePoint Designer 2010 Workflow on calendars that:

  • Triggers on creation or modification
  • “Unchecks” the All Day Event box
  • Sets a specific start time on the start date (I chose 7:00AM)
  • Sets End Time to the original end time (if you don’t do this, it sets end time to blank).

Solution: “This web site has been configured to disallow page editing with SharePoint Designer.”

There are three possible causes I’m aware of that you should check if you receive this message:

  • Central admin settings not configured properly
  • Site collection settings not configured properly
  • You’re using a Project Web App (PWA) site template and can only fix this on SharePoint Server

I’ll cover the solutions for each in the same order:

Central admin

Go to central admin –> manage web applications

Select the web app on which you received the error and select “SharePoint Designer” from the general settings drop-down. Make sure the first box is checked and click “OK.”

Site collection settings

Go to site settings –> SharePoint Designer Settings (under Site Collection Administration)

Make sure “Enable SharePoint Designer” is checked and click OK

Project web app template issue

Log in to a SharePoint server and go to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\TEMPLATE\SiteTemplates\PWA\XML

Open the ONET XML file in a text editor like NotePad

Search for “webdesign” (Ctrl+F to open search) and delete the following property:

DisableWebDesignFeatures="wdfeditpages"

Perform an IIS reset (run SharePoint management shell as administrator)

You may need to repeat these steps on multiple servers if you have multiple web front end servers. You can just copy the ONET file and overwrite the same file on the other servers in the same location. Don’t forget to do an IIS reset afterward on each.

Close and re-open your Project Web App site in SharePoint Designer and you should now be able to edit as you do with other sites.