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

[Jon Macmichael]

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

 

[Bernie Deitrick]

Jon,

Try the sub below.

HTH,
Bernie
MS Excel MVP

Sub AppendFiles2()
Dim SourceNum As Integer
Dim DestNum As Integer
Dim Temp As String

Dim myPath As String
Dim myFile As String
Dim myOut As String
Dim myLabel As String

myPath = "E:\MarketManager\ascii\"

ChDrive myPath
ChDir myPath
myFile = Application.GetOpenFilename("Text Files (*.txt),*.txt,")
myLabel = Replace(Replace(myFile, myPath, ""), ".txt", "")

' Open the source text file.
SourceNum = FreeFile()
Open myFile 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
DestNum = FreeFile()
myOut = Left(Temp, InStr(1, Temp, ",") - 1) & "tv.txt"
Open myOut For Append As DestNum
Print #DestNum, myLabel & " " & _
Mid(Temp, InStr(1, Temp, ",") + 1, Len(Temp))
Close #DestNum
Loop

' Close the source file.

Close #SourceNum
End Sub

 

[Jon Macmichael]

Bernie,

I appreciate the code. When I ran it there was a "Compile Error - Sub
or Function not defined." The module left the debug yellow across the
"Sub..." heading and the second "Replace" selected, in the line below;

myLabel = Replace(Replace(myFile, myPath, ""), ".txt", "")

........................^ here

Hoping you can help.

My apologies for this being slow. Until early this am (here in Aus) my
browser would only show threads till 27th May (rang some friends and..
not so with them?)

Regards
Jon

 

[Bernie Deitrick]

John,

You must be using Excel 97 - Replace first appeared in XL2000.

Use

Application.Substitute

in the place of each Replace.

HTH,
Bernie
MS Excel MVP

 

[Jon Macmichael]

Bernie,

I cannot follow through with this for a couple of hours. Considering I
might be on the otherside of the planet, and timing; thought I'd ask
you this 'quickly' as I might otherwise be asking in your o'nite
hours.

Would this the be correct?;

myLabel = Application.Substitute(Application.Substitute(myFile,
myPath, ""), ".txt", "")

regards and thanks again
If it was not with your help and all the other contributors, these big
projects would be way over the moon. Well i'm pretty much over the
moon with the help and seeing an idea come to life.

Jon

 

[Bernie Deitrick]

Jon,

That would be correct.

Bernie
MS Excel MVP

 

[Jon Macmichael]

Bernie,

I have put Application.Substitute in. I am presuming the
GetOpenFileName is requesting the single daily update source file from
which to append all the other indvidual text files.

This source file is in the dir "C:\jmData\tvCopy\" and the folder
holding all the files to be appended to is in the dir
"C:\jmData\tvASXCopy\".

On running the sub I get a 'Run-time' Error '424' Object Required.

If I have the picture wrong and the sub is only going to update the
destination files one at a time then I have also selected one of the
destination files from GetOpenFileName, but get the same error
message?

Hoping you can help, please.

Thanks
Jon

 

[Jon Macmichael]

Bernie,

Please forget my earlier post. Typo. I had "Aplication". I'm sorry.

Had to make a few adjustments. The sub was outputing to the same dir
as where the input file was. Hence make all new files in that dir.
Also, the new records were getting the addition of the dir added, eg
C:\JMData\tvASXcopy\040531 7
instead of just
040531 7

It's been a great learning exercise for me. From your code I was able
to read through the VBA help to see what was happening, and make these
changes;
added "myDest" and "myDate" swapping "myLabel" in the print statement
to "myDate".

Where there are no associated file in the destination dir then a new
file is added fro the symbol, which is good. Now, I just have to
consider how to append the date with a zero value where there is no
record for that symbol in the source dir? And maybe how to have
multiple source files distributed (say for a folder full of
undistributed files)?

As it is only the VB code running otherwise non-associated text files,
is it necessary for the excel file holding the VB to be open to run
the macro ie is there a way to run the macro without excel being open.

Also would the following statement (if put directly above 'End Sub')
be successful as a 'warning' to wait until the distribution is
complete;

MsgBox = "Distribution now complete"

Many thanks for your help
regards
Jon

Sub AppendFiles2()
Dim SourceNum As Integer
Dim DestNum As Integer
Dim Temp As String

Dim myPath As String
Dim myFile As String
Dim myOut As String
Dim myLabel As String
Dim myDest As String
Dim myDate As String

myPath = "C:\JMdata\tvASXCopy\"
myDest = "C:\JMdata\tvCopy\"

ChDrive myPath
ChDir myPath
myFile = Application.GetOpenFilename("Text Files (*.txt),*.txt,")
myLabel = Application.Substitute(Application.Substitute(myFile,
myPath, ""), ".txt", "")

' Open the source text file.
SourceNum = FreeFile()
Open myFile 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
DestNum = FreeFile()

myOut = myDest & Left(Temp, InStr(1, Temp, ",") - 1) &
"tv.txt"
myDate = Mid(myLabel, InStr(17, myLabel, "\") + 1, 6)

Open myOut For Append As DestNum
Print #DestNum, myDate & " " & _
Mid(Temp, InStr(1, Temp, ",") + 1, Len(Temp))
Close #DestNum
Loop

' Close the source file.
Close #SourceNum
End Sub

 

[Bernie Deitrick]

Jon,

See my notes in-line with your comments/questions.

HTH,
Bernie
MS Excel MVP

Typo. I had "Aplication".

Some good practices when coding are 1) never use your shift key when
entering code and/or 2) turn on autocomplete (in VBE, use Tools | Options...
Editor tab, check "Auto List Members" and 3) always use option explicit at
the top of your module, and 4) use variables that have a mix of lower case
and upper case letters in their name.

So if you had properly typed in

application

with 1) when you moved off that line, application would have become
"Application" and you would know that you had typed it in correctly. With
2) when you type the . after aplication and no list members had appeared,
you would have known that you had mistyped application. With a variable like
Dim myValue As String

if you type in
myvalue

it will change to
myValue
when you finish your line of code, and you'll know that you typed your
variable correctly.

Had to make a few adjustments. The sub was outputing to the same dir
as where the input file was.

That requirement wasn't clear from you original post, but you handled it
well.

Also, the new records were getting the addition of the dir added, eg
C:\JMData\tvASXcopy\040531 7
instead of just
040531 7

The original myLabel had the path taken out, using those two replace
statements.

Where there are no associated file in the destination dir then a new
file is added fro the symbol, which is good. Now, I just have to
consider how to append the date with a zero value where there is no
record for that symbol in the source dir?

Do you mean no listing in the opened .txt file?

And maybe how to have
multiple source files distributed (say for a folder full of
undistributed files)?

You could use the multi-select feature of the getopenfilename method, and
loop through the array of file names returned by that method.

As it is only the VB code running otherwise non-associated text files,
is it necessary for the excel file holding the VB to be open to run
the macro ie is there a way to run the macro without excel being open.

You could compile it into an EXE file if you have a version of VB, although
you would no longer have access to Application methods and objects. You
would need to use the methods available in VB.

Also would the following statement (if put directly above 'End Sub')
be successful as a 'warning' to wait until the distribution is
complete;

MsgBox = "Distribution now complete"

You could use the Application.Statusbar object within your loop:

Application.Statusbar = "Now processing " & myOut

and at the end use
Application.Statusbar = False
Msgbox "Finished processing!"

Many thanks for your help

You're welcome,

Bernie

 

[Bernie Deitrick]

Jon,

Now, I just have to
consider how to append the date with a zero value where there is no
record for that symbol in the source dir?

After thinking a bit, this is what I came up with. At the bottom of your
code, use the Application.Filesearch method to read your directly and loop
through all files, and open those whose modified date isn't the current day
(if you could be doing this multiple times per day, then you would need to
read the time as well), then paste the label and 0 into the file. If you
need help with the code, let me know.

HTH,
Bernie
MS Excel MVP

 

[Jon Macmichael]

Bernie

Shall get your VB typing practises into play. Have to learn about
"option explicit" though.

Sorry I was not clear I my original description. It's a worry when
thinking it is taking up someone else's time and effort.

The original myLabel had the path taken out, using those two replace
statements.

I'll have to read up on Application.Substitute too. I could see its
result but didn't really understand much of what was going on in that
line.

Do you mean no listing in the opened .txt file?

Yes. The single daily source file holding many symbols and respective
number of trades for the day/date appends out to their repective
destinations; any one symbol/file and its date history of trades. It
can happen (often) where a symbol has no trades on a given date.

You could use the multi-select feature of the getopenfilename method, and
loop through the array of file names returned by that method.

My concern here is to whether or not "multi-select" would distribute
each source file in chronological order?

You could compile it into an EXE file if you have a version of VB,

Ahh. Beyond me. I don't have VB and it is beyond my cpabilities. Guess
I'd better hang back on this one.



Thanks for the 'status bar' code. This looks like a great way to be
aware of 'behind the scenes' processing.

After thinking a bit, this is what I came up with. At the bottom of your
code, use the Application.Filesearch method to read your directly and loop
through all files, and open those whose modified date isn't the current day
(if you could be doing this multiple times per day, then you would need to
read the time as well), then paste the label and 0 into the file. If you
need help with the code, let me know.

Thanks for this advice too. I'll see how I go on it. There is alot of
leads in the code you have done for me and I should be able to get it
together.

Bernie, ...can't say how much I have appreciated your help.
best regards
Jon

 

[Jon Macmichael]

Bernie

Shall get your VB typing practises into play. Have to learn about
"option explicit" though.

Sorry I was not clear I my original description. It's a worry when
thinking it is taking up someone else's time and effort.

The original myLabel had the path taken out, using those two replace
statements.

I'll have to read up on Application.Substitute too. I could see its
result but didn't really understand much of what was going on in that
line.

Do you mean no listing in the opened .txt file?

Yes. The single daily source file holding many symbols and respective
number of trades for the day/date appends out to their repective
destinations; any one symbol/file and its date history of trades. It
can happen (often) where a symbol has no trades on a given date.

You could use the multi-select feature of the getopenfilename method, and
loop through the array of file names returned by that method.

My concern here is to whether or not "multi-select" would distribute
each source file in chronological order?

You could compile it into an EXE file if you have a version of VB,

Ahh. Beyond me. I don't have VB and it is beyond my cpabilities. Guess
I'd better hang back on this one.



Thanks for the 'status bar' code. This looks like a great way to be
aware of 'behind the scenes' processing.

After thinking a bit, this is what I came up with. At the bottom of your
code, use the Application.Filesearch method to read your directly and loop
through all files, and open those whose modified date isn't the current day
(if you could be doing this multiple times per day, then you would need to
read the time as well), then paste the label and 0 into the file. If you
need help with the code, let me know.

Thanks for this advice too. I'll see how I go on it. There is alot of
leads in the code you have done for me and I should be able to get it
together.

Bernie, ...can't say how much I have appreciated your help.
best regards
Jon

 

[Jon Macmichael]

Bernie

Shall get your VB typing practises into play. Have to learn about
"option explicit" though.

Sorry I was not clear I my original description. It's a worry when
thinking it is taking up someone else's time and effort.

The original myLabel had the path taken out, using those two replace
statements.

I'll have to read up on Application.Substitute too. I could see its
result but didn't really understand much of what was going on in that
line.

Do you mean no listing in the opened .txt file?

Yes. The single daily source file holding many symbols and respective
number of trades for the day/date appends out to their repective
destinations; any one symbol/file and its date history of trades. It
can happen (often) where a symbol has no trades on a given date.

You could use the multi-select feature of the getopenfilename method, and
loop through the array of file names returned by that method.

My concern here is to whether or not "multi-select" would distribute
each source file in chronological order?

You could compile it into an EXE file if you have a version of VB,

Ahh. Beyond me. I don't have VB and it is beyond my cpabilities. Guess
I'd better hang back on this one.



Thanks for the 'status bar' code. This looks like a great way to be
aware of 'behind the scenes' processing.

After thinking a bit, this is what I came up with. At the bottom of your
code, use the Application.Filesearch method to read your directly and loop
through all files, and open those whose modified date isn't the current day
(if you could be doing this multiple times per day, then you would need to
read the time as well), then paste the label and 0 into the file. If you
need help with the code, let me know.

Thanks for this advice too. I'll see how I go on it. There is alot of
leads in the code you have done for me and I should be able to get it
together.

Bernie, ...can't say how much I have appreciated your help.
best regards
Jon

 

[Bernie Deitrick]

Jon,

It's been a long time since I programmed in XL 97, and perhaps I may be
wrong but:

Application.Substitute

is acceptable shorthand for

Application.Worksheetfunction.Substitute

at least in XL2000 and onward. I'm not sure if the Worksheetfunction
property existed in XL97, but if you try typing in

Application.Subst

and your autocomplete doesn't come up with "Substitute", then you'll know
you'll need to start it by typing

Application.Worksh

and highlighting "Worksheetfunction" prior to hitting the next period.


For the Option Explicit, in the VBE, use Tools | Options... Editor tab,
check "Require Variable Declaration" Then any module you add will have the
line

Option Explicit

at the top, and none of your code will run unless you declare your
variables. ;-)

As to your chronological order concern: you could address this either of two
ways.

If the files are created in proper chronological order, then your could sort
the files prior to opening and processing them. If they aren't created in
order, it would be quite easy to sort the output files after everything was
done: of course, you would need to read the entire file into Excel, do the
sort, then write it out again. Not too hard, but it would add some time to
the processing.

And let me know if you need help with updating files missing from the master
list - the 0 problem.

HTH,
Bernie
MS Excel MVP

 

[Jon Macmichael]

Bernie I could do with some more help if its available, please?

I didn't have much success with Application.Substitute or
Application.WorkSheetFunction.Substitute There was no help for it in
excel, and I couldn't find too much relevant on the net.

Strangely enough with "myFile" returning (in the following code) this;
C:\JMdata\tvASXCopy\040531.txt then either of the two '.Substitute 's
will truncate the ".txt" but that's all.

myPath = "C:\JMdata\tvASXCopy\"
myDest = "C:\JMdata\tvCopy\"
ChDrive myPath
ChDir myPath
myFile = Application.GetOpenFilename("Text Files (*.txt),*.txt,")
myLabel = Application.Substitute(Application.Substitute(myFile,
myPath, ""), ".txt", "")

But, anyway the macro is working for appending the files ,so this is
not causing any problem.

What I would like help with is appending records with a zero value for
those files where no trades have taken place. Just to recap, you have
helped me construct a macro to append date & value from a daily stock
txt file ('Source_File') to the many history txt files
('Destination_Files'). One file per stock symbol as per;

this 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

which distributes to many 'Destination_Files' like this; named <
ACUtv.txt >;
040521 243
040524 120
040525 162
040526 449
040527 114
040528 167

ie: in the case of 'ACU' >> ACU,307 << from the source file
appends the destination file with date 040531.

OK. All is going well, except for cases where the source file contains
no record for a stock that hasn't traded. I need the destination file
to show a record for that date with a zero value.

As you have stated below, I can see some resemblance of
Application.Filesearch fitting in, but not sure how to get this coding
off the ground. Looking through help I can see that when combined with
".Execute() then" and ".FoundFiles.Count" then the files not modified
maybe able to be listed, or could then compared to the Source file
(before it is closed in the current macro) to return files without a
match. Either way, I can see that "myDate" should be moved outside the
loop so that it could be used again. It probably should be outside the
loop anyway? A case would be; if in the example above, >> ACU,307
<< did not trade, then there'd be no record, but it's destination file
should still be updated with the record >> 040531 0 <<.

The destination folder only contains .txt files of these stock
symbols, updated once per trade day, all from a single source file.
And, again, here is the working sub, I'm hoping can be modified?

Sub AppendFiles2()
Dim SourceNum As Integer
Dim DestNum As Integer
Dim Temp As String

Dim myPath As String
Dim myFile As String
Dim myOut As String
Dim myLabel As String
Dim myDest As String
Dim myDate As String

myPath = "C:\JMdata\tvASXCopy\"
myDest = "C:\JMdata\tvCopy\"

ChDrive myPath
ChDir myPath
myFile = Application.GetOpenFilename("Text Files (*.txt),*.txt,")
myLabel = Application.Substitute(Application.Substitute(myFile,
myPath, ""), ".txt", "")

' Open the source text file.
SourceNum = FreeFile()
Open myFile 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
DestNum = FreeFile()

myOut = myDest & Left(Temp, InStr(1, Temp, ",") - 1) &
"tv.txt"
myDate = Mid(myLabel, InStr(17, myLabel, "\") + 1, 6)

Open myOut For Append As DestNum
Print #DestNum, myDate & " " & _
Mid(Temp, InStr(1, Temp, ",") + 1, Len(Temp))
Close #DestNum
Loop

' Close the source file.
Close #SourceNum
End Sub


Thanks
Jon

 

[Bernie Deitrick]

Jon,

Try the macro below, and let me know if it does what you want. In my
testing, it seems to meet your requirements.

I'm not sure why you are having a problem with the Application.Substitute
functions: both variable myLabel and myDate have the same value when I run
the code.

HTH,
Bernie
MS Excel MVP

Sub AppendFiles3()
Dim SourceNum As Integer
Dim DestNum As Integer
Dim Temp As String
Dim myDone() As String
Dim myFileCount As Integer
Dim i As Integer
Dim j As Integer
Dim FileDone As Boolean

Dim myPath As String
Dim myFile As String
Dim myOut As String
Dim myLabel As String
Dim myDest As String
Dim myDate As String

myPath = "C:\JMdata\tvASXCopy\"
myDest = "C:\JMdata\tvCopy\"

ChDrive myPath
ChDir myPath
myFile = Application.GetOpenFilename("Text Files (*.txt),*.txt,")
myLabel = Application.Substitute(Application.Substitute(myFile, myPath, ""),
".txt", "")
'Jon, this line shouldn't be needed - myDate is the same as myLabel when I
run it
'But, it doesn't seem to hurt.... Try putting a break on this line and
reading the value
'of myLabel and myDate during execution
myDate = Mid(myLabel, InStr(17, myLabel, "\") + 1, 6)
myFileCount = 0

' Open the source text file.
SourceNum = FreeFile()
Open myFile 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
DestNum = FreeFile()

myOut = myDest & Left(Temp, InStr(1, Temp, ",") - 1) & "tv.txt"

'Keep a record of files done
myFileCount = myFileCount + 1
ReDim Preserve myDone(1 To myFileCount)
myDone(myFileCount) = myOut

Open myOut For Append As DestNum
Print #DestNum, myDate & " " & _
Mid(Temp, InStr(1, Temp, ",") + 1, Len(Temp))
Close #DestNum
Loop

' Close the source file.
Close #SourceNum

With Application.FileSearch
.NewSearch
.LookIn = myDest
.FileType = msoFileTypeAllFiles
.SearchSubFolders = False
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
FileDone = False
'Check to see if the file was done
For j = 1 To UBound(myDone)
If .FoundFiles(i) = myDone(j) Then
FileDone = True
Exit For
End If
Next j
If Not FileDone Then
Open .FoundFiles(i) For Append As DestNum
Print #DestNum, myDate & " 0"
Close #DestNum
End If
Next i
End If
End With

End Sub

 

[Jon Macmichael]

Bernie,

It runs really well, just as all your other code has. I included (from
your earlier post>> Application.Statusbar = "Now processing " & myOut
<<. Put this just under >> myOut = .... << in the top loop. It runs so
fast I can hardly see it. However, when I run this over the 1000 odd
files which should be ready shortly, guess I'll see it for a beep
longer. I've only been running it on 20 or so test files. In any case
I do see the MsgBox, which is good warning.

I've been able to use 'Step through' in the debug, and the "?..." in
the Immediate Window, but not too sure about breakpoint, at this
stage. So I stepped the debug down to >> myFileCount = 0 << and got
these results from the Immediate Window;

?myLabel
C:\JMData\tvASXcopy\040531

?myDate
040531

It's strange on my machine with Excel97 that there is no help file on
Application.Substitute or Application.WorksheetFunction.Substitute but
the autocomplete will set the last scenario, but produce the same
results in the Immediate Window? Anyway we have made redundant its
need (guess "myLabel = " could be commented out or removed).

This has been a very successful project for me, and I wish to thank
you very, very much.

Regards and best wishes
Jon

 

[Bernie Deitrick]

This has been a very successful project for me, and I wish to thank
you very, very much.

Jon,

That's what I always want to hear from my OP's. Thanks for letting me know.

Take care, and if you ever need additional help on this...

Bernie