Move Access Data to a Word Processor or Text Editor


You can easily send Access records to a word processor (most notably, Microsoft Word) or a text editor. Sometimes you want the records in a file type such as RTF or DOC, to preserve table formatting. Other times you want the records to be in TXT files, which contain no formatting so they can be easily manipulated or re-exported to another program or computing environment.

Perhaps the most important way Access and Word are used together is in a mail merge to create form letters. (The term form letter is often used pejoratively, but here I simply mean letters with similar content sent to a number of recipients.) The increasingly automated mail-merge tool in Microsoft Word has made form letters easier than ever to create. But the process is still not completely intuitive, so I have provided an extended exercise on mail merges in the Case Example at the end of the chapter.

In this section, I focus on the various ways you can move Access records to word-processing and text files.

Cut and Paste

Sometimes you want to move just a few records from Access to Word. An easy way to do that is copy and paste. If you paste records into Word, you can do a plain-vanilla paste or use the Paste Special command to modify the formatting.

Plain Vanilla

Let's try a plain-vanilla copy and paste first.

1.

Download the NiftyLionsChap13.mdb file from the companion website and open the database.

2.

From the Database window, open the tblCustomers table.

Note that the First Name column is rather wide for the relatively short values it holds.

3.

Select the first four records.

4.

Right-click and choose Copy.

5.

Open Word. Right-click and choose Paste to insert the records.

Access pastes the records as a table that includes the table name and field names. You can easily delete these elements, as described in the next step.

Also note that the column widths have been resized to fit the available space in Word.

6.

In Word, highlight the first two rows, which contain the tblCustomers title and field names. Right-click and choose Delete Rows (see Figure 13.1).

Figure 13.1. A simple cut and paste of Access records into Word.


7.

Save the Word document as PasteCustomers.doc. Close the document.

NOTE

You could have accomplished the same task by using drag and drop. Open Word and then select the records in the tblCustomers table. Move the mouse directly below any column selector. When the mouse pointer becomes a white arrow (see Figure 13.2), drag the rows to the Word button on the Windows taskbar. Windows switches to Word, and you then can drop the selected records into the document. Alternatively, you can tile Access and Word windows, and then drag the records from Access to Word. Overall, I think copying and pasting is easier.

Figure 13.2. To drag and drop records, look for the white arrow pointer.



Paste Special

Now let's take a look at the choices offered by the Paste Special command in Word.

1.

If you haven't done so yet, open the tblCustomers table and select the first four records.

2.

Right-click and choose Copy.

3.

Switch to Word and choose Edit, Paste Special (see Figure 13.3).

Figure 13.3. The Paste Special command gives you more flexibility than a simple Paste.


You have the following choices:

  • Formatted Text (RTF) This choice pastes the rows with the same formatting and column widths as those in the original table. The table title is not included.

  • Unformatted Text The records are pasted as plain text, not as a table. All formatting (such as italics) is dropped. Tab stops separate the values in the fields.

  • HTML Format The records are pasted with HTML formatting. Columns are resized to fit the width of the document.

  • Unformatted Unicode Text Similar to the Unformatted Text option.

4.

Click Formatted Text (RTF) and click OK.

The records are pasted with the original column widths.

5.

Save the file as PasteSpecialWord.doc and close it. Close the tblCustomers table.

NOTE

In Microsoft Office 2002 and 2003, you can also use the formatting commands on the Paste Options button, shown in Figure 13.4. (If the Paste Options icon is not displayed after you paste, in Microsoft Word, choose Tools, Options, Edit tab and select the Show Paste Options Buttons check box.) Unraveling their mysteries is beyond my scope, but you can experiment with them to see if they help you deal with Word formatting issues.

Figure 13.4. The Paste Options button offers additional choices for formatting pastes of Access records.



Office Links

A quick and easy way to export records to Word is to use the Office Links command. The records are exported into a new RTF file, which maintains the formatting of the original file.

Let's try an example that exports an Access table.

1.

In the Database window, select tblOrders, but don't open it.

2.

Choose Tools, Office Links, Publish It with Microsoft Word.

3.

The table opens in a new file with the table name and an RTF extension.

4.

Close the tblOrders.rtf document.

By default, the file is stored in your My Documents folder.

Export Wizards

The most robust tool for exporting Access data is, naturally enough, the Export command, available from the File menu in the Database window. When you issue the command, it initiates a wizard that takes you through the export process.

As with Office Links, you export an Access table to Word as an RTF file instead of as a DOC file (a Word document). But that's not really a problem: You can always save the RTF file as a DOC file. Notably, many word processors can handle documents with the more flexible RTF format.

New computer users quickly learn to distinguish between rich-text (RTF) files and plain-text (TXT) files. A rich-text file keeps the formatting included in an Access table; a plain-text file strips it away. Sometimes you might find this to be a distinction without a difference; you merely want to get Access values into some kind of word processor or text editor. Afterward, you can change the formatting to fit your needs.

But exporting to a rich-text file and exporting to a text file are two quite different things because, as you'll see, their purposes are different. Let's export a table, first as an RTF file and then as a TXT file.

Export a Table as an RTF file

To export a table as an RTF file, simply do the following:

1.

In the Database window, click Tables and select tblOrderDetails.

2.

Choose File, Export.

3.

Select a convenient folder to store the new file, or just leave the default setting of My Documents.

4.

In Save As Type, open the drop-down list and choose Rich Text Format (.rtf).

The selection is near the bottom of the list.

5.

In the File Name box, change the suggested name to tblOrder DetailsExport.rtf and click Export.

6.

Open the file to see your results, and then close it.

The RTF file you created gives you the same results obtained using Office Links. When you use the Export command, however, the file doesn't open automatically.

NOTE

You can use the same basic steps to export to an HTML file. In step 4, open the drop-down list and choose HTML Documents (*html;*htm,).


Export a Table as a TXT File

You usually export an Access table as a text file so that the records can then be easily imported into some other program or environment. The absence of formatting in text files eliminates one headache in importing because the content contains no formatting characters or instructions to deal with. On the other hand, the absence of formatting means you must use some other means to distinguish records and fields from another.

Distinguishing one record from another is handled easily enough: A different record appears on each line. To separate columns, you either include a delimiter or choose fixed-length columns. Delimited text files are more common (and I think easier to work with), so I focus on them.

What is a delimiter? It's an uncommon word for some common bit of punctuation, such as a comma or semicolon. In an Access table with fields for last name, first name, address, and so on, the punctuation mark separates last name from first name, first name from street address, and so on (see Figure 13.5).

Figure 13.5. In this delimited text file, the commas separate one value from another, and the text qualifier of quotation marks encloses each value.


What happens if that bit of punctuation, such as a comma, appears within the values themselves? In other words, how do you distinguish between a comma that separates street addresses from cities and a comma such as the one in the book title Captain Newman, MD? For the commas in values, you need a text qualifier (such as quotation marks) that maintains the value as a single text string (as in "Captain Newman, MD"). The text qualifier also keeps the entire value together, spaces and all.

My discussion makes delimited text files sound more complicated than they are. An example will you show much more readily how they work.

1.

In the Database window, choose tblMerchandise.

2.

Choose File, Export.

3.

Navigate to a convenient folder on your hard drive. In Save As Type, choose Text Files (*.txt; *.csv; *.tab; *.asc). Keep the filename of tblOrders. Leave the Save Formatted option unchecked.

4.

Click Export.

5.

In the first dialog box of the Export Text Wizard, the Delimited selection should be chosen. The Sample export format box shows the values that will be exported.

6.

Click the Advanced button to open the Export Specification dialog box (see Figure 13.6). Key settings include these:

  • Field Delimiter You can choose which character separates values in a record.

  • Text Qualifier This is the character that keeps text strings together.

  • Date Order Open the drop-down list to see the various choices you have for Date/Time fields.

Figure 13.6. In the Export Specification dialog box, you can choose a delimiter and a text qualifier. These settings can be saved for future use in exporting and importing.


7.

Keep the default settings. Click Save As.

You might want to use the same settings for future exports (and imports of delimited text files as well). You can save the set of specifications you just made for future use.

8.

Keep the suggested title in the Save Import/Export Specification dialog box and click OK.

9.

Click Specs.

In the Import/Export Specification dialog box, you see the name of the specifications you just saved. In future exports or imports of delimited text, you can apply the same setting by selecting it and clicking Open.

10.

Click Cancel. Click OK in the Export Specification dialog.

11.

Click Next. You see the dialog box in Figure 13.7.

Figure 13.7. In this dialog box, you can see how the values will be affected by choosing various delimiters.


The delimiter and text qualifier selected in the Export Specification dialog box are displayed. If you only want to choose a delimiter or text qualifier, you can do it here and ignore the Export Specification dialog box. You can also choose to include the field names by selecting this option.

12.

Click Next and click Finish. Click OK. Open the text document tblMerchandise.txt to see your data (see Figure 13.8).

Figure 13.8. Access exported the table with the comma delimiter between fields; quotation marks are used as text qualifiers.


Zeroing Out the Zeroes

When you export values in fields with the Date/Time data type to a delimited text file, both the dates and the times are exported. But often in a Date/Time field, the values just indicate datesthe times are inconsequential or irrelevant. If there are no times accompanying the dates, you have a problem: Access attaches a string of zeroes, shown as 0:00:00, to each Date/Time value (see Figure 13.9).

Figure 13.9. In the delimited text file, zeroes have been attached to each date/time value.


Simply setting the Format field property in the table to Short Date or some other setting that eliminates the time display won't solve the problem. You need to create a query using the fields from the tables, and then export the query to the text file. Instead of using the original date field, you'll create a calculated field in the query that wipes out the zeroes. For example, for an OrderDate field, you can use this expression:

 OrderDate2: Format([OrderDate],"mm/dd/yy") 

where:

  • OrderDate2 is the name of the column.

  • Format is the function.

  • OrderDate is the field.

  • "mm/dd/yy" is two-digit month, day, and year formatting.

When you export the query, there won't be any 0:00:00 strings in the OrderDate field.





Hands-On Microsoft Access(c) A Practical Guide to Improving Your Access Skills
Hands-On Microsoft Access: A Practical Guide to Improving Your Access Skills
ISBN: 0321245458
EAN: 2147483647
Year: 2005
Pages: 169
Authors: Bob Schneider

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net