By “MS Word Electronic Form†I’m assuming you mean a Word document with form
fields?
Question: Is there a reason you’re using Word as your interface? Why not
create the data entry form in Excel? Wouldn’t the coding be more
straightforward?
In any case...
Below is some code that will give you some idea of the general steps that
need to be followed to get info from a Word form field into an Excel cell.
There are many ways of implementing the concepts the code below illustrates
(creating/referring to/destroying objects, working with ranges and strings,
etc) and you’d need to have some familiarity with how to navigate around Word
documents and Excel spreadsheets through code.
For illustration purposes, you’ll need a Word document (doesn’t have to be a
template for this exercise) with a simple text field. If you create a text
form field from the Forms toolbar, it will have a default Bookmark Name of
“Text1†– you can change that name but it’s important to note because that’s
how you would navigate around a Word form, by referring to fields by their
_bookmark_ names.
There are two macros below that show different ways of transferring the code
– into a spreadsheet that’s already open on your computer, and into one that
isn’t open.
USING AN OPEN SPREADSHEET
In Excel, open a spreadsheet or create a new one and save it.
Using the VBE copy the following code into a module in your Word document
Sub AddInfoToOpenExcelSheet()
Dim objOpenExcel As Object
Dim objOpenSheet As Object
On Error GoTo errorhandler
Set objOpenExcel = GetObject(, "Excel.Application")
Set objOpenSheet = objOpenExcel.ActiveSheet
ActiveDocument.Unprotect Password:=""
objOpenSheet.Cells(1, 1) = Selection.Bookmarks(1).Range.Text
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, _
NoReset:=True, _
Password:=""
Set objOpenSheet = Nothing
Set objOpenExcel = Nothing
Exit Sub
errorhandler:
If objOpenSheet Is Nothing Then
'do nothing
Else
Set objOpenSheet = Nothing
End If
If objOpenExcel Is Nothing Then
'do nothing
Else
Set objOpenExcel = Nothing
End If
MsgBox Err.Number & " " & Err.Description
End Sub
In the document itself:
1. right click on the Text1 field,
2. choose Properties
3. in the “Run macro on exit†drop down
choose AddInfoToOpenExcelSheet.
4. Click OK to close the Text Form Field Options dialog.
Protect your form for fields with no password (using the padlock button on
the Forms toolbar for example).
Type something in the field and press the Tab key.
Switch to Excel and you should see whatever you typed in cell(1,1). Note
that the text transferred to Excel is preceded by “FORMTEXT†– this is an
example of things you need to be aware of and accommodate, for instance by
stripping out that string before applying the value to cell(1,1):
strFieldValue = Selection.Bookmarks(1).Range.Text
objSheet.Cells(1, 1).Value = Right(strFieldValue, Len(strFieldValue) - 9)
(9 being the length of “FORMTEXT†plus the space after it).
If you read the code for AddInfoToOpenExcelSheet you’ll see that it first
creates an object (objExcel) that points to the open Excel session, then
another object (objWorkbook) that points to the active worksheet in Excel.
The code then unprotects the form and makes the value of cell(1,1) equal to
the value of whatever Word bookmark the Word selection point is contained in
(in this case, form field Text1) – by refering to Selection.Bookmarks(1) the
macro can work with any field on the form. The code then protects the form.
Finally it erases the objects from memory (this is very important).
REFERRING TO A SHEET THAT IS NOT OPENED.
In Excel, open a spreadsheet or create a new one and save it as
C:\YourWorkbook.xls (this is the name that’s in the code – you can change it
in the code if you want). Exit Excel (close it down so it’s not running).
Using the VBE, copy the code at the end of this message into the
ThisDocument module of your Word document (it needs to go into the
ThisDocument module because it uses the Document_Open and Document_Close
events).
In your Word document,
1. right click on the Text1 field,
2. choose Properties
3. in the “Run macro on exit†drop down
choose AddInfoToExcel.
4. Click OK to close the Text Form Field Options dialog.
Protect your form for fields with no password (using the padlock button on
the Forms toolbar for example), save and close your document. You need to
close the document this time so that the objects are properly created when
you open it.
Open the document (and say yes if you’re prompted whether to run macros).
Type something in Text1 and press tab. Save your document and close it.
Open YourWorkbook.xls – whatever you typed in Text1 should be in Cell(1,1).
The code: When you opened your form, the Document_Open event ran the
CreateObjects macro which created the objExcel and objWorkbook object. When
you tabbed out of Text1, AddInfoToExcel used those objects to transfer the
information. When you closed your document, the Document_Close event
destroyed the objects to remove them from memory. Creating the objects in
Document_Open and destroying them in Document_Close has the advantage of not
wasting memory continually opening and closing Excel in the background each
time you exit a field.
THE CODE
Like I said, there’s lots of ways you can use these concepts. Also, I’m no
expert, and others may have other (better) ideas about how to get what you
want done.
HTH
Option Explicit
Public objOpenExcel As Object
Public objWorkbook As Object
Private Sub Document_Open()
CreateObjects
End Sub
Private Sub Document_Close()
DestroyObjects
End Sub
Sub CreateObjects()
On Error GoTo errorhandler
Set objOpenExcel = CreateObject("Excel.Application")
Set objWorkbook = _
objOpenExcel.workbooks.Open("c:\YourWorkbook.xls")
Exit Sub
errorhandler:
DestroyObjects
MsgBox Err.Number & " " & Err.Description
End Sub
Sub AddInfoToExcel()
On Error GoTo errorhandler:
ActiveDocument.Unprotect Password:=""
objWorkbook.Worksheets(1).Cells(1, 1) = _
Selection.Bookmarks(1).Range.Text
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, _
NoReset:=True, _
Password:=""
objWorkbook.Save
Exit Sub
errorhandler:
DestroyObjects
MsgBox Err.Number & " " & Err.Description
End Sub
Sub DestroyObjects()
If objWorkbook Is Nothing Then
'do nothing
Else
objWorkbook.Close False
Set objWorkbook = Nothing
End If
If objOpenExcel Is Nothing Then
'do nothing
Else
objOpenExcel.Application.Quit
Set objOpenExcel = Nothing
End If
End Sub
)