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
Popularity: 2% [?]
Post a Comment