Viewing Word Documents in Excel
Years ago I developed an Access database to manage shipping containers for a distribution centre.
The solution needed to display the location and stacked position of 20 and 40 foot shipping containers on a 30-by-30 grid where each square in the grid represented a 20-foot area.
The approach I took was to embed an Excel workbook on an Access UserForm to display the grid. The cells could be dynamically sized and colour-coded where each colour represented how many containers were stacked in that position.
(In other projects I’ve used Office Web Controls and still lament their demise).
Being able to embed the contents of other application was a very quick and convenient way to achieve a result.More recently I was asked to do the same in Excel allowing the client to preview a series of Word documents from within Excel.
The Web Browser Control
However, unlike MS Access, Excel UserForms do not natively support embedding other applications objects (documents).
Excel UserForm do, however allow you to embed “controls” on them. These controls can be what you would normally consider to be a control to do things like display a list or calendar.
But they also include “mini application” controls such as Windows Media Player to play videos on an Excel UserForm. In this case, I was able to use the Web Browser Control.
Now I know what you’re thinking. Word documents aren’t web pages.
That’s where a little slight-of-hand was required. When the client selected a Word document on the form, in the background the Word document was saved as a HTML page to a temporary folder which in turn was displayed on the form.
Sometimes, Excel development involves a dash of smoke-and-mirrors.The Code
There is some sleight-of-hand happening in the background.
As the client only needs to “preview” the document, the selected document is saved in HTML format in the background.
It is the converted HTML document which is displayed in the UserForm, not the original Word document.
Viewing the Word Document
The end result was the client could preview a list of Word documents before selecting one to process using data stored in the Excel workbook.
A slight caveat with this story. This approach worked for documents with simple structures and layouts. In this case letters and contracts. For more complex layouts or documents with lots of graphics, the conversion to HTML for viewing doesn’t produce satisfactory results.
