Import text file with type trx instead of txt

[richardb]

The following method would not work until I changed the file type to .TXT. A
message appeared saying (I think) "Cannot import this file."

DoCmd.TransferText acImportFixed, "Transaction Import", "tblMyTable",
"G:\MyFolder\01202005.TRX"

Is there any way to make this file import method work when files are of a
type other than TXT?

Thank you...

 

[Douglas J. Steele]

See http://support.microsoft.com/?id=306144

 

[richardb]

I tried the code example from the article and it worked just fine. That will
be better for me than modifying the registry because I will be distributing
my program to end-users. Thank you again...

 

[richardb]

The code in the KB article that you referenced worked fine for importing the
file. Now I need to export the text file with the same trx type to a
different folder. However, Access will not allow me to do that unless I use
txt as the output file type. I could not find another KB article about
exporting to a file name type that Access does not recognize. Can you help me
again please?

 

[Douglas J. Steele]

Export it to a file name ending .txt, then rename the file when you're done.

On rereading that KB article, they've made it far more complicated than is
really required. There's no reason to use FileScriptingObject: VBA has
built-in commands to copy a file (the FileCopy command) and to rename it
(the Name command)

 

[richardb]

I also tried adding my trx type to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\
DisabledExtensions as advised in Greg Stemp's article in the MSDN library
"Much ADO About Text Files" but that did not help to export to text files
with extension trx. (I haven't yet restarted the computer so maybe it will
still work.)

Based on your suggestion to export as txt and then rename to trx, I am
wondering if you can help me with code to find all files with extension *.txt
in a given folder, kill all files with the same name but extension trx (in
case they modified and re-exported a duplicate file) and then rename the txt
file to trx. This has to run transparently to the user when she clicks one
button to export her edited table. He/she cannot go into explorer and do the
renaming.

Thanks. I will try to figure this out myself in the mean time.

 

[richardb]

The modifications to registry worked after I restarted the computer (duh!),
but I'd still like to avoid modifying the registry at my distributed
installations, so will look for the file renaming code (txt to trx) that I
asked about my last post. Thanks...

 

[richardb]

Real sorry about so many replies, but here are my latest questions (also my
on-line help doesn't find these methods unless I write them out and click F1.
It probably needs to be re-installed):

Found syntax for FileCopy and that will work for me. Could not find a VBA
Name command, however. Does it have another name? Also is there a built in
VBA Kill command to delete a file?

 

[Douglas J. Steele]

http://msdn.microsoft.com/library/en-us/office97/html/output/f1/d6/s5b273.asp

(Don't worry that that's the Access 97 help file: the Name statement has not
changed over time)

 

[Douglas J. Steele]

Untested air-code:

On Error Resume Next

Dim strFile As String
Dim strFileTRXExt As String
Dim strFolder As String

strFolder = "C:\MyData\MyFolder\"
strFile = Dir$(strFolder & "*.txt")
Do While Len(strFile) > 0
' Take off the last 4 characters and replace them
' with .trx
strFileTRXExt = Left$(strFile, Len(strFile) - 4) & ".trx"
' Kill the file ending in trx
' (The On Error Resume Next will keep an error
' from being raised if the file doesn't exist)
Kill strFolder & strFileTRXExt
' Rename the file with the .txt extension
Name strFolder & strFile As strFolder & strFileTRXExt
' Get the next .txt file
strFile = Dir$()
End While


Hopefully there's no word-wrap in the above!

 

[richardb]

OK that was helpful. With a combination of Name, Kill and Dir functions, I'll
be able to export a txt file, test if the same file was previously exported
and renamed, and then rename the new export to type trx. Now my problem is
that the exported table writes out with hyphens surrounding the rows and
columns. Here's what I'm using:

Dim strOutputDir As String
strOutputDir = "G:\MyTest1.txt"
DoCmd.OutputTo acOutputTable, "tblTransactions", acFormatTXT, strOutputDir,
False, 0

I have a "template" that I created by manually exporting my table. It is
called "transactions output", but I can't figure out where to put it in the
syntax, or if that's the solution. Can you move me along in this learning
curve please?

 

[Douglas J. Steele]

Don't use OutputTo. Use TransferText.

http://msdn.microsoft.com/library/en-us/vbaac11/html/acmthactTransferText_HV05186521.asp

 

[richardb]

That does it...thanks...

Don't use OutputTo. Use TransferText.

http://msdn.microsoft.com/library/en-us/vbaac11/html/acmthactTransferText_HV05186521.asp