Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: transfertext help needed

    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?

  2. #2
    Join Date
    May 2004
    Posts
    159
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Try using the FileSystemObject (reference MS Scripting Runtime)

    Along the lines of (aircode to start you off):

    Code:
     
    Dim fso as New Scripting.FileSystemObject  
     
    fso.MoveFile "C:\MyText.txt", "C:\MyText.xxx"
    	
    Set fso = Nothing
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2004
    Posts
    159
    Thanks I will give that a try. I see that the docmd.rename will not work as it will only rename access objects such as tables, macros, queries etc..

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by WilliamS
    Thanks I will give that a try. I see that the docmd.rename will not work as it will only rename access objects such as tables, macros, queries etc..
    Yep - you are playing around with the file system now
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2004
    Posts
    159
    I get an error- object undefined if I use that
    dim fso as New Scripting.FileSystemObject
    I have put some code together that almost works but gives me a type error
    Perhaps you could fix it..?

    Dim fso As AccessObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    strOutputPath = gstrPath

    statusMsg "Renaming Files"

    With Forms(gstrForm)
    strFileName = .txtEventFile
    strExportFile = strOutputPath & strFileName
    strNewFileName = strOutputPath & Left(strFileName, 8) & ".EVO"
    fso.MoveFile strExportFile, strNewFileName

    end with




    Quote Originally Posted by pootle flump
    Hi

    Try using the FileSystemObject (reference MS Scripting Runtime)

    Along the lines of (aircode to start you off):

    Code:
     
    Dim fso as New Scripting.FileSystemObject  
     
    fso.MoveFile "C:\MyText.txt", "C:\MyText.xxx"
    	
    Set fso = Nothing
    HTH

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    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

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    May 2004
    Posts
    159
    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

    strOutputPath = gstrPath
    statusMsg "Renaming Files"

    With Forms(gstrForm)
    strFileName = .txtEventFile
    strExportFile = strOutputPath & strFileName
    strNewFileName = strOutputPath & Left(strFileName, 8) & ".EVO"
    fso.MoveFile strExportFile, strNewFileName


    strFileName = .txtEpisodeFile
    strExportFile = strOutputPath & strFileName
    strNewFileName = strOutputPath & Left(strFileName, 8) & ".EPO"
    fso.MoveFile strExportFile, strNewFileName


    strFileName = .txtUnitFile
    strExportFile = strOutputPath & strFileName
    strNewFileName = strOutputPath & Left(strFileName, 8) & ".UNO"
    fso.MoveFile strExportFile, strNewFileName

    End With

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    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

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •