|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.
- 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 > ;
Here's a sample of 'Destination File' named < ACUtv.txt > ;
-so the next appended record for ACUtv.txt should be;
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.
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
MsgBox "You didn't enter a filename"
goodgoing = False
' Open the source text file.
soName = Range("M9").Value
SourceNum = FreeFile()
Open "E:\MarketManager\ascii\" & soName & ".so" For Input As
' Read each line of the source file and append it to the
Do While Not EOF(SourceNum)
Line Input #SourceNum, Temp
Print #DestNum, Temp
' Close the destination file and the source file.
On Error Resume Next
If goodgoing = True Then
Kill "E:\MarketManager\ascii\" & soName & ".so"
On Error GoTo 0
MsgBox "Error # " & Err & ": " & Error(Err)