How to add a picture to a worksheet in Word and Excel using VSTO with c# and autosize

Here is a really simple example of adding a picture without really needing to know the dimensions of the image. Excel uses points which is pretty annoying, since pics are in pixels….duh… I put in the sizes for my pics, but you could use any number since we repair it with the scale calls. Anyway, here it is. Word vstoDoc is my document.

[csharp]
//push resource to disk.
var path = Path.GetDirectoryName(vstoDoc.FullName);
var fn = Path.Combine(path,"rfn.png");
//insert the fun fact box at top.
var pic = vstoDoc.Shapes.AddPicture(fn,false,true,5,5,455/1.0,162/1.0);
pic.Name = ROLLFORWARDSHAPENAME;
pic.ScaleHeight(1, MsoTriState.msoTrue);
pic.ScaleWidth(1, MsoTriState.msoTrue);
[/csharp]

Here is Excel. vstoSheet is my Worksheet. I loop through them to add pics.

[csharp]
//push resource to disk.
var path = Path.GetDirectoryName(vstoDoc.FullName);
var fn = Path.Combine(path,"rfn.png");
var pic = vstoSheet.Shapes.AddPicture(fn, MsoTriState.msoFalse, MsoTriState.msoTrue, 5, 5, 231 * 72 / 96, 317 * 72 / 96);
pic.Name = ROLLFORWARDSHAPENAME;
pic.Locked = false;
pic.ScaleHeight(1, MsoTriState.msoTrue);
pic.ScaleWidth(1, MsoTriState.msoTrue);
[/csharp]

Happy Coding!

How to speed up Excel and Word 2003,2007 and 2010 launch times when using Automation in C#

One of the biggest impacts on the launch time for Office is the speed at which all of the addins load. Unfortunately, poorly written or unnecessary addins add a startup cost which isn’t something you want to have to pay when loading Office via automation.
I wrote a nice helper class that allows you save the pain of loading them, without impacting the users experience.
Here is the class:

[csharp]
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Win32;
 
namespace Seekford.Office
{
    public class OfficeAddinManagementUtility : IDisposable
    {
        private Dictionary<string, int > wordLoadBehaviorStorage = new Dictionary<string, int >();
        private Dictionary<string ,int > excelLoadBehaviorStorage = new Dictionary<string, int >();
 
        private const string _wordAddinRootKey = @"SoftwareMicrosoftOfficeWordAddins";
        private const string _excelAddinRootKey = @"SoftwareMicrosoftOfficeExcelAddins";
        private const string _loadString = "LoadBehavior";
 
        public void DisableAllWordAddins()
        {
            if (wordLoadBehaviorStorage.Any()) return;//unbalanced?
            DisableAllAddinsInHive(_wordAddinRootKey, wordLoadBehaviorStorage);
        }
 
        public void DisableAllExcelAddins()
        {
            if (excelLoadBehaviorStorage.Any()) return;//unbalanced?
            DisableAllAddinsInHive(_excelAddinRootKey, excelLoadBehaviorStorage);
        }
 
 
        private void DisableAllAddinsInHive(string hiveLocation, Dictionary<string, int > storage)
        {
            var userKey = Registry.CurrentUser.OpenSubKey(hiveLocation);
            if (userKey != null)
            {
                var subKeys = userKey.GetSubKeyNames();
                if (subKeys != null)
                {
                    foreach (var s in subKeys)
                    {
                        var subKey = userKey.OpenSubKey(s, true);
                        int val = (int)subKey.GetValue(_loadString, 0);
                        if (val != 0)
                        {
                            subKey.SetValue(_loadString, 0);
                            storage.Add(s, val);
                        }
                    }
                }
            }
        }
 
        private void RevertAllAddinsInHive(string hiveLocation, Dictionary<string, int > storage)
        {
            var userKey = Registry.CurrentUser.OpenSubKey(hiveLocation);
            if (userKey != null)
            {
                var subKeys = userKey.GetSubKeyNames();
                if (subKeys != null)
                {
                    foreach (var s in subKeys)
                    {
                        if (storage.ContainsKey(s))
                        {
                            var subKey = userKey.OpenSubKey(s, true);
                            if(subKey != null)
                            {
                                subKey.SetValue(_loadString, storage[s]);
                                storage.Remove(s);
                            }
                        }
                    }
                }
            }
        }
 
        public void RevertAllAddins()
        {
            RevertWordAddin();
            RevertExcelAddin();
        }
 
        public void RevertWordAddin()
        {
            if(wordLoadBehaviorStorage.Any())
                RevertAllAddinsInHive(_wordAddinRootKey, wordLoadBehaviorStorage);
        }
 
        public void RevertExcelAddin()
        {
            if(excelLoadBehaviorStorage.Any())
                RevertAllAddinsInHive(_excelAddinRootKey, excelLoadBehaviorStorage);
        }
       
        public void Dispose()
        {
            RevertAllAddins();
        }
    }
} 
[/csharp]

See how easy that is.

Just wrap your Word or Excel code, call the right methods and away you go.

[csharp]
//code below is a snippet showing you to disable addins, create your automated word,
// then reenable the addins for future word users
// use the excel methods for excel addins.
               var addinManager = new OfficeAddinManagementUtility();
                try
                {
                        addinManager.DisableAllWordAddins();
			myWord = new Word();
                }
                finally
                {
                    addinManager.RevertWordAddin();
                    addinManager.Dispose();//same as above, but lets comply with regs
                }
                return myWord;
[/csharp]

The utility disables all the Office Addins and then enables them back for the next launch of Office.

It is an easy to use C# class for disabling Word 2007/2010 and Excel 2007/2010 Addins. Should work with 2003 as well.

Happy Coding!

How to create CheckBoxes in Word 2007 without using the ActiveX or Legacy Forms checkbox!

So you think you are stuck with using that lovely ActiveX checkbox for your document. The way most people thought, I would have figured you were stuck with it too. The other alternative, the Legacy Forms checkbox, is rarely viable as you have to protect the document. That means no edits, which is a whole new pain.

Well, what’s wrong with the ActiveX checkbox you ask? Not much, if you don’t mind extreme document load times when you have a large number of them. Also if you don’t mind lugging around the ActiveX baggage and security issues that come with them. Plus the conversion issues when displaying them online.

So, what is the solution? It is much simpler than you would think. I have seen some complex solutions that people have employed with Templates and other nonsense. Those require you to give that template to the end reader and is just kludgy.

I have a simpler approach. A very short macro and the use of Fields, which in turn I use at MacroButtons. Simple, yes. Effect, yes.

Think outside the box Smile

Here is the how to:

Create a Macro named CheckMe in your document.

image

Paste this code into it:

Sub CheckMe()

‘ CheckMe Macro


”MsgBox “clicked”
Dim f As Field
”254 is check box
”168 is unchecked
For Each f In Selection.Fields
f.Code.Font.Name = “WingDings”
If (f.Code.Text = “MACROBUTTON CheckMe ” + Chr(111)) Then
f.Code.Text = “MACROBUTTON CheckMe ” + Chr(254)
Else
f.Code.Text = “MACROBUTTON CheckMe ” + Chr(111)
End If

Next

End Sub

image

Now you have it setup to handle the new field.

Time to add the field. After you add one, it is super simple to add more. All you will to do is copy and paste.

Add our Macro Field aka our CheckBox.

I made this really easy for you so you don’t need to lookup symbols or any of that jazz.

Step 1  – Click Insert –> Quick Parts –> Field

image

Step 2 – Data Entry

Select MacroButton from Field names.

Enter Display Text of BOX

Select CheckMe from MacroName list

Hit OK

image

But now I have the word box? That’s not a check box! You fooled me!

image

Well, I said I was making it easier on you. Click the word! Voila. Like magic, it is now  a check box.

image click again

image

Yeah! Checkbox.

You can copy and paste this to where you want, or go through the steps again. Copy and paste is easier.

I have attached a working Word 2007 document to example the alternative checkbox technique.

Happy coding!

Example of not using checkboxes

How to Host/embed Microsoft Word or Excel in a web browser or your own application?

I am currently looking to see if there is any demand for the feature to host Office applications, like Word or Excel in a browser or an application. I am considering writing an ActiveX control that would allow for this ability, but have not decided if it is worth the effort.

If you think you would find this useful, and would want to buy something like this, let me know. I would hate to waste my time to make something no one would use, except for me.

-Brian