Office hacks: Excel to play cross-border Word data collection and entry more easily

We often have to issue some basic information questionnaires at work, they need to be made in Word, but after retrieving them, they have to be entered into Excel one by one in order to process them, which is a huge workload and prone to errors. There is no good way to automatically enter the data into a unified it?The biggest advantage of Office is the linkage, especially from the 2013 version, PPT, Word, Excel, the trend of integration of functions in each other more obvious. I have Office 2016 as an example, to share a Word document to collect information, using Excel to automatically extract the data method.


First of all, in accordance with normal work requirements, using Word to create information collection form, after the production of modifying the information to be filled in the box, switch to the development mode tab, click on the “Aa” format text content control (if there is no development mode tab, you need to click “File → Options → Customize Ribbon “, check “Development Tools” on the right side, you can appear this option).


Next, click the message box, and then click “Design Patterns” in the Development Tools. In the pop-up dialog box, enter the title, which is the message text of the message box, if there is demand, you can also modify the border color, text style and so on.



Each information box are made after the corresponding changes, click the development mode, “Restrict Editing”, in the right column of Word, check the “Restrict formatting of selected styles” and “only allow this type of document in the Modify” and check the “fill in the form”, Word part is set up, and then just save and send the file to the person whose information is collected.


After the return of the survey, we began to use Excel batch automatically extract Word fill in the information. Open Excel, the same switch to the Development Tools tab, tap the leftmost “Visual Basic”. Double-click Sheet1, fill in the macro code (also available from the Web site download, link:, extraction code: 1iri):.



Note that after transcribing the macro code, do not rush to close, click the current Visual Basic interface above the toolbar “Tools → References”, in the “available references” to find the ” Microsoft Word 16.0 Object Library” and check OK (Note: Word 2013 and other versions of the name is slightly different).



After returning to the main Excel interface, or in the Development Tools tab, click on “Macro”, select the macro command just edited, click “Run”, then Excel will automatically pop up the file selection box, find the information to be extracted Word document, click to open, Excel can begin to automatically extract information. In this way, the work of data excerpting completely rely on the computer automatically completed, not only greatly save the work time, but also to avoid the chance of manual excerpting errors.


Leave a Comment