TransferText(FileName)

[CP]

Hi: I have a Macro which Exports a table to my C: drive. For the File Name
I have a specific name, but is there a way to automatically update that name
to something different with a promt or other feature every time I Export the
file? Instead having to go to FileName in the Macro and changing it every
time? Thanks.

 

[SacCourt]

DoCmd.TransferText acExportDelim, "Standard Output", "External Report",
"C:\Txtfiles\N" & Format(now(),"YYYYMMDD") & ".txt"

The File_Name_Directory is a string, so you can build it with variables.

DoCmd.TransferText acExportDelim, "Standard Output", "External Report",
"C:\Txtfiles\N" & me.FieldName & ".txt"

Foundation

TransferText Method
See AlsoApplies ToExampleSpecificsThe TransferText method carries out the
TransferText action in Visual Basic.

expression.TransferText(TransferType, SpecificationName, TableName,
FileName, HasFieldNames, HTMLTableName, CodePage)
expression Required. An expression that returns one of the objects in the
Applies To list.

TransferType Optional AcTextTransferType.

AcTextTransferType can be one of these AcTextTransferType constants.
acExportDelim
acExportFixed
acExportHTML
acExportMerge
acImportDelim default
acImportFixed
acImportHTML
acLinkDelim
acLinkFixed
acLinkHTML
If you leave this argument blank, the default constant (acImportDelim) is
assumed.

Notes

Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or
acExportMerge transfer types are supported in a Microsoft Access project
(.adp).


SpecificationName Optional Variant. A string expression that's the name of
an import or export specification you've created and saved in the current
database. 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, linked, or exported text file. To create a schema file, you can use
the text import/export wizard to create the file. For delimited text files
and Microsoft Word mail merge data files, you can leave this argument blank
to select the default import/export specifications.

TableName Optional Variant. A string expression that's the name of the
Microsoft Access table you want to import text data to, export text data
from, or link text data to, or the Microsoft Access query whose results you
want to export to a text file.

FileName Optional Variant. A string expression that's the full name,
including the path, of the text file you want to import from, export to, or
link to.

HasFieldNames Optional Variant. Use True (–1) to use the first row of the
text file as field names when importing, exporting, or linking. Use False (0)
to treat the first row of the text file as normal data. If you leave this
argument blank, the default (False) is assumed. This argument is ignored for
Microsoft Word mail merge data files, which must always contain the field
names in the first row.

HTMLTableName Optional Variant. A string expression that's the name of the
table or list in the HTML file that you want to import or link. This argument
is ignored unless the transfertype argument is set to acImportHTML or
acLinkHTML. If you leave this argument blank, the first table or list in the
HTML file is imported or linked. The name of the table or list 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, Microsoft Access distinguishes them by adding a number to the end
of each table or list name; for example, Employees1 and Employees2.

CodePage Optional Variant. A Long value indicating the character set of
the code page.

Remarks
For more information on how the action and its arguments work, see the
action topic.

You can leave an optional argument blank in the middle of the syntax, but
you must include the argument's comma. If you leave a trailing argument
blank, don't use a comma following the last argument you specify.


Note You can also use ActiveX Data Objects (ADO) to create a link by using
ActiveConnection property for the Recordset object.


Example
The following example exports the data from the Microsoft Access table
External Report to the delimited text file April.doc by using the
specification Standard Output:



DoCmd.TransferText acExportDelim, "Standard Output", _
"External Report", "C:\Txtfiles\April.doc"

 

[Steve Schapel]

CP,

Yes. For example, one option would be to put an unbound textbox on a
form which will be open at the time that you run your export, where you
can enter the name of the file. If you are running the macro from an
event on this form, such as the click of a command button, then in the
File Name argument of the textbox, you can enter something like this...
="C:\YourFolder\" & [NameOfTextbox]
Another option, for example, is you could name the file with the current
date to identify it, so the File Name argument could be something like
this...
="C:\YourFolder\Export" & Format(Date(),"yyyymmdd") & ".txt"

 

[Chris Reveille]

In the file name put
= InputBox("enter the path and filename:")

this will promt the user for the file name