Transfer form data from Word to Excel

Microsoft Excel is a powerful spreadsheet database management application.If you are using a Word application to collect user data, it is necessary to transfer all of this data to an Excel spreadsheet for management.Besides, data conversion is a job with multiple records (Record) and field (Field) data.

But sometimes, you just need to transfer one record at a time. For example, you need to transfer a form of order data into a larger purchase database. This article will show you how to transfer data from Word to Excel.

The first step in the conversion process is to define four components: the data to be transferred, the source file containing the data, the target file you want to transfer data, the conversion implementation tool (VBA code) . For data, you can use a form of Word to collect data (input data) or can be a Web form, some other format.

When the work is completed, you define the dynamic elements in the target format. In this article using two elements, the name and phone number of a company, records are recorded in the record, you can see a simple example in the following figure:

Transfer form data from Word to Excel Picture 1

Once you have determined the destination data format, you need the data type of the source file, which can change the data type before transferring the data.Both fields in this data sheet are text using General format.Next, note the path to the destination file.In this example, both files are saved on a drive but in two different folders.

Next, you need to create the source form to collect data. The Word form is a document that contains empty cells called fields so that you can enter data. The field is a predefined cell that stores and accepts input data. This example uses a simple form that includes two Shipping Company and Phone fields. To create a form in Word, insert two text fields into the Word document as follows: click the Developer tab and then select the ab field in the Legacy Forms drop-down menu (in the Controls group). In Word 2003, select the Toolbar from the View menu and select Forms , which appears the toolbar containing the Text Form Field tool . Then, click Properties in the Controls group or double-click the field that is currently displayed to open its properties. In the Text Form Field Options dialog box , type txtCompanyName in the Bookmark property box, click OK . You do the same for txtPhone , then save the form.

Transfer form data from Word to Excel Picture 2

To add a code that converts a record from fields to a spreadsheet, open the Word form and then press Alt + F11 to launch the Visual Basic Editor (VBE). From the Insert menu, select Module and then enter the following code, then save and return to the Word form.

- Sub TransferToExcel ()

- 'Transfer a 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 được dùng để chèn một mục ghi tập tin đích đến.

- 'Don't omit the $ trong bảng identifier.

- strSQL = "INSERT INTO [PhoneList $]" _

- & "(CompanyName, Phone)" _

- & "VALUES (" _

- & strCompanyName & "," _

- & strPhone _

- & ")"

- Debug.Print strSQL

- Define kết nối chuỗi và mở kết nối để đặt tập tin workbook.

- Set cnn = New ADODB.Connection

- With cnn

- .Provider = "Microsoft.ACE.OLEDB.12.0"

- .ConnectionString = "Data Source = E: ExamplesSales.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

You can add a macro button to the Ribbon or a button to the document. However, by using the field in the form, you can skip the tools on the interface and execute the fields in the macro as follows: double-click the Phone ( txtPhone ) field to open the sheet's properties sheet. From the Exit list of the Exit section, select the conversion macro from the above code ( TransferToExcel ), click OK . If you press the Tab key, the Phone field will be executed, it will copy the text from the Company name and Phone fields to the Sales.xlsx file. Note, when applying the above code to your actual work, please update the path appropriately.

Before using the Word form, you should limit the changes to the fields in the form, by going to the Developer tab and clicking Restrict Editing in the Protect group. In Word 2003, click Protect on the Form toolbar. In the task pane that appears, check the Allow only this type of editing option in this document , select Filling In Forms in the drop-down list, click Yes , Start Enforcing Protection and double-enter the password and click OK .