Mediaboss Marketing

November 5, 2012, 2:13 PM PST

Takeaway: When you need to transfer a data record to Excel, a Word form and a little VBA makes the process a snap.

Data transfer often comes in bulk jobs with multiple records and data fields. But occasionally, youll need to transfer only one record at a time. For example, you might need to transfer details from a data entry order form to a larger purchasing database. In that case, you transfer details as each order is processed, one at a time. When youre facing such a task, you can use VBA code and you can set it all up in 10 quick steps.

There are several parts to a transfer task. The scenarios are unique, but the components are generally the same:

Youll need to identify these four components before you do a thing.

Our example uses a Word form to gather data (input values), but you might use a Web form, an Excel userform, or some other format. The transferring code and process will be mostly the same, regardless of the inputs format. This exercise is less about the source and more about the ability to transfer records, one at a time, to a destination file.

After ensuring that you have all the pieces you need to begin your work, determine the physical dynamics of the destination file. Usually, this format is predetermined. Well transfer two text elements, a companys name and phone number, record by record, into the simple Excel sheet shown in Figure A.

Once you know the format, note the data types the source file expects to receive. You might have to convert data types before actually transferring the data. We wont do so in this example. Both fields in this destination sheet are text using the General format. But its important to note this information before beginning because the data might need special handling. For instance, strings and dates must be delimited property.

The next bit of information youll need is the path to the destination file. In this example, both files will be on the same drive but in different folders. Some data must travel long distances to get from the source to its destination file, and youll need to know every node of that journey. If youre using a network, you might need to code in special permissions and passwords to use along the way. Our example destination workbook resides at E:Examples and isnt password protected.

Read more:
10 steps to transferring Word form data to an Excel sheet