Auto Remembering File Names

[Guest]

Being new to Access, I'm trying to setup macros to import text files, one at
a time, into access using a specified format, then immediately output them in
Excel format. However, each text file has a unique name which I want to use
in the name of the output file. Unfortunately, Access requires a file name
be identified when using the "OuputTo" command. How can I get Access to
remember this filename during import, then use it to select the this table
for export into Excel?

 

[John Nurick]

Hi Kerry,

I don't quite understand what you're asking. Are you (1) trying to
remember the name of the *table* that contains the data imported from
the text file, in order to select the right table to export to an XLS
file - or (2) trying to remember the name of the text file you imported
in order to give the XLS file a corresponding name?

If (1), I think you can do it with a macro by using the same name for
the table every time.
Step 1: link (rather than import) the text file, using a fixed table
name such as tbl_TEMP_IMPORT
Step 2: output tbl_TMP_IMPORT to Excel format
Step 3: delete tbl_TEMP_IMPORT.

If (2), I doubt whether it's possible with a macro. You'll need to write
VBA code to do something like this:

1) get the file name from the user (there's code at
http://www.mvps.org/access/api/api001.htm to use the standard Windows
File Open dialog for this)

2) link (rather than import) the text file using DoCmd.TransferText

3) trim the .txt from the end of the file name and substitute .xls

4) export the data to Excel (often DoCmd.TransferSpreadsheet is
preferable to DoCmd.OutputTo)

5) delete the linked table.


On Wed, 13 Jun 2007 12:15:00 -0700, Kerry Westervelt <Kerry

 

[Guest]

Is Access going to do anything with this data? If not, why involve Access?
You can just as easily import text files directly into Excel.