If Word randomly crashes when opening SharePoint documents

I’ve been working on a PowerShell script that automates Microsoft Word to open all files in a SharePoint library, modify the header and other properties and then save it again. I ran the script against 4000 documents and everything went fine. But then I tried running it again (against the same files). Now Word began crashing randomly when opening files, with an error message The remote procedure call failed. (Exception from HRESULT: 0x800706BE) . The same could happen if I manually opened documents from SharePoint. Word just died silently.

Turns out it was a problem with the cache. After clearing and disabling the cache in Word, my script magically started working again! Edit you saving options:You may also want to clear you %TEMP% directory, just to be sure.

Posted in Development, Tips | Tagged , , , , , , , , | Leave a comment

Excluding page templates in CAML queries

Typically, when requesting SharePoint pages we do not want to include templates, just normal pages. When saving a SharePoint page as a template, this is noted in a property called OData__SPSitePageFlags . With this information we can easily exclude templates by adding the following to the CAML query:

 

Posted in Development, Tips | Tagged , , , | Leave a comment

Open a SharePoint list in classic experience if link is missing

The modern list experience in SharePoint is the default by now. But you can still revert to classic experience by clicking the link under the left navigation. But on communication sites the left nav is turned off by design. If you need to go back to the classic experience you can run this in the browser console:

Use as a bookmarklet for easy access:

My experience is that you need to click the left nav link twice – and thus also run above code snippet twice – to get it to work.

Posted in Tips | Tagged , , , , , | Leave a comment

Where is “Tenant Wide Extensions” list when using Site Collection App Catalogs?

A fairly new feature in SharePoint Online is the ability to have Site Collection App Catalogs. Neat. Even “Tenant Wide” application customizers work (though they are of course scoped to only the site collection).

Creating a Site Collection App Catalog is simple; just a few lines of PowerShell. After that you get the “Apps for SharePoint” list in your Site Content. But the Tenant App Catalog has a companion list called “Tenant Wide Extensions”. Don’t we get that here too?

Turns out the list is there – it is just hidden. Simply browse to “/Lists/TenantWideExtensions”   to find it!

Posted in Development, Tips | Tagged , | Leave a comment

What if there were no ad blockers?

Sometimes I browse the web without an ad blocker. It’s typically a horrible experience. I don’t understand how people stand it.

I don’t watch TV any more, and a big part of this is that I can’t stand the ads. But TV ads are predictable and can be avoided by changing the channel or muting the sound and go out to the kitchen to grab a beer. Web ads are much more in your face.

I wonder what my life would be if there were no ad blockers. I admit that I spend a good amount of time procrastination at different sites. But if I had been forced to watch their ads, I’m not sure I would any more.

Perhaps, in a world without ad blockers, I would be a more productive person due to the lack of distractions. And being productive is a great feeling. Perhaps a world without ad blockers would not be that bad after all…

Posted in Opinion & Thoughts | Tagged , , , | 3 Comments

Maximizing Hyper-V Windows (instead of full screen)

I recently started using Hyper-V after being used to VMware. I want my virtual machine windows maximized, but not full screen since I need to quickly move between the host and virtual machines. In VMware the maximize button will do what it is intended for – maximizing the window – but Hyper-V will instead enter fullscreen mode when you maximize the window. Very annoying. It took me quite a while until I found a way around this:

Use the mouse to drag the window to the top of the screen. It will “snap” into place maximizing the window!

Note: This works only in “enhanced sessions”

Posted in Tips | Tagged , , , , | 3 Comments

Transparent CSS borders with the inherited color

You can inherit CSS colors from parent elements using the  inherit  or  currentColor  keywords:

This works for border color too:

But suppose you’d want to have a semi-transparent color. When setting colors normally you would simply use  rgba()  to specify the transparency:

But this does not work for inherit or currentColor since rgba does not accept these keywords. But with a little trickery we can use the  :before  pseudoelement to get the same effect:

Cool :-)

Posted in Development, Tips | Tagged , | 1 Comment

MS Teams fullscreen view?

Microsoft Teams does not have a “true” fullscreen mode. Even if you select both “focus” and “fullscreen” there is always a command bar left at the top. This can make it difficult read small text when people are sharing their (high resolution) screens. Here are two tips how to improve:

1. Pressing Ctrl+Minus will make the Teams GUI smaller. Do this multiple time. Each time the shared video gets more room! (You can always press Ctrl+Equals to go back to 100% zoom.)

2. While hovering the shared content, hold Ctrl and use the mouse wheel to zoom in and out. Only the shared screen is affected. When zoomed in, use the mouse to drag the content around.

3. Use the web version of Teams instead. This actually has a *real* fullscreen mode. (It’s not a problem to have both the web version and the ordinary desktop client running at the same time!)

As a side note: From time to time I’ve had problems with viewing screen shares in Teams. The framerate of the incoming video is extremely bad and drops to something like one frame per *minute*. In these cases I have had better result using the web version instead of the ordinary client. Still not super fluid, but at least good enough so you can follow the conversion.

Posted in Tips | Tagged , | 2 Comments

Hacking DocX files to fix Quick Parts in Word Online

Microsoft Word documents can have Quick Parts connected to SharePoint fields, so that the value of the field is displayed inside the document. But Word Online (still) does not fully support this. It will display Quick Parts with a a placeholder text instead of the actual value. If the SharePoint list value changes, the document must be opened in the desktop client for the placeholder text to update and show correctly in Word Online.

This caused trouble for me when I was migrating thousands of files into SharePoint Online. I had a script that uploaded documents and tagged them with important metadata. This metadata was supposed to be displayed in the document, but in Word Online the field name was shown instead (as a placeholder). This was not acceptable, especially since Word Online is default when opening a Word document in SharePoint. I tried to use the Word Automation COM API to make a script that updates these values, but it does not allow editing these fields. What to do?

Here I present a solution to update the placeholder values from PowerShell. It works by unzipping the DocX files to directly modify the XML files. Hacky, but it works :-)

This is not a generic script. It was written for my own needs. Modify and adapt the script to suit your needs before running. For example, it does not show how to get the field values to replace the placeholders with. In my case I had a separate CSV file with migration data. Below I just use a simple key/value structure to map placeholders (e.g. [Author]) to a value. A colleague told me he actually found the “real” values elsewhere in the DocX file. I have not verified this, but perhaps it is possible to read these values instead.

Note: Due to a bug in Compress-Archive make sure to first update it or else Word Online will think the documents are corrupt and only open them in view mode: Install-Module Microsoft.PowerShell.Archive -MinimumVersion 1.2.3.0 -Repository PSGallery -Force -AllowClobber

Update: Forgot to use HtmlEncode() to avoid corrupting the XML

Posted in Development, Tips | Tagged , , , , , , | Leave a comment

Migrating data from Access to SQL Server

Suppose that you have a simple Microsoft Access database that you need to migrate to another database such as MS SQL Server. You model the tables and relationships in the new database, and now you need to export the actual data from Access and import it into the new database. This is how I did it:

I began by exporting the data as CSV. I found the easiest way was to simply use copy/paste. First though, open each tables in Design View. There is a tab named “Lookup” at the bottom. Ensure that any lookup field is set to display “Value List” instead of “Table/Query”. Then you can view the table normally and copy all data to the clipboard (Ctrl+A, Ctrl+C) and paste it into a .csv text file saved with UTF-8 encoding.

Next I used PowerShell to transform the CSV data into to SQL statement. While doing this I also adapted the structure of the data. For example, some fields were renamed, and I also had multiple fields that I wanted to merge into one. Here is a generic version of the script I put together that you can modify to your own needs:

Note: Normally you let SQL Server generate the ID of key fields. In this case though, we already have ID:s from the Access database. Since these ID:s specify the primary-foreign key relations, it is important to keep intact. To allow insertion of keys like that, IDs SET IDENTITY_INSERT Chemicals ON  is used.

Finally, open the generated SQL script (provisioning.sql) in SQL Server Management Studio (or similar) and run it to provision your data!

Posted in Development, Tutorials | Tagged , , , , , , | 1 Comment