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

KillExcel – A simple way to purge those unwanted ghost processes

If you develop Office Addins, or automate office, you probably have the issue of dealing with a lot of ghost Excel processes. You get them when you don’t free properly, or you kill your process but the Excel doesn’t get terminated(which is almost certain when you abnormally terminate).

Anyway, this simple executable will iterate all your Excel processes and kill them.

Note: Built in 4.0 .NET framework.

KillExcel.exe

Happy Coding!

How to optimize download performance for files stream from your database via asp.net

I had a requirement to create a download page that streamed office documents to the client from our website. The way Office (Word and Excel at least) open documents from URL’s are the browser downloads it first, then Word and Excel call back to the server to make sure nothing changed.

Normally, these results in you streaming the file twice, unknowingly. That is bad for performance and your bandwidth. No one wants a laggy and latent operation.

So, what is there you can do? Well, if you intercept the headers to a normally hosted document, you will see a header called ETag. This is basically a document hash reference that can be used when the follow request occurs.

Ok, English right.

So, the solution I implemented is pretty easy. I created a unique hash tag (it can be a true hash, or simply a rowid and the recordtimestamp combined) and set the ETag header.

You ask, “Well, how does setting an ETag header prevent duplicate efforts?”. It doesn’t. Here comes the work you do first.

When a request comes in. Look for a header called: IF-None-Match. If you see it, you need to jump into a little simple logic.

First, calculate your Etag value and compare it to the value of the If-None-Match and if it, well, matches, you get to do something special. If it doesn’t match, act like nothing happened and continue on with the normal data return.

So, what is the something special you ask? Simple, Clear your response contents, set your StatusCode to 304, and end the response .

Now I hear, geez. You made this sound very complicated. Is there an easier way to explain this?

Sure…I’ll try.

 

Here are the steps for simplicity:

  1. Check for If-None-Match header
    1. If Exists, compare value of header to your hash(id combo, CRC, whatever you want to use)
      1. If Matches,
        1. Set StatusCode =304,
        2. Clear Response Contents
        3. End Response
      2. Else.
        1. Continue as normal
  2. Normal Winking smile
    1. Generate ETag hash (again, however you want to identify this file as unique, something that changes if the file changes)
    2. Response with ETag in header

What does that mean in code? I don’t speak flow.

Here is a code example.

  private const string _wordMimeType = "application/msword";// (for Microsoft Word files)
    private const string _excelMimeType = "application/vnd.ms-excel"//(for Microsoft Excel files)
   
    private void StreamDocumentfromDatabase(Guid documentID)
    {
        string etagFilter = Request.Headers["If-None-Match"];
        string etag;
        //load the bits into memory
        using (var context = new EvidenceReviewContext())
        {
            if (!string.IsNullOrEmpty(etagFilter))
            {
                //check db to see if to respond with ignore (cached copy already found)
                var blobCheck = (from ev in context.Documents
                                 where ev.documentID == documentID &&
                                 ev.BlobDataId.HasValue
                                 select ev.BlobDataId.Value
                                     ).FirstOrDefault();
                if (blobCheck != null)
                {
                    if (etagFilter == (documentID.ToString() + blobCheck.ToString()))
                    {
                        Response.ClearContent();
                        Response.StatusCode = 304;                       
                        Response.End();
                    }
                }
            }

            var theBlob = (from ev in context.Documents
                           where ev.documentID == documentID &&
                           ev.BlobDataId.HasValue
                           select new
                           {
                               Data = ev.BlobData.Data,
                               FileExtension = ev.FileExtension,
                               BlobID = ev.BlobDataId.Value
                           }).FirstOrDefault();

            if (theBlob == null)
                return;

            if (theBlob.FileExtension.ToLower().StartsWith(".doc"))
                Response.ContentType = _wordMimeType;
            else if (theBlob.FileExtension.ToLower().StartsWith(".xls"))
                Response.ContentType = _excelMimeType;
            Response.AddHeader("Content-Disposition", "attachment; filename=" + documentID.ToString().Replace("-", "") + theBlob.FileExtension);
            etag = documentID.ToString() + theBlob.BlobID.ToString();
            Response.AddHeader("ETag", etag);

            //Write the file directly to the HTTP content output stream.

            //NOTE: THIS IS NOT MEMORY EFFICIENT AT ALL. TODO: change compression technique for documents
            byte[] dataToSend = null;
            using (MemoryStream dataStream = new MemoryStream(theBlob.Data))
            {
                using (MemoryStream stream = UnzippedData(dataStream))
                {
                    dataToSend = stream.ToArray();
                }
            }
            //relieve some pressure by pushing write of data outside of the blocks above to allow for memory free.
            Response.BinaryWrite(dataToSend);
            Response.Flush();
        }
        Response.End();
    }

I hope this article helped you out a little and saved you some serious download times. (When Office revisits the file, it remembers the ETag. So you get efficiencies all over the place).

Happy Coding!

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

How to fix Handler “PageHandlerFactory-Integrated” has a bad module “ManagedPipelineHandler” in its module list

If you did what I did, and just picked the default IIS installation options for your Windows 7 box, then you probably get a bunch of fun errors.

This one is if you didn’t install IIS support for asp.net. (WHICH SURPRISE, IS NOT DEFAULT SELECTED)

This configuration section cannot be used at this path. This happens when the section is locked at a parent level. Locking is either by default (overrideModeDefault="Deny"), or set explicitly by a location tag with overrideMode="Deny" or the legacy allowOverride="false".

The fix is to install ASP.NET support. Easy.

image

 

Now, why the error from the title? Why no PageHandlerFactory blah blah blah? Well, .NET didn’t register itself with IIS the way it wants, so you need to rectify that.

That is also easy. Never hard if you have the right tool, it’s finding the right tool that is hard.

Open a Command prompt with Administrative Privileges. (Key, otherwise it doesn’t work).

go to your Framework folder. %WINDIRMicrosoft.NET

Pick the one you are working with, and the right bit type. i.e. 64 if your on 64 bit. Then the right version. I use 4.0

Call aspnet_regiis.exe -i

 

image

 

Voila, your done. Restart IIS and try your page again.

Unit Test to verify Entity Framework Model (EDMX) is accurate

I work extensively with the Entity Framework in most of my projects. One thing that gets frustrating though is when you work with a large team and the data model gets out of synch with your local database.

You normally don’t figure it out until you are running some operation in your application and get the dreaded Entity Framework error. (inner exception = Column Missing), or non-nullable type, or other enjoyable light reading.

I put together a simple unit test that will work with any model and verify it using a very simplistic technique. It’s a quick smoke test.

 

You will need to modify this code to fit your mechanics for creating the entity context, and the connection strings, etc. Other than that, it is shake and bake.

        [TestMethod]
        public void VerifyModelAccuracy()
        {
            //FYI – When switching to multiple engagements, will need to pass in engagement id for db connection (get from routing db)
            using (var context = EngagementContextFactory.CreateEngagementContext())
            {
                //lazy and dont want to research why this isnt loading
                //use reflection to iterate over each collection in the context and just watch for errors.

                Type t = context.GetType();
                var props = t.GetProperties();
                foreach (var p in props)
                {
                    var isEnumerable = (from i in p.PropertyType.GetInterfaces()
                                        where i == typeof(IEnumerable)
                                        select i
                                            ).Count() > 0;
                   
                    if (isEnumerable)
                    {
                        try
                        {
                            IEnumerable dataToPull = (IEnumerable)p.GetValue(context,null);
                            if (dataToPull != null)
                            {
                                var result = (from object d in dataToPull select d).FirstOrDefault();
                            }
                        }
                        catch (Exception ex)
                        {
                            string error = ex.Message;
                            if (ex.InnerException != null)
                                error += ex.InnerException.Message;
                            Assert.Fail(error);
                        }
                    }
                }               
            }
        }
Happy Coding!!

Request for Comment – Not a normal RFC :)

I normally only use this blog to help seal in my mind issues I have worked on. Hence, the brevity of most posts. I just write the basic knowledge needed to complete the specific task, but without a lot of wordy fanfare a lot of bloggers like to put in. 8 pages how-to’s seem odd for a blog, but hey, they are plentiful out there.

Anyway, my question to those of you who are happening upon my small piece of the Internet: Do you want video how-to’s, specific information or answers? If so, comment to let me know.

Otherwise, I will continue with my random tip or blurb of knowledge.