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:
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.
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 .
You should read it
- How to copy Word data to Excel keeps formatting
- How to convert a Word file to Excel
- Create reports automatically in word from Excel's data sheet
- How to insert a Word document into an Excel file
- How to use Excel spreadsheets in Microsoft Word
- Instructions to copy data from Excel to Word retain the format
- How to Convert Excel to Word
- How to convert Cad to Word, PDF, Excel
May be interested
- Mix data from Excel to Wordthe following article shows how to merge data from excel to word 2013.
- How to make a recruitment form with Excel quicklyexcel recruitment form is a convenient tool to help manage candidate information effectively. this article provides detailed instructions on how to create a recruitment form in excel with simple and easy-to-follow steps.
- How to scroll bar scrolls horizontally and vertically in Word and Excelbecause of accidentally changing certain settings on word or excel, the scroll bar scrolls horizontally, vertical scrolling suddenly disappears. this sometimes makes it difficult for users to conduct text content processing, or excel data spreadsheets.
- How to convert a Word file to Excelyou have a word file but you want to switch to excel to make it easier to calculate and process data. but if you enter it again, it will take a lot of time, so you are looking to convert word files into excel. below tutorial guide to you two c
- Microsoft introduced a new tool to turn an image table into an editable Excel tableat the ignite 2018 event, microsoft has demonstrated a new feature called insert data form picture that allows users to turn a snapshot of a printed or hand-drawn data sheet into an editable, 'soft' table on excel
- How to use Excel spreadsheets in Microsoft Wordlike peanut butter and chocolate, a word processor with some basic spreadsheets is a great combination. when you no longer want normal spreadsheets in text documents, microsoft word is now able to do this thanks to the built-in feature from the excel sister program.
- How to use Form Controls in Excellearn more about form controls in excel, instructions on how to use form controls in excel in the most detailed and easy to understand
- 7 ways to transfer data between 2 computersguide you how to transfer data between two computers using software, cloud storage services, let's learn 7 ways to convert data quickly!
- How to align text in a cell in Exceljust like on word, users can align text in excel cells, making the distance between the letters more evenly.
- How to recover Word, Excel files when deleted with Data Recovery Wizardthis sharing article will guide how to get word, excel files when deleted from your computer is very simple. when you lose excel or word files on your computer or external devices such as usb flash drives, portable hard drives, a simple way is to use the data recovery wizard software.