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).
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.
Run Power BI Desktop as administrator before opening the report.
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=126.96.36.199, 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.
So I checked that table in the query editor and received yet again the same error.
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.
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.
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.
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:
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:
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.
Recently I was helping a client visualize their excel data using Power BI and ran across an error in Power BI when it couldn’t deal with “#DIV/0!” as a cell value in Excel:
“DataFormat.Error: Invalid cell value ‘#DIV/0!’.”
Excel error #DIV/0!
Power BI error when trying to read that excel file: “DataFormat.Error: Invalid cell value ‘#DIV/O!’.”
You get this error in excel whenever you have a calculated formula that is trying to divide by zero or a blank cell. Similarly, you get “DataFormat.Error: Invalid cell value ‘#VALUE!'” typically when your data formats don’t match – like there’s a word in a number-formatted column.
It’s a simple fix: just modify your Excel formula to be wrapped in =IFERROR((YourFormula),0) so that if your formula attempts to divide a value by zero, it returns a value of zero instead of an error code.
A client of mine recently ran into an issue I hadn’t seen before. When she would click a formula cell and drag down to calculate it across multiple rows, it only copied the value. The formulas were correct, but the value being shown was from the original cell: