“Can I export the form data into Excel?”
This was an excellent question and I informed the student that I would do some research on the subject and mail the group the answer to the question. Being able to take data from a form that was created and easily importing into Excel makes perfect since. What good is a form if you still have to manually input the data from the form into your spreadsheet? I mean why both with the form all together right?
Using my best online friend, Google, I searched for the answer to the question. Almost immediately came across an article on TechRepublic that answered my question. Below is the step by step instruction on how to transfer data from Word forms into an Excel worksheet. Using this method you can avoid the hassle of manually importing Word form data into Excel.
Obviously, you need to have a create form already done. For those of you who have taken my Lunch & Learn, you know how easy it is to actually create a form in Word. You can take the form you created for your homework and tweak it a bit. You will need to use one of the legacy form controls, primarily the Text Form Field, the one that gives you the gray boxes. See example below:
The first step is to take the data from the Word form. But you have to tell Word to save the form data. Follow these steps to save the data in each completed form to a text file that can be imported into Excel.
1) Open one of the completed forms
2) Click the Office Button, click Word Options, then click Advanced, then scroll down to “Preserve Fidelity When Sharing this Document” and click “Save Data As Delimited Text File”
3) Click OK
4) Save the file as a .txt file
5) When the File conversion dialog box appears click OK (see example)
You can now import the data in the text files into a spreadsheet by following these steps.
1) Open a blank worksheet in Excel
2) Go to the Data Tab and in the “Get External Data” group, click the “From Text” icon
3) Select the data text file
4) Select the Delimited option and click Next (see example)
5) Clear the Tab check box and then select Comma check box
6) Click Next and then Finish
7) Click in cell A1 and then click OK
To import the second text file, you just open the same Excel worksheet and click in the second row below the last row of data. As and FYI, the Wizard forces you to skip a row each time you add a new row of data, you can delete these blank rows later.
Give this example of try and let me know how it works for you. Also, let me know how you might be able to use this for data you may collect at work. I would be very interested to see how you can incorporate what you learn from the Lunch & Learn and this post.
Thank you
-Bill Stephens
