spared hours of mindless boring <

Ahem! I find it rather engaging <g (or at least addictive!)

This new version, which I'll call the "text" version (below) may take care
of the "quotes" issue--let me know. It does away with the worksheet search
and parses with a simple vba "InStr" function. I'm not sure if this will
hinder or aid you, as I'm not clear what you're after with the example you
gave ( "=""abc"""). If you want to use the "workbook" version, you might
try some of the below options for your "replace" term, and possibly include
the option "LookIn:=xlFormulas" in the Replace statement, i.e.,

..Cells.Replace What:="text1", Replacement:=a1, LookIn:=xlFormulas.

Even though the cell might show "#NAME?", the Replace function will find the
formula. You can also change the workbook setting: Tools | Options |
Window Options | Formulas to show formulas rather than values in cells (for
your personal peace of mind).

For the particular example you gave, you probably need an expression such as

"=" & "abc" & ""
or
"=" & "abc"
or just
"=abc"

instead of

"=""abc"""

But like I say, I'm not sure what you're after with this--I'm thinking you
may be trying to link to some value in a worksheet? I don't know of any way
to get the compiler to accept quotes within quotes, but my guess is this
something you don't really need.

If you want to generalize the code for use by others, it might (or might
not!) help if you describe the context in which it is being used--for
example, what types of data are being generated, how often, and in what ways
the layout of the data might change. For example, will the text terms to be
replaced always be in the same position in "input1"? In that case I can
parse for their names. Otherwise, you can just list them in a new procedure
I've added near the top of the module ("SetTextTerms"). I can't promise
that I'll be able to accomodate you on generalizing as it may run into too
much time.

Depending on your use of the data and how much you generate, a database
might also be appropriate--a very handy tool for analysis, but again, that
could run into time and I'm only just beginning to learn the ropes of
database programming.

I should also add that the parsing function in the new version has some
vulnerabilities that may or may not show up, depending on the data and your
replacement terms. For instance, it doesn't have built in the power to
distinguish between whole and parts of words, the way you can choose with
the Excel Replace function. This can be added, it's just more work that I
may or may not have time for. But please do let me know of any problems or
any mods you would like and I will see what I can do. And let me know if you
prefer the "workbook" version or the "text" version.

Regards,
Shockley

Program Notes:

You can change the "home directory", i.e., the folder that stores the input
and output files in the first line of code, "Public Const..." It might be a
good idea not to clutter up your root directory with a lot of text files.
So, for example, you could change "C:\" to "C:\Data\", but be sure to
include the the "\" at the end. And don't forget to move "input1.txt",
"input2.txt", and File_ID.txt to the new directory. There is a new file,
"LastFileName.bat" that the program creates and places in the C:\ directory,
which you can leave there. It is used for opening the output text files
with notepad.

You also now have the option to open the output file as a workbook or as a
text file, by choosing the first or the second procedure in the module.
There's also a procedure (ResetFileID) to automatically reset the file
number to whatever number you like. Just put the desired number after the
equal sign on the first line. The first output file created will have the
next number after the one you entered.

Set the terms to be replaced in "input1.txt" in the procedure called
"SetTextTerms". Enter each term between the quotation marks on the right
side of the equal sign. Search terms are case sensitive.

****

Public Const DataDir As String = "C:\"
Private arr_a(1 To 3), _
arr_Text(1 To 3) As String
Sub OpenLastFile_xls()
OpenLastFile "xls"
End Sub
Sub OpenLastFile_txt()
OpenLastFile "txt"
End Sub
Sub SetTextTerms()
arr_Text(1) = "text1"
arr_Text(2) = "text2"
arr_Text(3) = "text3"
End Sub
Sub ResetFileID()
NewFileNo = 0
sFileNo = Format(NewFileNo, "@")
PrintOut DataDir & "File_ID.txt", sFileNo
End Sub
Sub Tester()

ChDrive Left(DataDir, 1)

Dim sFileID As String

SetTextTerms

input2 = FreeFile(1)
Open DataDir & "input2.txt" For Input As input2

NewText = ""
OutText = ""

Do
Line Input #input2, LineX
Get_array LineX
SubstituteValues NewText
If OutText = "" Then
OutText = NewText & vbCrLf
Else: OutText = OutText & NewText & vbCrLf
End If
Loop Until EOF(input2)

Close #input2

sFileID = Format(FileID, "000000")
PrintOut DataDir & "output_" & sFileID & ".txt", NewText

End Sub
Sub SubstituteValues(NewText)

ChDrive Left(DataDir, 1)

input1 = FreeFile(1)
Open DataDir & "input1.txt" For Input As input1

Do
Line Input #input1, OldLine
NewLine = ChangedLine(OldLine)
If NewText = "" Then
NewText = NewLine & vbCrLf
Else: NewText = NewText & NewLine & vbCrLf
End If
Loop Until EOF(input1)

Close #input1

End Sub
Sub Get_array(LineX)
Tab1Pos = InStr(1, LineX, Chr(9))
arr_a(1) = Left(LineX, Tab1Pos - 1)
s2 = Mid(LineX, Tab1Pos + 1)
Tab2Pos = InStr(1, s2, Chr(9))
arr_a(2) = Left(s2, Tab2Pos - 1)
arr_a(3) = Mid(s2, Tab2Pos + 1)
End Sub
Function ChangedLine(OldLine)
For i = 1 To 3
Do
textPos = InStr(1, OldLine, arr_Text(i))
If textPos < 0 Then
s1 = Left(OldLine, textPos - 1)
s2 = Mid(OldLine, textPos + Len(arr_Text(i)) + 1)
OldLine = s1 & arr_a(i) & s2
End If

Loop Until textPos < 1
Next i
ChangedLine = OldLine
End Function
Function FileID() As Double
ChDrive Left(DataDir, 1)

Dim iFile As Integer
Dim LastID As Double
Dim sLastID, sFileID As String

iFile = FreeFile(1)
Open DataDir & "File_ID.txt" For Input As iFile
Line Input #iFile, sLastID
FileID = sLastID + 1
Close iFile

iFile = FreeFile(1)
Open DataDir & "File_ID.txt" For Output As iFile
Print #iFile, Trim(sLastID + 1)
Close iFile

End Function
Sub PrintOut(FileName, NewText)
ChDrive Left(DataDir, 1)
Output = FreeFile(1)
Open FileName For Output As Output
Print #Output, NewText
Close #Output
End Sub
Sub OpenLastFile(filetype)
ChDrive Left(DataDir, 1)
sFile = Dir(DataDir & "output_*.txt")
HiFileNo = Mid(sFile, 8, 6)
Do
sFile = Dir
If sFile < "" Then
NewFileNo = Mid(sFile, 8, 6)
If NewFileNo FileNo _
Then HiFileNo = NewFileNo
Else
If filetype = "xls" Then
OpenText "Output_" & _
HiFileNo
Else
MakeBat HiFileNo
Shell ("C:\LastFileName.bat")
End If
Exit Sub
End If
Loop
End Sub
Sub OpenText(sFileName)
ChDrive Left(DataDir, 1)
Workbooks.OpenText _
FileName:=DataDir & sFileName & ".txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Tab:=True, _
FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), _
Array(4, 2), Array(5, 2), Array(6, 2))
End Sub
Sub MakeBat(HiFileNo)
sLastFile = DataDir & "output_" & Format(HiFileNo, "000000") & ".txt"
LastFileName = "C:\LastFileName.bat"
sCommand = _
"@Echo Off" & vbCrLf & _
"Notepad.exe " & sLastFile & vbCrLf & _
"Exit"
PrintOut LastFileName, sCommand
End Sub





"Tom" wrote in message
...

Tom, very happy to help!

Thank you!

The newest version works beautifully! What can I say - I am as
grateful as anyone who is spared hours of mindless boring work.
I truly appreciate your kind help.

Shockey, may I please about one last thing? when the text that I am
replacing [i.e. text1, text2, text3] contains " " ", or in other
words

when I replace

. Cells.Replace What:="text1", Replacement:=a1"
.Cells.Replace What:="text2", Replacement:=a2
.Cells.Replace What:="text3", Replacement:=a3

replace it by

Cells.Replace What:="=""abc""", Replacement:=a1"
.Cells.Replace What:="text2", Replacement:=a2
.Cells.Replace What:="text3", Replacement:=a3

I get problems when I compile and run the code. When the text taht I
am replacing doesn't contain a quatation mark, therea re no problems
when I run the code. Is it at all possible to make the compiler
distinguish between the quatation marks around the text that I am
replacing [as in What="text1"], and the quatation marks that are part
of the text that I am replacing?

I hope other people will be able to use this code - for instance they
could start with one batch file and generate a longer batch file using
this code.

Thank you

Best Wishes


Tom

Tom, you'r very welcome and I'm glad it worked. If you don't mind my
curiosity: If you have thousands of queries to make, does this mean you
will be automating the queries and running the macro in a loop? If so, the
text version should be very good for this since it runs many times faster
than the workbook version. Also, assuming that you will be looping the
program, will the data automatically go to your database?

This is kind of similar to the project I'm working on now for myself,
building a database of stock market fundamental and trading data, using
automated downloads from yahoo, BigCharts, and Wall Street Journal on-line.

Regards,
Shockley

"Tom" wrote in message
...
Ahem! I find it rather engaging <g (or at least addictive!)

:)

It worked, and it is exactly what I needed. Thank you - G'd Bless you
Shockey!!!

I apologize for not providing feedback yesterday. Yesterday in the
evening, I tried the new macro as well as your "&" suggestion, but I
didn't get the output file. However, today I found out what I was
doing wrong, and now everything works!

I got a two week trial password to a database containing economic time
series [i.e. GDP of various countries, etc]. I have a SAS program
that pulls out 1 data series [and it pulls out only observations that
satisfy certain criteria]. However there are literally tens of
thousands of data series that I need to download. I wanted to create
a program containing thousands of small "one time series" programs.
The "technical support" of the university that hosts the database had
refused to help me with this. I will now be able to do it.


All the Best


Tom