Word

Exchange Online Migration Clears ‘Recent’ Document Lists from Word and Excel

I struggle to fit these issues into a short but descriptive headline sometimes :)

This issue is a little strange. If you didn’t know any better (like me), you’d expect the location of a user’s mailbox to have no impact whatsoever on the function of ‘Recent’ document history inside of Microsoft Excel and Word, but it actually does.

I found this out the hard way of course, when a couple of staff mentioned their recent lists had disappeared and it co-coincided with their Exchange on-prem to Exchange online migration.

After some digging, I came across this Reddit post: 
Users losing Recent Documents lists in Office 2016 due to upgrade to ADFS. It’s the same problem with a slightly different root cause, and goes into a much deeper technical explanation than what I’ll do here.

The short of it is that the Office applications detect what sort of login you’re using – if it’s Active Directory (AD) or Azure Active Directory (AAD). When that state changes, it uses a different registry path for a few things, including those recent documents.

Without knowing for sure but based on my testing, it must be doing some check to see if the associated account’s mailbox is in Exchange Online or not – and if not, it considers it an AD account. It doesn’t matter if you already have the users in Azure AD, Single Sign on and all that other good stuff set up – the single change of changing the mailbox location to online triggered the change for me.

For an AD account, the history paths are saved in the registry here:

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Word\User MRU\AD_1234567890 (the number on the end is some sort of unique GUID).

For an Exch account, it’s in this slightly different path:

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Word\User MRU\ADAL_1234567890 (again, unique GUID at the end).

In case you were wondering, MRU stands for ‘Most Recently Used’. AD is to do with on-prem Active Directory, and ADAL is (according to that reddit post) Azure Active Directory Authentication Library.

Also note the example above is for Word, there’s corresponding paths for other Office applications such as Excel.

There’s two subkeys below this key, one for File MRU and the other Place MRU.

The good news on hitting this scenario is that the values can just be exported, the path changed and re-imported. To do this, via regedit find the registry key that has the values you want (probably the AD one) and right click > export.

Find the file you exported and use notepad to do a find and replace on all the entires for AD_1234567890 and replace to the new value (which you can find from just looking in the registry).

Now, re-import the registry file and you’ll have all the recent document paths restored.

This should only be a one time problem for migrations, and only for people who had a bunch of document paths saved in there and can’t find where they are easily.

Automate Backup Of Word AutoRecover Files For More Recovery Options

“I’ve lost a document and can’t find it!” is a common phrase that nobody likes to hear. Most people are working in Microsoft Word for their documents, and although it has a bunch of nice features for autorecovering lost work, it doesn’t cover all scenarios.

There’s even a new feature which autosaves your work as you go; as long as the document is in SharePoint Online or OneDrive for Business.

However, it’s still easy for someone to accidentally close a document and say ‘no’ to saving changes, or other scenarios where documents get overwritten with the wrong information. A document management system (DMS) with versioning (such as SharePoint) can help, but I’ve yet to hear of a company that has 100% of their documents at all times in their DMS!

Anyway, after seeing many scenarios of lost work, I thought there might be another method I can implement to help capture lost data. Microsoft Word’s Autorecover function does work quite well, in keeping an ASD file updated at regular intervals (10 minutes by default) which are saved in C:\Users\username\AppData\Roaming\Microsoft\Word\ (by default). I changed this to 5 minutes rather than 10:

Microsoft Word Options > Save screen

Autorecover will update an ASD file in this folder for each document you have open, on the frequency configured above. That file can get closed or lost depending what the user clicks (again, closing and not saving a document is a scenario that will lose the ASD).

My idea was to back up these ASD files also on a 5 minute interval, giving another avenue to restore lost documents. Because the AutoRecover starts at a random time, a script running every 5 minutes would also start at a random time, and together there’d be a 5 to 10 minute window on copying out the backup files, which isn’t a huge amount of work to lose if someone had been working for hours.

Here’s the PowerShell script I wrote.  It first sets a few variables that can be configured, then does a cleanup of previous backups. If they’re > 2 days old, backup folders are purged or we’d have an ever growing amount of backups. The 2 day value in (Get-Date).AddDays(-2) can be changed.

Then, it runs a filecheck to make sure there’s ASF files to back up. If not, the script breaks. If files exist though, it then creates the Backup folder, creates a sub folder based on the date/time and then copies the ASD files into that folder.

The format of the folders is set at the very start of the script, and again can be changed to a different format if you prefer.

#get-date format https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/get-date?view=powershell-6
 $date = get-date -uformat %Y%m%d%H%M

$SourceDir= "C:\Users\$env:username\AppData\Roaming\Microsoft\Word\"
 $backupdir = "C:\Users\$env:username\AppData\Roaming\Microsoft\Word\Backup\"
 $targetDir = "C:\Users\$env:username\AppData\Roaming\Microsoft\Word\Backup\$date"

Get-ChildItem $backupdir -Recurse | Where CreationTime -lt (Get-Date).AddDays(-2) | Remove-Item -Force -recurse

$Filecheck = get-childitem $sourcedir -filter *.asd

If ($Filecheck -eq $null) {break}

md $targetDir

set-location -path $sourceDir

$files = Get-Childitem -File -filter *.asd

foreach ($file in $files)
 {
 $strippedFileName = $file.BaseName;
 $extension = $file.Extension;
 $sourceFilePath = $file.DirectoryName;
 $DestinationFile = $targetDir + $sourcefilepath.TrimStart($sourceDir) + "\" + $date + $strippedFileName + $extension;
 Copy-Item $file.FullName -Destination $DestinationFile -Recurse -force
 }

(note that the File copy section was taken from here). Save the above as a .PS1 script and you’re good to go.

That worked well after a lot of testing, but the next problem was getting it to run on everyone’s computer. Using a Scheduled Task means we can configure it to run however often we like and whenever we like, as well as being able to push out the task via Group Policy. However, you can’t run PowerShell scripts silently just by running a PS1 file when triggered from Scheduled Tasks.

Scheduled Task pushed out via Group Policy

There is a great workaround here which uses a VBS file to then trigger the above PS1 script. the VBS component itself runs silently which in turn runs the PS1 script silently. Here’s a copy of the script in case the link goes dead, but please read the original link for more details:

Set objShell = CreateObject("Wscript.Shell") 
Set args = Wscript.Arguments 
For Each arg In args 
 Dim PSRun
 PSRun = "powershell.exe -WindowStyle hidden -ExecutionPolicy bypass -NonInteractive -File " & arg
 objShell.Run(PSRun),0
Next

The final catch is then opening an ASD file when you want to recover something. To open a recovered file, in Word go to File > Info > Manage Document > Recover Unsaved Document (if the Info link is greyed out, open a new blank document first). If you had to navigate away from the default location it shows to open the ASD file, you will probably see this error:


Microsoft Word cannot open this file because it is an unsupported file type

As pointed out here, for some reason Word doesn’t like opening the file unless it’s in the special ‘UnsavedFiles’ location. Luckily you can just copy the ASD file into this folder (which by default is C:\Users\%username%\AppData\Local\Microsoft\Office\UnsavedFiles” ) and then open it as per the above method.

Keep in mind, both the PS1 and VBS files also need to be available to the user, which you may want to also push out by Group Policy. Just make sure the file called by the Scheduled Task exists, or the users will see an error saying the file can’t be found, every single time the script runs.

Update 16th July 2018

I’ve adjusted the script slightly for use in a terminal server environment (RDS or Citrix). The scripts are seperate – yes a master one could be created, detect if it’s a client or server, then run the appropriate parts, but I haven’t done that :)

The only real change is getting the list of logged on users from a broker. I could filter these out to only grab the users on the local box but the script runs within a second anyway and shouldn’t find or do anything if the user profile doesn’t have any backup files.

#Terminal Server Version
#get-date format https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/get-date?view=powershell-6
$date = get-date -uformat %Y%m%d%H%M

Import-Module RemoteDesktop
$users = Get-RDUserSession -connectionbroker #brokerservername

foreach ($user in $users){

$login = $user.UserName

$SourceDir= "C:\Users\$login\AppData\Roaming\Microsoft\Word\"
$backupdir = "C:\Users\$login\AppData\Roaming\Microsoft\Word\Backup\"
$targetDir = "C:\Users\$login\AppData\Roaming\Microsoft\Word\Backup\$date"

$SourceDirExcel= "C:\Users\$login\AppData\Roaming\Microsoft\Excel\"
$backupdirExcel = "C:\Users\$login\AppData\Roaming\Microsoft\Excel\Backup\"
$targetDirExcel = "C:\Users\$login\AppData\Roaming\Microsoft\Excel\Backup\$date"


Get-ChildItem $backupdir -Recurse | Where CreationTime -lt (Get-Date).AddDays(-2) | Remove-Item -Force -recurse
Get-ChildItem $backupdirExcel -Recurse | Where CreationTime -lt (Get-Date).AddDays(-2) | Remove-Item -Force -recurse

md $backupdir
md $backupdirExcel


$Filecheck = get-childitem $sourcedir -filter *.asd
$FilecheckExcel = get-childitem $sourcedirExcel -filter *.xar -Hidden

If ($Filecheck -ne $null) {

md $targetDir

set-location -path $sourceDir

$files = Get-Childitem -File "C:\Users" -recurse -filter *.asd

foreach ($file in $files)
{
$strippedFileName = $file.BaseName;
$extension = $file.Extension;
$sourceFilePath = $file.DirectoryName;
$DestinationFile = $targetDir + $sourcefilepath.TrimStart($sourceDir) + "\" + $date + $strippedFileName + $extension;
Copy-Item $file.FullName -Destination $DestinationFile -Recurse -force
}
}

If ($FilecheckExcel -ne $null) {

md $targetDirExcel

set-location -path $sourceDirExcel

$files = Get-Childitem -File -filter *.xar -hidden

foreach ($file in $files)
{
$strippedFileName = $file.BaseName;
$extension = $file.Extension;
$sourceFilePath = $file.DirectoryName;
$DestinationFile = $targetDirExcel + $sourcefilepath.TrimStart($sourceDirExcel) + "\" + $date + $strippedFileName + $extension;
Copy-Item $file.FullName -Destination $DestinationFile -Recurse -force
}
}
}

Microsoft Word – Show all formatting marks

Microsoft Word 2016 and earlier versions have a handy toggle for ‘Show/Hide paragraph marks and other hidden formatting symbols’. It’s that backwards P looking thing in the Word ribbon:

… but also choosable from Word’s options under the Display section, called ‘Show all formatting marks’:

By default, this option is off. I had a business requirement to have it on by default, which proved harder to work out than I thought.

Normally for Word and Office, a user option is controlled by the registry. Procmon is great for capturing those changes, but I couldn’t see anything when toggling this option.

Jeremy Moskowitz from PolicyPak gave me a hint on finding the solution on this one; Word sets some of it’s user settings at the time of closing Word, rather than changing an option and pressing ‘OK’.

Once I knew that, the setting was easy to find.

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Word\Options

DWORD Value - ShowAllFormatting

1 - Enabled / On

0 - Disabled / Off

Word will read this setting at startup, and write to it at shutdown based on what the setting was last set to.

Using Group Policy Preferences and setting the option ‘Apply once and do not reapply’ to push out the registry setting will make it the default, but let users change it as they please.

Mail Merge Crashes When Opening Data Source

word crash

Sharing another problem and resolution I came across.

Recently, staff started complaining about Mail Merge crashing at the point of selecting a data source use. It was easily recreatable, and caused this event viewer error:

Faulting application name: WINWORD.EXE, version: 14.0.7113.5001, time stamp: 0x52866c04
Faulting module name: mso.dll, version: 14.0.7106.5003, time stamp: 0x5231bdf1
Exception code: 0xc0000005
Fault offset: 0x00c23ab0
Faulting process id: 0xe48
Faulting application start time: 0x01d204e6d69112b6
Faulting application path: C:\Program Files (x86)\Microsoft Office\Office14\WINWORD.EXE
Faulting module path: C:\Program Files (x86)\Common Files\Microsoft Shared\office14\mso.dll
Report Id: 3bf6bbe2-70da-11e6-bd32-b8763fabbff5

Pretty standard for a crash. In our environment, we had changed from Lync 2010 to Skype for Business 2016, and installed Skype for Business through the Office 2016 installer rather than standalone, to make future Office product updates easier (Skype for Business standalone won’t co-exist with an Office 2016 suite install).

For some reason, this upgrade process has broken the mail merge function for Microsoft Word. The quick fix was to do a repair of the Office 2010 suite after the Office 2016 install, and mail merge worked again.

It’s worth noting that a computer that had Office 2010 suite and Office 2016 (Skype for Business only) worked fine, it was only if Lync 2010 was installed first and then removed, then Office 2016 installed.

Excel and Word Macros Broken with Windows Update

A problem popped up recently where an Excel Macro file wasn’t working – there was a button to run the macro, but the button wouldn’t even click. This is despite all the security settings being their lowest – e.g. Enable all macros (not recommended; potentially dangerous code can run).

A friend pointed me in the right direction for this one, and the cuprit was Windows Update KB2553154 which I don’t think has actually been pulled yet (although InfoWorld reports others have). The patch is designed to fix a vulnerability.

There’s a great post on StackOverflow about this, along with a fix from user John W  that I can confirm works:

From other forums, I have learned that it is due to the MS Update and that a good fix is to simply delete the file MSForms.exd from any Temp subfolder in the user’s profile. For instance:

C:\Users\[user.name]\AppData\Local\Temp\Excel8.0\MSForms.exd

C:\Users\[user.name]\AppData\Local\Temp\VBE\MSForms.exd

C:\Users\[user.name]\AppData\Local\Temp\Word8.0\MSForms.exd

Of course the application (Excel, Word…) must be closed in order to delete this file.

I actually just deleted everything in the Temp folder. The user didn’t need to log off or anything, just opened up the Excel Macro template and it instantly worked.

You could use group policy preferences to delete these .exd files if you don’t want to manually remove it, but hopefully you don’t have too many people in your company affected by this. Otherwise, it might be a good idea to hold off on 2553154 as MS may release a hotfix or re-patch the patch.

Updated: Affects Word also.