Office

Office Support and Recovery Assistant Tool

I was just made aware of this useful tool by Microsoft Support – the Microsoft Support and Recovery Assistant for Office 365 (also known as ‘SaRA’).

Even better, it’s not just for Office 365, other Office products can be scanned using this tool such as Outlook in Office 2010, 2013 and 2016.

The article above has a step by step guide for scanning Outlook for problems. It takes a few minutes to run, but will identify a bunch of possible issues you may have. But, from the results I see, I’d say everyone should run this tool regardless!

For example, my scan came up with this as one of the issues found:

The link goes here which then goes into details about the problem. I had noticed in Outlook 2016 by default, that users had sometimes mentioned they could no longer delete items from mailboxes they only had Inbox access to, and I assumed this was a change in behavior from Outlook 2010. This tells you how to toggle that setting if you’d rather the deleted items go to the other person’s mailbox, which removes the need for the delegate to have access to someone else’s deleted items.

If I’d run this at the start of the Office 2016 deployment during testing, it would have given me a better idea of potential issues that might come up. Here’s another one:

That’s not ideal at all! Again the link goes into more detail and this one seems really important –

Since it was patched in 2010 and 2013, but 2016 needs a registry change to fix it (why would they not just change the registry value in 2016 with an update?). This is something that may never get picked up without running this utility.
I’ve now got some work ahead of me to go through the rest of the issues from my scan, do testing and hopefully improve things. I’ve only looked at the Outlook component so far, and there’s other scans I’ll also need to try. Check it out and hopefully it’ll help you too.

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
}
}
}

Become an Office Insider

Similar to the Windows Insiders program, you can also be an Office Insider.

The programs have the same ideas – give users access to new features before everyone else, and let those users provide feedback to help report issues or shape decisions that will go out to the rest of the world.

This program is for the Click To Run version of Office, not MSI.

If you’re not already a Windows Insider, Microsoft has easy to follow instructions. It’s also not a requirement to be a Windows Insider to be an Office Insider.

For Office Insiders, it depends what version of Office you’re licensed to. Home, Personal and University licenses can just go to File > Account > Office Insider from any Office app, and follow the prompts.

However if you’re using a School or Work account, you won’t see this option. The full instructions are available from Microsoft but here’s the condensed version:

Download Office 2016 Deployment Tool and run it. It will extract a setup.exe and configuration.xml file.

Edit the configuration.xml file: The line -<Add Channel=”Monthly” OfficeClientEdition=”32″> needs the word ‘Monthly‘ changed to either ‘InsiderFast’ to get updates as early in the process as possible.

Open an admin commant prompt, navigate to the folder that contains the two above files and run:

Setup.exe /configure configuration.xml

(If you have any issues, try uninstalling your existing version of Office).

Once that’s done, you should be good to go. Launch an Office app such as Word, log in with your Work/School account, and go to File > Account. Under the About Word section, you should see a mention of Office Insider:

If you want to be an Office Insider for apps on iOS or Android, then follow the instructions here on how to register and obtain updates (it’s very easy!).

Excel – Something Went Wrong While Downloading Your Template

Excel 2013 and 2016 have a great inbuilt feature of having online pre-built templates available for different purposes. You find them by going to File > New. Templates such as Family Budgets or Back to School Planners. They’re hosted by Microsoft and download the template as you need them:

List of Excel 2016 Templates

Normally you’d pick the template you want, and use the create option:

Creating an Excel 2016 Template

However, there’s a scenario I found that this doesn’t work, and you’ll see the message ‘Something went wrong while downloading your template’:

Something went wrong

After digging around for a bit, I found this Technet thread which mentioned uninstalling Visio Viewer to fix it. Seems strange, but I tried this and it worked. I wasn’t happy with that as a solution though, so logged a Microsoft case.

I went through the process of capturing fiddler traffic and logs, but was then asked a simple question: Was Visio Viewer 32 or 64 bit? I had a look and it was 64 bit, however the Office 2016 suite itself was 32 bit. I quickly guessed that 32 and 64 bit wasn’t a good mix for Office products, even if they were installed separately.

Sure enough, using Visio Viewer 32 bit with Excel 2016 32 bit fixed the problem.

 

TL;DR – Visio Viewer needs to match your Office/Excel install – 32 bit or 64 bit for both.

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.