TransferText Macro Action

  • Article

You can use the TransferText action to import or export text between the current Microsoft Office Access 2007 database (.mdb or .accdb) or Access project (.adp) and a text file. You can also link the data in a text file to the current Access database. With a linked text file, you can view the text data with Access while still allowing complete access to the data from your word processing program. You can also import from, export to, and link to a table or list in an HTML file (*.html).

** Note **  If you link to data in a text file or an HTML file, the data is read-only in Access.

** Note **  This action will not be allowed if the database is not trusted. For more information about enabling macros, see the links in the See Also section of this article.

Setting

The TransferText action has the following arguments.

Action argument Description
Transfer Type The type of transfer you want to make. You can import data from, export data to, or link to data in delimited or fixed-width text files or HTML files. You can also export data to a Microsoft Office Word 2007 mail merge data file, which you can then use with the Word mail merge feature to create merged documents such as form letters and mailing labels.

Select Import Delimited, Import Fixed Width, Import HTML, Export Delimited, Export Fixed Width, Export HTML, Export Word for Windows Merge, Link Delimited, Link Fixed Width, or Link HTML in the Transfer Type box in the Action Arguments section of the Macro Builder pane. The default is Import Delimited.

 Note   Only Import Delimited, Import Fixed Width, Export Delimited, Export Fixed Width, or Export Word for Windows Merge transfer types are supported in an Access project (.adp).
Specification Name The specification name for the set of options that determines how a text file is imported or linked. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported or linked text file.

To create a specification for importing or linking a text file:

  1. On the External Data tab, in the Import group, click Text File.
  2. In the Get External Data dialog box, enter the path of the source text file in the File name box.
  3. Click the option you want for storing the data (import, append, or link), and click OK.
  4. In the Import Text Wizard dialog box, click Advanced.
  5. Specify the options you want for this specification, then click Save As.
  6. Enter the name you want for the specification, then click OK.
  7. You can manage existing specifications by clicking Specs in the specification dialog box.
  8. Click OK to close the specification dialog box.

You can then type the specification name in this argument whenever you want to import or export the same type of text file.

You can import, export, or link delimited text files without typing a specification name for this argument. In this case, Access uses the defaults from the wizard dialog box. Access uses a predetermined format for mail merge data files, so you don't ever need to type a specification name for this argument when you export these types of files. You can use import/export specifications with HTML files, but the only part of the specification that applies is the specification for data type formatting.
Table Name The name of the Access table to import text data to, export text data from, or link text data to. You can also type the name of the Access query you want to export data from. This is a required argument.

If you click Import Delimited, Import Fixed Width, or Import HTML in the Transfer Type box, Access appends the text data to this table if the table already exists. Otherwise, Access creates a new table containing the text data.

You can't use an SQL statement to specify data to export when you are using the TransferText action. Instead of using an SQL statement, you must first create a query and then specify the name of the query in the Table Name argument.
File Name The name of the text file to import from, export to, or link to. Include the full path. This is a required argument.

Access creates a new text file when you export data from Access. If the file name is the same as the name of an existing text file, Access replaces the existing text file.

If you want to import or link a particular table or list in an HTML file, you can use the HTML Table Name argument.
Has Field Names Specifies whether the first row of the text file contains the names of the fields. If you select Yes, Access uses the names in this row as field names in the Access table when you import or link the text data. If you select No, Access treats the first row as a normal row of data. The default is No.

Access ignores this argument for Word for Windows mail merge data files because the first row must contain the field names.

When you export an Access table or select query to a delimited or fixed-width text file, Access inserts the field names of your table or select query into the first row of the text file if you've selected Yes for this argument.

If you are importing or linking a fixed-width text file and select Yes in this box, the first row containing the field names must use the field delimiter set in the import/export specification to separate the field names. If you are exporting to a fixed-width text file and select Yes for this argument, Access inserts the field names into the first row of the text file with this delimiter.
HTML Table Name The name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the Transfer Type argument is set to Import HTML or Link HTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked.

The table or list name in the HTML file is determined by the text specified by the <CAPTION> tag, if there's a <CAPTION> tag. If there's no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Access distinguishes them by adding a number to the end of each name; for example, Employees1 and Employees2.
Code Page The name of the character set used with the code page.

Remarks

You can export the data in Access select queries to text files. Access exports the result set of the query, treating it just like a table.

Text data that you append to an existing Access table must be compatible with the table's structure.

  • Each field in the text must be of the same data type as the corresponding field in the table.
  • The fields must be in the same order (unless you set the Has Field Names argument to Yes, in which case the field names in the text must match the field names in the table).

This action is similar to clicking Text File in the Import or Export group on the External Data tab. The arguments of the TransferText action reflect the options in the wizard started by the Text File command.

Tip

An import/export specification stores the information Access needs to import, export, or link a text file. You can use stored specifications to import, export, or link text data from or to similar text files. For example, you might receive weekly sales figures in a text file from a mainframe computer. You can create and save a specification for this type of data and then use the specification whenever you add this data to your Access database.

** Note **  If you query or filter a linked text file, the query or filter is case-sensitive.

To run the TransferText action in a Visual Basic for Applications (VBA) module, use the TransferText method of the DoCmd object.

See Also