Software

10 steps to transferring Word form data to an Excel sheet

When you need to transfer a data record to Excel, a Word form -- and a little VBA -- makes the process a snap.

Data transfer often comes in bulk jobs with multiple records and data fields. But occasionally, you'll need to transfer only one record at a time. For example, you might need to transfer details from a data entry order form to a larger purchasing database. In that case, you transfer details as each order is processed, one at a time. When you're facing such a task, you can use VBA code -- and you can set it all up in 10 quick steps.

1: Analyze your needs

There are several parts to a transfer task. The scenarios are unique, but the components are generally the same:

  • The data you're transferring
  • The source file that contains the data
  • The destination file to which you're transferring the data
  • The transfer medium used to make the switch; usually, it's code

You'll need to identify these four components before you do a thing.

Our example uses a Word form to gather data (input values), but you might use a Web form, an Excel userform, or some other format. The transferring code and process will be mostly the same, regardless of the input's format. This exercise is less about the source and more about the ability to transfer records, one at a time, to a destination file.

2: Determine the destination format

After ensuring that you have all the pieces you need to begin your work, determine the physical dynamics of the destination file. Usually, this format is predetermined. We'll transfer two text elements, a company's name and phone number, record by record, into the simple Excel sheet shown in Figure A.

Figure A

Our example sheet is simple on purpose. Transferring the data is the job; the number of fields is usually irrelevant.

3: Identify the destination data types

Once you know the format, note the data types the source file expects to receive. You might have to convert data types before actually transferring the data. We won't do so in this example. Both fields in this destination sheet are text using the General format. But it's important to note this information before beginning because the data might need special handling. For instance, strings and dates must be delimited property.

4: Note the destination file's location

The next bit of information you'll need is the path to the destination file. In this example, both files will be on the same drive but in different folders. Some data must travel long distances to get from the source to its destination file, and you'll need to know every node of that journey. If you're using a network, you might need to code in special permissions and passwords to use along the way. Our example destination workbook resides at E:\Examples and isn't password protected.

5: Create the source form

If you're lucky, you'll have some flexibility when choosing the source format (but not always). In this case, we'll use the simple Word form shown in Figure B to collect two pieces of data.

Figure B

Use Word's form fields to collect data.

A Word form is a document that contains fill-in blanks called fields. Each field is a predefined cell that stores data input. To create the example Word form, insert two text fields into a blank Word document as follows:

  1. Click the Developer tab and then choose the ab field from the Legacy Tools drop-down in the Controls group (circled in Figure B). In Word 2003, choose Toolbars from the View menu and select Forms, where you'll find the Text Form Field control.
  2. Click Properties in the Controls group or double-click the field to display its properties.
  3. Enter txtCompanyName in the Bookmark property, as shown in Figure C.
  4. Click OK.
  5. Repeat steps 1 through 4, entering txtPhone in step 3.
  6. Save the form.

Figure C

The text form field is a legacy tool in the Ribbon versions.

6: Add the basic code

To add the code that transfers a single record from the fields to the example workbook, do the following:

  1. With the Word form open, press [Alt]+[F11] to launch the Visual Basic Editor (VBE).
  2. From the Insert menu, choose Module.
  3. Enter the code in Listing A.
  4. Save the module and return to the Word form.

Listing A: The transferring macro

Sub TransferToExcel()
'Transfer a single record from the form fields to an Excel workbook.
  Dim doc As Document
  Dim strCompanyName As String
  Dim strPhone As String
  Dim strSQL As String
  Dim cnn As ADODB.Connection
  'Get data.
  Set doc = ThisDocument
  On Error GoTo ErrHandler
  strCompanyName = Chr(39) & doc.FormFields("txtCompanyName").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$]" _
    & " (CompanyName, Phone)" _
    & " VALUES (" _
    & strCompanyName & ", " _
    & 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\Sales.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

7: Add a way to execute the macro

You could add a macro button to the Ribbon or even a command button to the document. But using form fields, you can bypass the interface tools and let one of the fields execute the macro as follows:

  1. Double-click the phone field (txtPhone) to open its property sheet.
  2. From the Exit drop-down, select the transfer macro from Listing A, TransferToExcel, as shown in Figure D.
  3. Click OK.

Pressing [Tab] to leave the phone field will execute TransferToExcel(), which will copy the text in the company name and phone fields to Sales.xlsx. When applying this code to your own work, be sure to update the path appropriately.

Figure D

Select the transfer macro from the Exit drop-down.

8: Protect the Word document

Before using the Word form, restrict its use by limiting changes to the form fields as follows:

  1. Click the Developer tab and then click Restrict Editing in the Protect group. In Word 2003, click Protect on the Form toolbar.
  2. In the resulting task pane, click Allow Only This Type Of Editing In This Document.
  3. From the drop-down, select Filling In Forms, as shown in Figure E.
  4. Click Yes, Start Enforcing Protection.
  5. Enter a password twice. Or leave both password entries blank if you don't need password protection.
  6. Click OK.

Figure E

Enable protection to restrict data entry to the form fields.

9: Use the form

All your basic components are in place and you're ready to use the form. To do so, tab into the first field (if necessary) and enter a company name. Press [Tab] and enter a phone number, as shown in Figure F. After entering the phone number, press [Tab] to execute the code. Then, check the Sales.xlsx Excel workbook. As you can see in Figure G, the code transferred the record, as expected. The code appends each record as transferred, allowing you to accommodate existing data.

Figure F

Using form fields to collect data is easy.

Figure G

Our macro code copied the data from the Word form to an Excel sheet.

10: The rest of the story

The macro in Listing A covers the basics. It identifies the data and transfers it, as is, to a destination workbook. That part's common to almost all transfer tasks where you're moving one record at a time. There's much more to consider. For instance, there's no data validation; there's nothing to force users to enter a valid phone number in the right format. To ensure consistent and valid data, include code that validates the data (usually before transferring).

You also might want to include a confirmation message that asks users to confirm the transfer before actually executing the code. Right now, the transfer is automatic. The error handling is bare bones. You'll need to test your code thoroughly for all possible problems. These are just a few of the areas you'll want to customize.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
xoan.ninguen
xoan.ninguen

Some fixings for this to work:

As stated by elyo10, should set a Reference to Microsoft Active Office Data Library 2.X or higher


Excel file should have a Sheet with the name 'PhoneList' without the quotation marks (the character '$' is only needed in the VBA code to handle the SQL string


The Excel Sheet also needs the first two cells to be 'CompanyName' and 'Phone'


In the VBA code, should change the set line to:

Set doc = ActiveDocument 'ThisDocument


If the set is to ThisDocument, it will browse to the original template file fields, which are blank fields, so it will not work.


Thanks Susan for this tip, although we had to strugle a bit to get this to work :)

elyo10
elyo10

" How does Excel know what PhoneList$ is and where to put the rows?"that ist a very good question

Voaraghamanthar
Voaraghamanthar

Here is how I needed to change your code to fit my needs, but when I hit tab at the last FormField to start the macro, I get a syntax error in the "INSERT INTO" section, and who knows what will happen even after I get that syntax message fixed. Can anyone help with this code? Sub TransferToExcel() 'Transfer a single record from the form fields to an Excel workbook. Dim doc As Document Dim strStoreNumber As String Dim strSurveyorName As String Dim strSurveyDate Dim strStoreLocation Dim strContactPhone Dim strCableTestedValidated Dim strNumberGoodLines Dim strNumberBadLines Dim strSQL As String Dim cnn As ADODB.Connection 'Get data. Set doc = ThisDocument On Error GoTo ErrHandler strStoreNumber = Chr(39) & doc.FormFields("StoreNumber").Result & Chr(39) strPhone = Chr(39) & doc.FormFields("SurveyorName").Result & Chr(39) strSurveyDate = Chr(39) & doc.FormFields("SurveyDate").Result & Chr(39) strStoreLocation = Chr(39) & doc.FormFields("StoreLocation").Result & Chr(39) strContactPhone = Chr(39) & doc.FormFields("ContactPhone").Result & Chr(39) strCableTestedValidate = Chr(39) & doc.FormFields("CableTestedValidated").Result & Chr(39) strNumberGoodLines = Chr(39) & doc.FormFields("NumberGoodLines").Result & Chr(39) strNumberBadLines = Chr(39) & doc.FormFields("NumberBadLines").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 [SiteSurveyData$]" _ & " (StoreNumber, SurveyorName, SurveyDate, StoreLocation, ContactPhone, CableTestedValidated, NumberGoodLines, NumberBadLines)" _ & " VALUES (" _ & strStoreNumber & ", " _ & strSurveyorName & ", " _ & strSurveyDate & ", " _ & strStoreLocation & ", " _ & strContactPhone & ", " _ & strCableTestedValidated & ", " _ & strNumberGoodLines & ", " _ & strNumberBadLines _ & ")" 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=C:\Users\Documents\POS Survey Database.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

sparent
sparent

How does Excel know what PhoneList$ is and where to put the rows?

Editor's Picks