Can this VBA for work on text files be modified for a different job ?

Giganews Newsgroups
Subject: Can this VBA for work on text files be modified for a different job ?
Posted by:  Jon Macmichael (jonm…@bigpond.com)
Date: 31 May 2004

I have been using the code below (which, from memory, came from the
M-Soft site before I hashed it about) successfully to open 2 text
files, appending the whole of one file to the end of the other, then
deleting the source file.

Now a new task is to;
- update all text files in a specified folder by appending a new
record. This is to be a daily routine, and there should end up being
about 800 to 1000 text files to be updated.

- The information for the record that is appended to each file comes
from a single text file each day (source file).

- The first column of this 'source file' holds a 3 character stock
symbol. This symbol needs to be used to find the right destination
file which is named the same as the 3 charaters & "tv.txt".

- The source file is named with a date "yymmdd" & ".txt". This
provides the first column of the record being appended to the found
'destination file'. The second, and last, column of the new record
comes from a looking up of the value next to the associated symbol in
the source file.

- Not any of these files should be "Killed" as per the ccode below.

Preventative measures;
- the delimiting is different between the files. 'Comma' in the source
file, and 'space' in the destination.
- If a destination file is not found then it could be created.
- If there is no record in the source file for an existing destination
file then the date recorded should be made with an associated 0 value
in the second column.

Here's a sample of a 'Source File' named <  040531.txt  > ;
NAB,1728
WBC,1087
AMP,892
AWE,675
TAH,546
SUN,430
FGL,418
MAP,311
BOQ,311
ACU,307
BLD,303

Here's a sample of 'Destination File' named <  ACUtv.txt  > ;
040521 243
040524 120
040525 162
040526 449
040527 114
040528 167

-so the next appended record for ACUtv.txt should be;
040531 307

Here's the code I have been using to append the whole of one text file
to the end of another, which I'm hoping can be modified for above, and
would dearly appreciate some pointers for adapting.

  Sub AppendFiles1()
      Dim DayName As Variant
      Dim soName As Variant
      Dim goodgoing As Boolean
      Dim SourceNum As Integer
      Dim DestNum As Integer
      Dim Temp As String
      Dim Message, Title, Default

      On Error GoTo ErrHandler
      goodgoing = True

      ' This block - request file name to append to

      Message = "Enter a File Name to append to (not inc .ext)"
      Title = "Append to this .d File Name"
      Default = Right(Range("M9").Value, 5)
      DayName = InputBox(Message, Title, Default)

      If Len(DayName) > 5 Then
          DestNum = FreeFile()
          Open "E:\MarketManager\ascii\" & DayName & ".d" For Append As
DestNum
        Else
        MsgBox "You didn't enter a filename"
        goodgoing = False
      End If

      ' Open the source text file.

      soName = Range("M9").Value
      SourceNum = FreeFile()
      Open "E:\MarketManager\ascii\" & soName & ".so" For Input As
SourceNum

      ' Read each line of the source file and append it to the
destination file.

      Do While Not EOF(SourceNum)
        Line Input #SourceNum, Temp
        Print #DestNum, Temp
      Loop

CloseFiles:

      ' Close the destination file and the source file.

      Close #DestNum
      Close #SourceNum
      On Error Resume Next
      If goodgoing = True Then
        Kill "E:\MarketManager\ascii\" & soName & ".so"
      End If
      On Error GoTo 0
      Exit Sub

ErrHandler:
      MsgBox "Error # " & Err & ": " & Error(Err)
      Resume CloseFiles
  End Sub
'                  <<<<<<<<<>>>>>>>>

Thanks
Jon

Replies