Editing Excel Content in Word

A reader asked us if there was a way to edit the contents of a cell in Excel, using an external program, for example Microsoft Word. This was an interesting challenge and we could see how there were many possible uses. Here is how we solved it!

Using Our Solution

If you want to see our resulting spreadsheet and follow along, go grab the example Excel file.

To make it easy to use, we bound the macro EditSelectedCell to a keyboard combination (Ctrl-E).

To launch Word and edit the cell content, put your cursor in the cell you wish to edit.

Now use the keyboard combination to launch the macro. When you are in Word, type your text.

When you are done, close your document.

Your cell will now be populated with the text you typed in Word.

How it Works

Each cell editor is an object of class CellEditor, which wraps a Word application and document, as well as a reference to the edited cell.

'' The Word application for this editor
Private WithEvents wApp As Word.Application
'' The Excel edited cell by this editor
Private editedCell As Excel.Range
'' The Word document for this editor
Private wDoc As Word.Document

Each cell editor handles the Word application’s WindowActivate and WindowDeactivate events in order to copy the cell contents from Excel to Word and back, so it appears to the user as if the editing is done inside Word.

Private Sub wApp_WindowActivate(ByVal Doc As Word.Document, ByVal Wn As Word.Window)
    '' As soon as Word appears, copy the cell contents into the document
    wDoc.Range.Text = ExcelToWord(editedCell.Formula)
End Sub

Private Sub wApp_WindowDeactivate(ByVal Doc As Word.Document, ByVal Wn As Word.Window)
    '' As soon as Word disappears, copy the cell contents back to Excel
    editedCell.Formula = WordToExcel(wDoc.Range.Text)
End Sub

In order to allow the user to close the Word application without saving, the CellEditor object also handles the Word application’s DocumentBeforeClose event, and closes the application without saving.

Private Sub wApp_DocumentBeforeClose(ByVal Doc As Word.Document, Cancel As Boolean)
    '' Prevent application from asking the user to save. Instead just close
    wDoc.Close (False)
    wApp.Quit (False)
    Cancel = True
End Sub

The CellEditorStatic module

In order to prevent creating more than one editor of the same cell, the CellEditorStatic module holds a dictionary of all active editors.

'' Dictionary of existing editors for cells
Private editorMap As Object

The dictionary object itself is initialized when it is first accessed.

An existing editor is simply activated using Activate instead of creating a new one, thus bringing it to the front of the UI.

If (editorMap.Exists(CellKey(cell))) Then
    editorMap(CellKey(cell)).Activate
Else
    editorMap.Add CellKey(cell), New CellEditor
End If

FinishEditCell handles the case when an editor closes (Word application’s Quit event), in order to remove the editor from the dictionary.

If (editorMap.Exists(CellKey(cell))) Then
    editorMap.Remove CellKey(cell)
End If

Note that the cell’s key in the dictionary is its address in external form (e.g. [DocumentName]SheetName!A2 ), so that editors of cells in different sheets do not collide.

Private Function CellKey(cell As Excel.Range)
    '' Computes the key to a cell in the dictionary
    CellKey = cell.Address(, , , True)
End Function

Summary

Although this is a simplistic article just to keep things simple, it does demonstrate how Microsoft Office allows far more integration between applications than it at first might seem. How might you use this feature? We would love to know your ideas …

About the author

Yoav is the CEO of a company called Cogniview that creates software to convert files from PDF to XLS.

Prior to that, Yoav Ezer was the CEO of Nocturnus, a technology-centered software solution company.

For more Excel tips from Yoav, join him on Facebook or Twitter