1: Analyze your needs
There are several parts to a transfer task. The scenarios are unique, but the components are generally the same:
The data you're transferring
The source file that contains the data
The destination file to which you're transferring the data
The transfer medium used to make the switch; usually, it's code
You'll 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 input's format. This exercise is less about the source and more about the ability to transfer records, one at a time, to a destination file.
2: Determine the destination format
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. We'll transfer two text elements, a company's name and phone number, record by record, into the simple Excel sheet shown in Figure A.
Our example sheet is simple on purpose. Transferring the data is the job; the number of fields is usually irrelevant.
3: Identify the destination data types
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 won't do so in this example. Both fields in this destination sheet are text using the General format. But it's important to note this information before beginning because the data might need special handling. For instance, strings and dates must be delimited property.
4: Note the destination file's location
The next bit of information you'll 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 you'll need to know every node of that journey. If you're 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 isn't password protected.
5: Create the source form
If you're lucky, you'll have some flexibility when choosing the source format (but not always). In this case, we'll use the simple Word form shown in Figure B to collect two pieces of data.
Use Word's form fields to collect data.
A Word form is a document that contains fill-in blanks called fields. Each field is a predefined cell that stores data input. To create the example Word form, insert two text fields into a blank Word document as follows:
Click the Developer tab and then choose the ab field from the Legacy Tools drop-down in the Controls group (circled in Figure B). In Word 2003, choose Toolbars from the View menu and select Forms, where you'll find the Text Form Field control.
Click Properties in the Controls group or double-click the field to display its properties.
Enter txtCompanyName in the Bookmark property, as shown in Figure C.
Repeat steps 1 through 4, entering txtPhone in step 3.
Save the form.
The text form field is a legacy tool in the Ribbon versions.
6: Add the basic code
To add the code that transfers a single record from the fields to the example workbook, do the following:
With the Word form open, press [Alt]+[F11] to launch the Visual Basic Editor (VBE).
From the Insert menu, choose Module.
Enter the code in Listing A.
Save the module and return to the Word form.
Listing A: The transferring macro
'Transfer a single 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
Set doc = ThisDocument
On Error GoTo ErrHandler
strCompanyName = Chr(39) & doc.FormFields("txtCompanyName").Result & Chr(39)