Finally, Edit Your 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 here (this file will be blank when you open it – this is normal!).

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 (you don’t need to save it).

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

If (editorMap Is Nothing) Then
    Set editorMap = CreateObject("Scripting.Dictionary")
End If

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.

P.S.

Don’t forget to check out our PDF to Excel Converter. It can save you a lot of precious time and improve your productivity.