Buzz
In this Mytour tutorial, we guide you through an example with a result spreadsheet containing two fields: SchoolName and Address. It is stored at the path E:\Examples\Gradebook.xlsx
Step 1: Add the Developer tab to Word. Select Office button → Word options
Step 2: Choose the Popular tab → check Show Developer tab in the Ribbon → OK.
Step 3: Select the Developer tab → Legacy Tools → Text Form Field
Now appearing on the Word page is the Text Form Field tool.
Step 4: Double-click on the Text Form Field tool. Enter txtSchooName into the Bookmark field → OK.
Step 5: Create an additional Text Form Field for the Address field.
Step 6: Enter txtAddress into the Bookmark field → OK.
Step 7: Press the Alt + F11 key combination to launch the Visual Basic Editor (VBE).
Step 8: Go to Insert → Module.
Step 9: Copy the following code into the module window. Then save and return to Word.
Sub TransferToExcel()
'Transfer a single record from the form fields to an Excel workbook.
Dim doc As Document
Dim strSchoolName As String
Dim strPhone As String
Dim strSQL As String
Dim cnn As ADODB.Connection
'Get data.
Set doc = ThisDocument
On Error GoTo ErrHandler
strSchoolName = Chr(39) & doc.FormFields('txtSchoolName').Result & Chr(39)
strPhone = Chr(39) & doc.FormFields('txtPhone').Result & Chr(39)
'Define sql string used to insert each record in the destination workbook.
'Don't omit the $ in the sheet identifier.
strSQL = 'INSERT INTO [PhoneList$]' _
& ' (SchoolName, Phone)' _
& ' VALUES (' _
& strSchoolName & ', ' _
& strPhone _
& ')'
Debug.Print strSQL
‘Define connection string and open connection to destination workbook file.
Set cnn = New ADODB.Connection
With cnn
.Provider = 'Microsoft.ACE.OLEDB.12.0'
.ConnectionString = 'Data Source=E:\Examples\Gradebook.xlsx;' & _
-'Extended Properties=Excel 8.0;'
.Open
Transfer data
Execute strSQL
End With
Set doc = Nothing
Set cnn = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Number & ': ' & Err.Description, _
-vbOKOnly, 'Error'
On Error GoTo 0
On Error Resume Next
cnn.Close
Set doc = Nothing
Set cnn = Nothing
End Sub
Step 10: Double-click on the Text Form Field for the Address field. Choose TransferToExcel in the Exit → OK box.
Now, when you press the Tab key on the keyboard, the Address field will copy data from both the SchoolName and Address fields to the Gradebook.xlsx file with the path E:\Examples\Gradebook.xlsx. If your Excel file and path are different, modify the code accordingly with your file's name and path.
Understanding the data is the key to smoothly converting each new record without encountering serious issues. The example code contains essential skills to get you started. You'll need to enhance the technique to handle data adjustments and other requirements.
So, Mytour has guided you through transferring data (Form) from Word to Excel. If you want to reverse the process, you can follow the convert Excel to Word method, copy, and insert Excel into Word.
