Excel

PowerShell – Getting M365 Tenant ID From Domain List

Posted on by Adam Fowler

It’s been a while since I’ve broken out PowerShell to solve a problem, but a scenario came up where I thought I could automate something I needed to do – look up a bunch of Microsoft 365 Tenant IDs based on domain names. Here’s how I tackled it:

First, I actually had a list of email addresses and just wanted the domain of each one. The list was in Excel so that’s easy enough – using the Text to Columns feature I selected the data, used the ‘Delimited’ option under Original data type then pressed Next:

Then on the next step, changed the Delimiters from the default ‘Tab’ to ‘Other’ and put the @ symbol on, and as you can see in the Data preview it takes the alias off the email address for the first column, and leaves the domain in the second:

Clicking ‘Finish’ gave me a column full of domains. From this, I created a header for each row (alias and domain):

And then in Excel went to File > Save As > and called the file ‘addresses’ while picking CSV from the dropdown:

Easy enough. From here, I knew I’d need to feed this data into PowerShell using the Import-CSV command, but first I wanted to work out what the one liner command was to get a M365 Tenant ID…. except I couldn’t find one. All the examples were how to find your own M365 Tenant ID after authenticating. I knew it was public and easily accessible since sites like https://whatismytenantid.com/ work great but only accept one domain at a time.

I ended up finding a Function written by Daniel Bradley which was fairly simple and using an API, with the core of it being this one line:

Invoke-RestMethod -UseBasicParsing -Uri "https://odc.officeapps.live.com/odc/v2.1/federationprovider?domain=$domain"

Swapping the $domain variable with an actual domain and piping to just selecting tenantid

Invoke-RestMethod -UseBasicParsing -Uri "https://odc.officeapps.live.com/odc/v2.1/federationprovider?domain=microsoft.com" | select tenantid

tenantId
--------
72f988bf-86f1-41af-91ab-2d7cd011db47

Alright, we should be able to put this all together. Set the $file variable as the imported CSV file, then for each domain record run the Invoke-RestMethod command using the current $record.

Except that didn’t work because I forgot the $record is the entire object and not just the domain membertype. To specify that, we just use $record.domain so the pure domain is used.

Except that didn’t work either and I don’t know why. Instead, I just made a new variable from the $record.domain and called that $newdomain, then referenced THAT in the Invoke-Restmethod line.

That did work, so I could then echo out the results of both the current $newdomain variable, and the newly looked up $result and again specifying the membertype of tenantid (as a bunch of other info gets looked up with that command).

I also then wanted to export this data back out to a new CSV, in this case one called ‘myfile.csv’. Again, I have to work around membertypes so just make a new variable containing the single tenantid line, and use the >> operator to create/append to a file:

$file = import-csv c:\temp\addresses.csv

foreach ($record in $file){
        $newdomain = $record.domain
    $result = Invoke-RestMethod -UseBasicParsing -Uri "https://odc.officeapps.live.com/odc/v2.1/federationprovider?domain=$newdomain"
$newtenantid= $result.tenantid
echo $newdomain $result.tenantid
"$newdomain,$newtenantid" >> c:\temp\myfile.csv
}

Works perfectly and I end up with a CSV that has a column of domains, and a column of Tenant IDs. If a domain had no Tenant ID then that value will be blank.

I’m sure this could be written better, but for quick occasional tasks for yourself, you just need something that works.

Excel – Something Went Wrong While Downloading Your Template

Posted on by Adam Fowler

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.

Mail Merge Crashes When Opening Data Source

Posted on by Adam Fowler
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.

Adding A Space Into Excel Cells

Posted on by Adam Fowler

This took much more digging than I thought to find the answer so here it is:

Say you have a field in Excel with a value such as “123456”, but want to display it with a space in the middle – “123 456″… how do you add the space?

You can just add it in manually if the cell type allows it, but for a bunch of data, that’s a very time consuming solution.

There’s probably a bunch of ways it can be achieved, but here’s the easy way I eventually found:

if A1 contains “123456” put this into A2:

=TEXT(A1,”### ###”)

B2 will read “123 456”!

If you have a leading zero in your fields, it will drop the zero. For that, you’ll have to do this:

if A1 contains “012345” put this into A2:

=Text(A1,”0## ###”)

Pretty simple, the hash passes on each character from the referring field, and you can modify what happens between each passed character.

If you want to clean it up, then copy your results, and paste special > results. That will drop the code, and just have your newly formatted results.

Excel and Word Macros Broken with Windows Update

Posted on by Adam Fowler

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.