Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append to a text file (2000)

    I need to create a text file as output from a query. I have used the following code to create the output file:

    DoCmd.TransferText acExportDelim, , "DataToTransfer", "cutput.txt"

    The problem is that if the file exists already, it will be over written.

    Can I append to this file rather than overwriting?

    I also need to check that the file isn't in use before I append to it as it is used as input by another application and deleted when the application has processed it.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append to a text file (2000)

    TransferText will always overwite an existing file, so you should export to a new file, then concatenate it with the original one, for example using Shell:

    Shell "cmd /c copy fileA + fileB fileA"

    Some applications lock a file when they open it, others don't (Notepad, for instance, loads a file into memory but doesn't lock it, so you can delete the file from disk while it is open in Notepad). The following function tests if a file is locked:

    Function IsFileLocked(strFilename As String) As Boolean
    Dim f As Integer
    f = FreeFile
    If Dir(strFilename) = "" Then Exit Function
    On Error Resume Next
    Open strFilename For Binary Access Read Write Lock Read Write As #f
    Close #f
    IsFileLocked = Err
    End Function

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append to a text file (2000)

    Many thanks Hans
    If I use the Shell "cmd /c copy fileA + fileB fileA" command, what will happen if the fileA is already being read/deleted at the time I try to append fileB ?

    FileB will always be open exclusively to me, so I do not need to check before writing to it, but I am concerned about the append of FileB to FileA

    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append to a text file (2000)

    Presumably, FileA will be locked if another application is writing to it, so you can use the IsFileLocked function to test whether IsFileLocked(FileA) is False. This could be done in a loop, so that your code can cool its heels until FileA becomes available.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append to a text file (2000)

    I have managed to append the two files together using the following code

    If Not FileLocked(strFileName) Then
    Shell "cmd /c copy c:current.txt + cutput.txt c:current.txt"
    End If

    The append works fine (though I found that if I tried cutput.txt + c:current.txt c:current.txt the current.txt file ONLY contained the contents of output.txt) Not a problem as the order doesn't matter. Is this just a timing issue?

    The problem I now have is that a control character of some sort is added into the newly created text file between the contents of the old current.txt and output.txt.
    If output.txt does not exist the control character is added to the end of the new file. If there a method of preventing this control character being added?

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append to a text file (2000)

    The control character is the end-of-file marker (ASCII character 26). Here is alternative code; it reads one text file line by line and appends it to the other file.

    Sub AppendOutput2Current()
    Dim fSrc As Integer
    Dim fTrg As Integer
    Dim strLine As String

    fSrc = FreeFile
    Open "C:Output.txt" For Input As #fSrc
    fTrg = FreeFile
    Open "C:Current.txt" For Append As #fTrg

    ' Optional - insert line feed
    Print #fTrg,

    Do While Not EOF(fSrc)
    Line Input #fSrc, strLine
    Print #fTrg, strLine
    Loop

    Close #fTrg
    Close #fSrc
    End Sub

    Depending on what the output looks like, you may want to comment out the line Print #fTrg, - it's best to experiment with copies of the files.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append to a text file (2000)

    Hans
    Many thanks for the help - I've merged the check for locking and the latest script together to produce exactly what I needed. It's very much like old fashioned Basic programming for this type of thing.
    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append to a text file (2000)

    Instructions such as Open, Print and Close are survivors from very early versions of Basic, so yes, this code definitely has an old-fashioned flavor. But it works, and is much faster than the more modern methods using Scripting.FileSystemObject.

Posting Permissions

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