I'm trying to use the transfertext method to copy a table to a text file.
The problem is that I need the text file to have it's own special .ext
E.G. 200509.evo The transfertext insists on doing it and leaving a .txt extension. I have put the new extension in a variable and put it into the equation but I get an error that the database is read only.
DoCmd.TransferText acExportFixed, "EventsExportSpec", _
"tblORYXEvents", strExportFile, False
The above will not work if I specify my own extension in strexportfile
How do I get it to accept the different extension?
Do I have to rename the darn thing in a separate function?
Nothing?? The problem seems to be that when defining an text export definition it then only will produce a .txt or .acl extension - this is done right at the beginning when selecting the export type. Is there a way to edit the schema to insert any chosen extension as acceptable with the text type?
Failing that -got a simple and fast function for renaming a file that allows path strings to be used and removes the .txt? right now I'm thinking of a
docmd.rename (srpath & strfile), (strpath & left(strfile),7 & ".evo) type of thing.
I have files like 200508EVO.txt that need to become 200508.EVO.
Not sure right now if access will allow me to change a .txt extension without complaining aout it.
My code will work with the correct reference (In the VBA IDE click Tools--> References and scroll down to Microsoft Scripting Runtime - tick this option).
Your code is (incorrectly as you know) late binding. You shouldn't do this within compiled code (as per Access) unless absolutely necessary. In this case, it is far from necessary. You will, using the reference, get a performance benfit and you can use the Intellisense features of VB too
You are right I did not have scripting as a reference. The code works now which is great but I do have a question. I have to run this on three files and do I have to set fso to nothing after each run or just at the end?
here is the working code as of now- it runs pretty fast as you said
Dim strOutputPath As String
Dim strExportFile As String
Dim strNewFileName As String
Dim strFileName As String
Dim fso As New Scripting.FileSystemObject
You only destroy the reference once you are done with it. Typically, although you can destroy it as soon as you have finished with it, most people destroy all references at the end of the proc (unless there is a requirement to destroy it earlier). Of course, there will be those that don't - very much what you prefer.
Personally, I stick them into the Exit part of an error trap, just before the end of the proc.
Anyhoo - you can use the same fso to move as much stuff as you like around so no need to destroy after each use