Office Development – Minimal Code in practice

MinimalCode

Returning on my last ‘minimal code’ blog post, I promised to get back on this to see how this works in practice. One of my question marks I added to the post was that I wondered why the Yeoman boilerplate was using run instead of RequestContext where run passed a context parameter as opposed to the RequestContext where actively a context is requested before accessing the Word object model.

I think I found an answer to that, but will save this to a later date as I first want to make sure the minimal code I provided would work and I found some issues in the original posted code that didn’t seem to be correct.

Here is the updated code that should work in real life:

(() => {

   async function MinimalWordmethod() {

      // Create the client request context. You’ll do this for al Word add-ins.

      var ctx = new Word.RequestContext();

      // Do your things here, such as ..

      var range = ctx.document.getSelection();

      range.insertText("Test MinimalWordMethod", "After");

      await ctx.sync();

   }

   MinimalWordmethod();

})();

How to get the minimal code running

If you go back to the Yeoman project instructions in this post (and fixed the certificate issues in post Office Development – Fixing Trusted Authority – Word Client and https://maartenvanstam.wordpress.com/2017/08/07/office-development-fixing-certificate-issue-with-openssl-certificate/) you should already have a working Word Add-in.

Open the project folder (or if you already deleted it quickly create a new one following my instructions from my earlier blogposts) and open the file app.ts in the src source folder.

Replace all the code from this file with the code above, save it and start your Add-in ‘server’ by running npm start in the root of your project.

Opening Word, with the installed Add-in (again, if you already dropped it follow my other blogposts earlier this month to install it) will show you the Add-in button:

addin7

By clicking the button the Taskpane is showing, just like we’ve seen before as we haven’t changed the Taskpane ‘web’ code, but now you should see the words “Test MinimalWordMethod” arriving in your document:

MinimalCode2

As you can see the code worked! Here is how:

MinimalWordmethod();

This line is automatically triggered when the code is running on startup of the Add-in. By doing so inside the MinimalWordmethod a context is requested in this line:

var ctx = new Word.RequestContext();

If the context is returned correctly you now can access the Word object model to access parts of the document. In this case we access the selected Range object and insert the text right after the Range:

var range = ctx.document.getSelection();
range.insertText("Test MinimalWordMethod", "After");

Instead of having this executed immediately like we’ve seen in VBA or VSTO, the text is only inserted after calling the sync method on the context. In the JavaScript APIs for Office calls to Office are handled asynchronously and to work with that the await and async keywords can be found in the code.

Very simple and basic code, but there are some drawbacks you need to keep in mind. For instance, because things are async, how do you know what the right order is how things are processed. And what about batch processing of boatloads of instructions? Will this all be handled correctly, and what if there are dependencies between the batched instructions? All of these questions arise when looking at this little piece of code, and we need to address all of that in later posts.

To be continued … It’s a never ending story Smile

Office Development – Installing Script Lab

Yesterday in my blog a short introduction to Script Lab, the Microsoft Garage project to work in some sort of a playground to develop your JavaScript for Office scripts so you can test-drive your code that you plan to use in your Add-in.

Today I will go over the installation process of Script Lab. Step by step I’ll follow the instructions and see what this looks like. How hard can it be ‘ey?

To start installing navigate to https://aka.ms/getscriptlab. It looks like this:

clip_image002

Click the Add button to arrive at the guided installation page:

clip_image004

To stay in the mood, we select Word again. I’ll will be using other clients like Excel or PowerPoint soon, but to be consistent I’ll select Word again. This will provide you with a comparable environment and maybe you get used to it. Now, to open in Word click the “Open in Word” option. This will ‘warn’ you that you are about to switch applications:

clip_image006

As this is the plan, you want to move into Word instead of staying in Microsoft Edge. Select ‘Yes’ to go on. This will launch Microsoft Word 2016 (assuming you have Office 2016 installed):

clip_image008

A document is opened, but not without warning. Because you open the document from the dark and dangerous Internet you are provided with a warning and start in the Protected View of Office:

clip_image010

In this case we tend to trust Microsoft in this we continue our journey by pressing “Enable Editing” but now we run into a second warning because the document also contained a Task pane that wants to run the Script Lab Add-in.

clip_image012

Again to make you feel safe, you don’t want any add-in to kick in without your confirmation, you get the option to “Trust this add-in” or “See Details”.

clip_image014

If you click “See Details” they bring you back to base. The installer page is provided where you can read information about the add-in. I guess you already did that, otherwise you wouldn’t get this far in the installation process (unless you just trusted my simple introduction and went ahead ). For now, not all that interesting, so we close this and leave it for what it is.

clip_image016

We go ahead, and decide to trust this add-in. This will bring us the Script Lab tab:

clip_image018

Showing you a balloon with the text “Code, Run and share your Add-in snippets directly from Office” and a “Got It” button to confirm:

clip_image020

Click the Script Lab and you’ll see a ribbon appear with the options:

Code, Run, Tutorial, Help, Reference Docs and Ask the Community

clip_image022

The most obvious thing to do would probably be to go through the Tutorial, at least that was what I picked as a starting point. The interesting thing is however that the Tutorial brings you to an Excel environment to explain you what you can do with the Add-in:

clip_image024

You basically go through all the same steps again, but ending up in Excel showing you all the same options. Open a script-lab-tutorial.xlsx from script-lab.azureedge.net :

clip_image026

This is an Excel starter document with more information about the Add-in and guiding you through the options:

clip_image028

Again the little Protected View warning and the Enable Editing button.

clip_image030

It didn’t provide me with the Script Lab Add-in tab though, only the instructions that you also saw when running the Word installer, except now for Excel.

clip_image032

This wasn’t really what I was looking for, probably useful if I just started from Excel but I didn’t. I went through the GitHub issues and noticed that getting a Word Tutorial is still in planning. Some patience here.

For now, at least the Word Script Lab Add-in is installed and ready to use.

To be continued

Office Development – Fixing Certificate Issue with OpenSSL Certificate

clip_image002

Yesterday we’ve developed our first Add-in for Word, that is – the web ‘site’ that represents the Add-in. We did however run into an issue where the certificate for the website wasn’t recognized due to the different naming on the certificate. Also, the certificate wasn’t trusted so even if we had a correct named certificate we needed to add it to the trusted root certificates.

Let’s fix the naming issue first. To do so we need to create a new certificate with the right name on it. There are a couple of options to do this. For now, I will be using OpenSSL to create this new certificate.

To do so you’ll need to install some tooling to support OpenSSL on your Windows machine. You can download it here:

https://slproweb.com/products/Win32OpenSSL.html

From the downloads, I installed the Win64 OpenSSL v1.1.0f version. By the time you read this it may have been updated to a newer version. Just check the list of downloads to see what the current version is.

Installing Win64 OpenSSL v1.1.0f

clip_image004

Installing is fairly trivial, just follow the instructions, click Next

clip_image006

Accept the agreement (or don’t but you won’t get the software then ) and click Next again.

clip_image008

Select the path where you want to install the software. Initially it opts to install it in the root of the C:\ drive, but I prefer not to put it in the root of my drive so I’ve changed this to put it in my data folder. Click Next.

clip_image010

Accept the default and click Next.

clip_image012

Again, I prefer not to install directly into my system directory so I changed the default to “The OpenSSL binaries (/bin) directory. Click Next.

clip_image014

Click Install to install the software

clip_image016

Optionally you are asked to support the Windows OpenSSL software by donating an amount of money. Feel free to what you think it is worth and click Finish.

Now you can find the installed software in the location specified in step 3 of the installation process.

To create the certificate in your Word Add-in projects you need to create a subfolder in the root of your Add-in project:

Create a .\certs folder in the project

Copy openssl.cnf from (in my case) C:\Data\Projects\Software\OpenSSL-Win64\bin\cnf into the certs folder you just created in the previous step

Open the .\certs\openssl.cnf file and add the following to the end of the file:

[ SAN ]

subjectAltName=DNS:localhost

Open a command window and run the command from the .\certs folder:

C:\Data\Projects\Software\OpenSSL-Win64\bin\openssl genrsa -des3 -out server.key 2048

This will generate the private key. In this process, you need to enter a password twice:

clip_image018

Next you will be generating the certificate request:

C:\Data\Projects\Software\OpenSSL-Win64\bin\openssl req -new -sha256 -key server.key -out server.csr -subj /CN=localhost -reqexts SAN -config openssl.cnf

You will need to enter the previously inserted password again here.

clip_image020

Now generate the certificate based on the request

C:\Data\Projects\Software\OpenSSL-Win64\bin\openssl x509 -req -days 3650 -in server.csr -signkey server.key -out server.crt -extensions SAN -extfile openssl.cnf

clip_image022

Again, enter pass phrase …

Now copy the password protected server key:

copy server.key server.key.copy

Create an unprotected private key:

C:\Data\Projects\Software\OpenSSL-Win64\bin\openssl rsa -in server.key.copy -out server.key

At this time you’ll have an RSA key with the necessary subject and subjectAltName. Let’s put this into our project by changing the bsconfig.json file in the root of the project.

Replace the line "https": true, with the following:

"https": {

"key": "./certs/server.key",

"cert": "./certs/server.crt"

},

Like this:

clip_image024

Don’t forget to save the bsconfig.json file before running npm start as I did, otherwise it still won’t like your certificate as it will point to the old one .

npm start

If all goes well we’ve now fixed the certificate error due to the different naming and our website will run fine (on your local machine on port 3000):

clip_image026

Now we fixed the website we are now ready to run it as a Word Add-in in a Word client. We will go over the steps to do this in my next blogpost.

To be continued

Office Development–The Bad

clip_image002

Visual Studio Tools for Office (VSTO) is a set of development tools available in the form of a Visual Studio add-in (project templates) and a runtime that allows Microsoft Office 2003 and later versions of Office applications to host the .NET Framework Common Language Runtime (CLR) to expose their functionality via .NET.

Quote: https://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office

As we have seen in my previous blog post Office Development – The Ugly the first option to build your Office extensions using Visual Basic for Applications (VBA) did not win the beauty contest. A very powerful toolset to create beautiful and especially productive customizations. I’m sure that VBA, the tools to set Office to your hand, allowing you to tailor Office in a way that it perfectly fits the needs of your company, is one of the main reasons that made Office such a popular box of applications.

It was however risky – a potential security risk and code management is a pain. In the real world it happened that a ‘new’ version was rolled into production where all the sudden existing features ‘disappeared’ as a result of the developer picking the ‘wrong file’ containing an older version of the solution and continued developing features using the incomplete version.

This needed to change … in the meantime managed code -.NET- appeared at the horizon and the next option at least had to be a) secure and b) needed an improved source control option.

The solution Microsoft put on the table were the:

Microsoft Visual Studio Tools for the Microsoft Office System.

The product name was most likely the longest name available at that time. The Visual Studio Tools for the Microsoft Office System (VSTO) was architected as a bridge between Visual Studio and The Microsoft Office System. On one side the languages team with Visual Studio, on the other side the Office team and in the middle the VSTO team – code named Trinity …

clip_image004

So how is VSTO different compared to VBA?

Applications, add-ins or document customizations, are built using Visual Studio. Initially in a separate Visual Studio .NET 2003 VSTO SKU and after a few version inserted in the Visual Studio 2008 box. The code no longer lived in the documents or other Office files, but from now on lived in an external assembly that would be triggered by the Office host application if the right properties were available in the document.

Loading the assembly was done in a relative complex manner and it was made sure that security wise the pain seen with VBA would not exist with VSTO add-ins.

Here is a schematic overview of the load pattern:

clip_image005

As you can see the Office Application is looking at the Registry to see what add-ins are installed, if found any the Deployment manifest (pointed to by the Registry) is read and following that the Application manifest is read and the assembly loaded.

A real extension to the Office Application … to make this all secure the security is enforced at installation time. There was a whole slew of prereqs that must be covered before the add-in would be installed:

clip_image006

This part made VSTO the “Bad” in The Good, The Bad and The Ugly … it appeared to be a real pain for the IT Pros at the time. By default, VSTO used Click-Once to install the extension on the machine and a lot went wrong … certificates expired, not added to the Trusted Publisher lists, etc. etc. There was another option, to install an add-in using an .msi installer but the average VBA developer had a hard time getting around all of this, jumping all the hoops to make it work.

Was it all Bad?

Not at all! By using managed code, C# or VB.NET – whatever your preference was, your world of Office completely opened. Whatever you could do with .NET (and that is basically everything) you could add to your solution. Calling third party libraries, UI components, later even XAML UI interfaces, sky is the limit.

For me personally this is still my fav option to build Office customizations. I wasn’t awarded nine-year Visual Studio Tools for the Microsoft Office System MVP – I guess the longest MVP title as well, without reason. VSTO controlled my life for the large part of these years (hopefully my wife is not reading this ) did a lot of forum support in this area and built VSTO applications for large enterprise companies. At the time, we still had on Microsoft campus Software Design Review meetings to share feedback and we gave the team a hard time to make sure they released the features that we really needed.

Where to go next

But … the world changed, Windows is no longer the only platform that needs to be supported. Because of that a new mechanism was proposed. By using the common web standards and JavaScript a platform independent system was developed by providing OfficeJS APIs to access the Office object model from web based add-ins.

And this is where we will dive in soon. It’s a relative new world, although development already started years ago, so there is a lot to learn there. Let’s see if we can do the same with OfficeJS as we can do with VSTO – but now on multiple platforms and several clients.

Maybe I will return to VBA and VSTO in separate blog posts just for fun. There is just a ton of information to share on all off these areas.

Book

Oh, and if you really want to know the ins and outs of VSTO I really recommend you reading the VSTO bibles by Eric Carter and Eric Lippert: Visual Studio Tools for Office 2007: VSTO for Excel, Word and Outlook. It is a huge and heavy piece of reference with over a thousand pages of inside information.

Office Development – The Ugly

clip_image002[4]

In the short introduction in my previous post “Office Development–The Good, The Bad and The Ugly?” I was showing three of the main options to build Office add-ins (again I will use Office Add-ins with the capital A to indicate talking about the ‘new’ Office JS Add-ins as opposed to add-ins in general). In this post, I’m now going into the first of each of the options a bit: how to start and where to look for things.

The first option is Visual Basic for Applications (VBA). If you go back a long time, like I do, this was about your first option to customize your Office environment. None of the other options were around at the time. VBA is hosted within the individual host applications such as Word, Excel, Outlook, PowerPoint, Excel or even some other applications not limited Microsoft to but licensed by Microsoft to these other vendors.

Disabled by default

Straight out of the box you need to activate the option to be able to use VBA from the host application. In this case Word, you need to go into the File menu option, select Customize Ribbon and tick the option on the right under Main Tabs called “Developer”.

clip_image004[4]

After activating the option you will notice that the “Developer” Tab becomes visible:

clip_image006[4]

By selecting the tab, you’ll see options like “Visual Basic”, “Macros”, “Record Macro” and more.

Entering your first code

Ok, now it’s time to enter your first VBA code … we won’t be creating the good old “Hello World” but it sure comes close. Click the “Visual Basic” button to open the VBA Integrated Development Environment (IDE) the environment where you build and (test)run your application.

Let’s dive in now … open the VBA IDE as explained above, and enter the following lines:

Sub test()

   Dim DefinedVariable As String
UndefinedVariable = “test”
MsgBox DefinedVariable

End Sub

Like this:

clip_image008[4]

Running the code with function key F5 or stepping into the code with F8 until End Sub (the yellow line) you will see that Word is showing an empty dialog box where you expected the word “test” to appear if all variables were used correctly:

clip_image010[4]

The Ugly I

You will notice that I defined the variable “DefinedVariable” but I initialized the variable “UndefinedVariable” with the value of “test”. I did this to show you the first “Ugly” in VBA … Although you won’t get an error your application will show an unexpected result (well … if the mistake was made in real life)

What happened here is that VBA didn’t throw an error as you are perfectly allowed to initialize an undefined variable (the one called UndefinedVariable in the code) but you’ll find that the variable was displayed that you did not initialize, called DefinedVariable.

This, especially in larger applications will cause extremely unexpected results and if the variables just look alike it will take you hours to notice that you made a typo somewhere.

To avoid these issues “Option Explicit” was invented. By adding the line, VBA requires you to define all variables used in your application. So, if you use this and runs the code your will now see that an exception will be thrown to point out that you missed something here:

clip_image012[4]

Where is your code stored?

You built your first application, but you wonder … where is it stored. Well that is both the beauty and evil thing about VBA, it travels with the document (template, workbook, presentation … depending on your host application). The beauty because it is very easy to distribute, you just share the document, if it is about Word, to another person in your organization and they can run it. That is also -the risk- of being able to just share it, shady virus writers realized this and used it as a vehicle to spread their viruses in a fairly easy manner. Microsoft tried to solve this issue a little bit by differentiating between file naming. Documents containing VBA code are using extensions with an m like document.docm while ‘plain’ no code documents are named document.docx. It is good to know though, that the old extension .doc still exists.

The Ugly II

Lack of security and the risk of writing faulty code makes VBA the “Ugly” one of the three options. You are perfectly capable of creating the best and most intelligent tools using VBA and there have been some great examples that boosted productivity but it is just not “Enterprise” ready. It is hard to control and can cause a large impact on your support division within your company.

So, should you use it? Of course, you can use it whenever you feel it will help you being productive! I still use it myself … if I need to create some quick ‘n dirty solutions to avoid repetition in my “task for the day” it is really easy to record a few lines of code, edit it to fit your needs and run it.

I deliberately didn’t go into details of the VBA language. The reason for that is that I just wanted to provide a primer showing the three development options and my goal will be to dive into the “Good” (as considered today) option. If your want to know how to proceed with VBA I suggest you dive into the documentation behind the links below or find some introduction sites. The benefit of a toolset that has been around for such a long time is that there is a boatload of information available on the interwebs to get you started. This post is merely an introduction as part of the big picture called “Office Development” or as I called it “Office Development 2017”.

Documentation

https://msdn.microsoft.com/VBA/VBA-Language-Reference

Wikipedia

https://en.wikipedia.org/wiki/Visual_Basic_for_Applications

Office Development–The Good, The Bad and The Ugly?

Office Development - Good Bad Ugly

In my last post we’ve seen that there are just a whole list of options covered under Office Development. Just to get a starting point we start to focus on Office add-ins –without the capital A– first. With Office add-ins I mean Office add-ins in general, that is … from the early start to where we are today.

I know that these days the name Office Add-ins is considered to be the part where Office Add-ins are built using the OfficeJS APIs but in the early years you had Office add-ins in many forms and technologies.

The Ugly

Initially the only option to customize Office was by adding code using Microsoft’s Visual Basic for Applications (VBA). This was, or should I say is – it still exists and you can still use it, the embedded code engine running a subset of the full Visual Basic command set capable in addressing the Office Object Model to quickly build some Office automation functions to make life as an Office Worker easier. The language appeared to be very powerful and in the real world people built some crazy complex stuff with it.

The Bad

To add more structure to building Office add-ins a new development paradigm was developed. Still using the Office Object Model but this time using the managed languages such as VB.NET or C Sharp (C#).

Managing code with VBA could be a pain in the … Code traveled with the documents and when the document was copied another ‘branch’ was created and you would never know you were working on the latest version unless you managed your distributions very strictly. Also the declaration of variables wasn’t always enforced causing all sorts of runtime errors to surface just after releasing the production code.

By using this new way to build your Office add-ins or customizations (I will return on customizations vs. add-ins later in a separate blog post, that’s a story on itself) with the new tools: Microsoft’s “Visual Studio Tools for the Microsoft Office System” (VSTO) at least you had a better control over your source code and you could even use Source Control to keep track of your code versions.

With this, the first versions of VSTO, seemed to be very hard when it came to installing the add-ins. Security was improved a lot, but that came at the high price of tough installation issues.

The Good

In these days, deploying add-ins can’t be limited to the Windows Platform so another change was rising … these are the add-ins built using the combination of well known web technologies and by including a Manifest you are now able to run your Office Add-ins (with the capital A) on all platforms in all supported and still growing number of client applications. Currently the client applications are Excel, Outlook, PowerPoint, Word and recently added Project, Access and OneNote.

Really?

Are these three technologies really The Good, The Bad and The Ugly? Well no, obviously not, you can still build add-ins in all three of these technologies but VBA for instance always had a very bad name, also caused by virus developers using this technology to harm innocent computer users by exploiting the technology to take over the computer and in worst case disable it.

VSTO was considered to be a real improvement but this technology also had his downsides. Deployment could be really hard, but this improved in later versions. Still going multi platform was no option for VSTO.

So is all good with the OfficeJS add-ins? Again, not at all … for starters these technologies used here are in general very hard to understand when you come from the VBA or managed code languages. Also, the APIs are not fully completed.

It still isn’t possible to do all the things that you could do with VSTO. With VSTO there is not really a limit. Whatever you can do with managed code you can do with VSTO as it just is interfacing between Office and .NET. This of course in itself could be very dangerous and should be managed to the max. Also VSTO is used by shady guys building malicious code.

We will get more in detail (we are still diving in, going deeper and deeper at this time) in my following blog posts so subscribe and join me in this adventure called Office Development!

Office Development – Where to Start

Office Development – Where to Start

clip_image002

Starting at dev.office.com you see a wide variety of options related to Office Development. There are a couple of sections to identify when you enter the Getting Started entry

  • Microsoft Graph
  • SharePoint Add-ins
  • Office Add-ins
  • Office 365 Connectors

Apparently, this is not all, as below the four options there is another option:

Can’t find what you’re looking for? Check out this full list of all development options for Office linking to the URL https://msdn.microsoft.com/en-us/office. If you follow this URL you’ll notice that it is redirected to, you might have guessed it: https://dev.office.com/docs, the documentation part of dev.office.com.

Here the options are not limited to the options above but now you can see there are sections for:

  • Microsoft Graph
  • Office Add-ins
  • Office 365 Connectors

And below this

  • Office Clients
  • Outlook.com
  • OneNote
  • SharePoint
  • OneDrive
  • Yammer
  • Exchange
  • Skype

Wait …, what …, why is there a section Office Clients separated from the Office Add-ins?

Well, I don’t know. Looking at this it you might think the ‘older technologies’ are under the ‘subsections’. This is true for Office Clients, covering:

  • Office 2013
  • Office 2010
  • Visual Basic Reference

The last item in the list is in fact incorrect, and should have been called “Visual Basic for Applications Reference” or “VBA Reference” or even better “Language Reference VBA” as it redirects to Language reference VBA. It has nothing to do with Visual Basic. Sure Visual Basic resembles a lot compared to Visual Basic for Applications (I will reference to VBA from now on for Visual Basic for Applications) but it isn’t the same. It’s as if you compare Visual Basic to VB.NET they aren’t the same either.

To continue the ‘older technologies’ theory … under SharePoint you find:

  • SharePoint Framework
  • SharePoint WebHooks
  • SharePoint Add-ins
  • SharePoint 2013
  • SharePoint 2010

In this case you see a combination of older and newer technologies. Also under the other subsections you find a mix of newer and older technologies (Exchange) or just newer technologies (Outlook.com, OneDrive, Skype, OneNote and Yammer).

There are even parts missing on this part of the documentation. What about Microsoft Teams as a newer application of Office or existing applications like Microsoft Access. Office and therefor Office Development is just huge, we can spend so much time on these sections, there is no end in all of this.

To start the research, I first want to focus on the Office Clients and their Add-ins. The reason for this is that this was the part that was applied the most when you look at Office customization (adjusting the Office client applications in a way that it suited the needs of the productivity workers).

So, I will drill down from the part covered under Office Clients and move over from there to the section Office Add-ins. This will provide a sense of history and slowly move into the newer technologies as provided in the OfficeJS APIs.

Enough stuff to dig in, so hang in there … see you in my next blogpost(s)

%d bloggers like this: