Dynamically viewing Word Documents in Excel with a UserForm to expand the functionality of your Excel solutions.
Embedding Applications in Applications
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.
However, Access has (or had) a limitation restricting the number of 'objects' which could be displayed on a screen. So, the approach I took was to "embed" an Excel workbook on an Access UserForm to display the grid.
Technically, this is called "Object Linking and Embedding" or "OLE".
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.
Displaying Word Documents in Excel
In one project, the client wanted to preview Word documents directly from Excel without having to switch applications to Word to view the document.
Typically, these were structurally simple documents like letters or contracts.
The Excel solution drove the application – such as a contract management tool – and needed a simple and convenient way to allow users to preview Word documents before processing them.
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.
The Code to Display the Document
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.
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.
Display the Word Document
From the user's perspective, what they is the selected “document”, although one converted to HTML for display purposes.
While suitable for simple document layouts such as letters and contracts, it is not suitable for documents with complex layouts and lots of graphics.
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.
Over to You
Over to you - when have there been times you needed the output from one application displayed in Excel? How did you achieve this? Leave your comments below.
0 comments