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!).
Now use the keyboard combination to launch the macro. When you are in Word, type your text.
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.