|
Gathering information into a Word form from users or clients is a great way to help you accumulate important data that you can further analyze. For example, suppose you have customer questionnaires or employee forms that you need entered into databases. You send the form to your users or clients and ask them to tab through the document to complete the requested information and email the completed Word form back to you.
Once you gather this data, many people assume they need to take time to key that information into an Excel spreadsheet or Access database to do trend analysis. Although you probably will want to add it to a program that has more analysis power than Word eventually, you don't have to rekey the data from scratch. In this article, I show you how to save a few keystrokes and ensure accuracy by not retyping the data.
If you have a lot of forms you need to process, you may be better off either learning how to use ADO (ActiveX Data Object) to create a code connection to the spreadsheet or database to automate the data transfer process, or hire someone (like me) who specializes in this type of coding to do the job for you and save you hours of work and lots of money.
Avoid Retyping by Exporting
However, if you only have a few forms you need to process at a time and you manually transfer the data by retyping, this article can help you save a little time and money.
Here's an example. The figure below shows a sample form I've filled in with data for an imaginary software installation request.
In this case, the order form isn't linked to a database or spreadsheet, but you need to enter this sale into another application so you can calculate your monthly sales figures.
Instead of printing this page and reentering the data, you can export out only the data that is within the form fields. Then if you have a spreadsheet or database properly created to accept this type of data, you can just import the data into the other application. Not only is this approach faster than retyping, but it also helps to ensure that the information passed is accurate. You don't have to rely on good proofreaders to check it. Whatever information was typed into the form will be exported and ultimately imported.
Export the Data from Word
To export the data out from Word, have the form open and then click Tools | Options | Save and check the option for Save data only for form.
When you save the file, be sure to select Text as the file format. When you do, a new dialog box appears that asks you to choose details about the type of export you want. Unless you have special needs, you should be safe accepting the defaults.
In the image below, note that there are commas between each separate piece of data and quotes around each individual item (it is a comma delimited format).
If you look at the actual text file, as you can see in the image below, the data in the file is the same as it shows in the export dialog box from Word.
Now you can import this data into a database or spreadsheet. Here is a quick look at how the import works in Access and Excel.
Import the Data into Access
With Access, first you need to at least create a blank database if you don't have one prepared. Now click File | Get External Data | Import. You may have to click the drop down to set the default file type to the type of file you are using (.txt) so you can see the file in the dialog box.
An import wizard appears and walks you through a few panels. You have to answer questions about the data you are importing and make specific choices regarding how the data should be imported and displayed.
If this is a new database, import the data into a New Table. However, if you have a database already created, make sure you choose the correct table that holds the data layout format you need. Then import the data.
As you can see below, the data is now imported. If you need to make any adjustments to the headings or format, you can move to the design mode and make the necessary changes. Then save your database. The next time you need to add more data, just choose to add it to this same table. Eventually, you'll have a database filled with data accumulated from your Word forms, without retyping anything.
Import the Data into Excel
Importing into Excel works in much the same way, but there are a couple of glitches. The process starts out about the same as the Access import. Click Data | Import External Data | Import Data. Unfortunately Excel 2003 isn't as good as Access at recognizing the import format. Pay close attention to the choices. Often Excel seems to assume your commas are spaces or tabs, so be sure to make the proper choice.
The data import into the first row of the spreadsheet, as shown below. However, Excel seems to have a minor bug. When you import a single row of data (as you often will when you save forms to text files), Excel seems to disable the next row. If you attempt to import another file into row 2, you'll discover that the Import Data option is now grayed out!
Although, you can work around the bug in a couple of ways, the easiest method is to skip down to row 3 and import your new data into that row. Then just delete row 2 so the data moves into the correct place. (Thanks to MVPs Bill Manville and Jon Peltier, as well as MOS Linda Johnson for helping me with this issue.)
Either way, importing your form data into Excel or Access from Word will save you from having to retype the data manually and will help to ensure a more accurate data transfer.
To learn more about creating forms in Word, see my free tutorials here: http://www.mousetrax.com/techpage.html#autoforms. Or if you'd prefer to have a custom solution created for one-click data form transfers, see my consulting page here: http://www.mousetrax.com/consulting.
|
Department
