Replacing a line in a text file

[geoB]

Through a process of abductive logic (flounder aimlessly, hoping for luck or inspiration) I've concocted the following code to replace lines in a text file. The file is created by a test generator and to be used to import quizzes in a course management system. The purpose is to rename each question with something more meaningful. The question name follows the text ":TITLE:" in the text file.

The code:

Sub TitleSearch()
Dim r As Range
Dim strSearchFor As String, strReplace As String
Dim strQLabel As String, i As Integer

strQLabel = InputBox("Question number prefix, e.g. 'Chapter 1, Question '", "TITLE Replacement")

' set the search string to the current Selection
strSearchFor = ":TITLE:"
i = 1
' set the range object to be from
' the Selection END, to the end of the document
Set r = ActiveDocument.Range( _
Start:=Selection.Range.End, _
End:=ActiveDocument.Range.End)

' start the search

Do
With r.Find
strReplace = ":TITLE:" & strQLabel & i
.Execute FindText:=strSearchFor
If r.Find.Found = False Then
Exit Do
End If
r.MoveEnd unit:=wdParagraph
r.Delete
r.InsertAfter strReplace & vbCr
r.Collapse Direction:=wdCollapseEnd
End With
i = i + 1
Set r = ActiveDocument.Range(Start:=r.Start, End:=ActiveDocument.Range.End)
Loop

End Sub

Surely there's a more elegant way to do this? Your comments and corrections are most welcome.

Thanks.

George

[fumei]

I am not totally following.

1. Question number prefix, e.g. 'Chapter 1, Question '

Should that not be suffix, rather than prefix? Is it not "Chapter 1, Question X"? Is it really "Chapter 1, X Question"?

2. This is not correct:
' set the search string to the current Selection
strSearchFor = ":TITLE:"
It does not set the string to the current Selection...it sets the string to be ":TITLE:". It has nothing to do with whatever is the Selection.

3. Besides, what does the Selection have to do with this anyway? You do not deal with the Selection. What if the Selection was at the end of the document in the first place? Then: Set r = ActiveDocument.Range( _
Start:=Selection.Range.End, _
End:=ActiveDocument.Range.End)
would not do much. If the Selection is significant (and I fail to see why it would be), then you need to explicitly do something with it, say move it to the start of the document.

4. Your variable strQLabel is the return from the Inputbox, which if I understand correctly will be a number. In which case:
strReplace = ":TITLE:" & strQLabel & i
will be something like:

":TITLE:32"

3 (say) from the Inputbox, and (say) 2 from the variable i.

Is this the "meaningful" you want? It does not seem that way to me.

5. I am not following the loop. Do you want to replace ALL found instances of ":TITLE:" with the same thing? Seems odd. After all, you only get input via the InputBox once.

Yes, I am positive your code can be optimized much better. Can you state precisely your requirements?

Are there a bunch of ":TITLE:" in the document? Are you trying to go through each ":TITLE:" and replace the existing text of its paragraph with different numbers (from the inputbox)? In other words:

":TITLE: yadda blah"
":TITLE: ho haaaa"
":TITLE: whatever whatever"

with

":TITLE: 11"
":TITLE: 12"
":TITLE: 21"

As i said, i am not quite following what you are doing.

BTW: I am not sure if this may help, but you can do things like put an Inputbox instruction IN another instruction, like:

CONST TITLE As String = ":TITLE:"

... other stuff

strReplace = TITLE & _
InputBox("Question number prefix, " & _
"e.g. 'Chapter 1, Question '", _
"TITLE Replacement") & i


":TITLE:" is declared (and set) as a constant; since you use it a lot, why not?

The string strReplace can then be set using the constant, AND the return of the Inputbox, AND the variable i.

BTW2: VBA no longer uses Integer. It automatically converts all declared Integer into Long.

[geoB]

Let's see if this helps:

1. If i is the question number, then strQLabel is a prefix to the question number. For example, if strQLabel is "Ch 1, Question ", i =1 and the original line is ":TITLE:Testbank 1.2-1", it is replaced with ":TITLE:Ch 1, Question 1" as intended.

2. The comment regarding selection is due to its having been borrowed with some code from a thread here at VBAX. I apologize if it caused confusion.

3. Perhaps addressed in 2?

4. The input expected at the input box would be similar to "Ch 1, Question " and would not be a number. The question number is generated by i. So yes, ":TITLE:" is followed by different numbers, each preceded by the text supplied just once at the input box.

5. More precisely, the line beginning with ":TITLE:" contains the question title as created by the test generator and contains a reference to the test bank library's question (see #1). It has no meaning for the test taker. The objective is to create a more meaningful title for the question such as "Chapter 1, Question 3", and so on.

Point taken about VBA and dimensioning as Integer. It must be deprecated; I've done a bunch of VBA in Access & Excel and, like the cows in the pasture, I still go around the tree long after the tree has died and been hauled away.

Hope this provides sufficient clarification.

George

[Norie]

George

If this is a text file why do you even need to use Word to manipulate it?

Why not use the native VBA File I/0 functions and the String functions?

ie read in line of text, alter as/if required, write new line to new file.

Note I mentioned a new file, it would be possible to write to the existing file but it would be a bit of a nightmare.:banghead:

Easier to create new file, delete old file and rename new file.:)

[geoB]

Thanks for the replies.


If this is a text file why do you even need to use Word to manipulate it?
...
Easier to create new file, delete old file and rename new file.:)

I used Word, I guess, because it was there and I could see what I was doing. And, for me, it was easier. Just lazy, I suppose.

G

[Norie]

George

Word was easier to use?:huh:

You are dealing with a text file not a Word document. If you used the methods I mentioned you would have no need to use things like Select/Range etc.

Note, I'm not saying it's impossible but as far as I can see manipulating a text file would be easier using standard VBA file I/O and string methods.:)

[fumei]

"For example, if strQLabel is "Ch 1, Question ", i =1"

But how can strQLabel be "Ch 1, Question "? Ah... I see.strQLabel = InputBox("Question number prefix, " & _
"e.g. 'Chapter 1, Question '", "TITLE Replacement")
actually means: "Type in the TEXT (of the chapter), the TEXT "Question"."

I thought it was asking for just the number. My mistake. Yes, OK, that makes sense.

I am still confused though, as there is still only ONE use of inputbox.

The user types in "Ch 1, Question".

The number following "Question" comes from the variable i.

OK.

So...there is only the ONE chapter (input via the inputbox)?

There will never be Ch 2, Question x?

As it stands, there can not be, as again there is only the one inputbox.

In which case, why bother with any user input at all?

As for Norie's comment, while it true standard file I/O could possibly do the job, to use "standard VBA" methods you must be in a VBA compliant application...e.g Word.

So, there you are, in Word anyway, using Word VBA...so what the heck. I see no problem using Word as you are doing. If there is only one chapter - and I actually doubt this is the case...
Option Explicit

Sub TitleSearch()
Const TITLE As String = ":TITLE:"
Dim r As Range
Dim strQLabel As String
Dim strReplace As String
Dim i As Long

strQLabel = InputBox("Question number prefix, e.g. " & _
"'Chapter 1, Question '", "TITLE Replacement")
Set r = ActiveDocument.Range

i = 1

With r.Find
strReplace = TITLE & strQLabel & i & _
vbCrLf
Do While .Execute(FindText:=TITLE, _
Forward:=True) = True
With r
.MoveEnd unit:=wdParagraph
.Text = strReplace
.Collapse Direction:=wdCollapseEnd
End With
i = i + 1
Loop
End With
End Sub
What it does.

1. declare variables (including the constant TITLE)
2. get replacement string via inputbox
3. set the range object for the document
4. Using range.find, set the replacement string with the constant TITLE, the replacement string, and the counter (i)
5. find TITLE
6. move range end to paragraph
7. replace the range text
8. collapse the range
9. increment counter
10. repeat 4 - 9 for all Found instances of TITLE

[Norie]

Fumei

Well I don't disagree with you regarding using Word VBA.

But to do so you would have to have at least some grasp of Word's Object Mode/methods/properties etc.

And if you used standard VBA I/O functions etc you could do it any application that supports them.

[geoB]

fumei: Thanks for the code tuneup. That's the kind of thing I was looking for. The intended application was for weekly chapter quizzes, so in general the chapter would not change for a given text file. If it did, I'd simply say something like "Question ".

As for working in Word, I thought it was time I learned some of its object model, so that's why I was there. Besides, the end result will be a text file, and I might as well use Word to view it.

Again, many thanks for your time.

George

[geoB]

fumei: Just got around to running your code. Turns out that the question number in the replaced text is always 1. I've attached a sample test generator output file to play with if you like.

G

[Norie]

George

Is this the logic behind renaming the questions?

:TITLE:Testbank 1.2-1-Ch 1 Question 1

:TITLE:Testbank 1.2-4-Ch 1 Question 2

:TITLE:Testbank 1.2-6-Ch 1 Question 3

:TITLE:Testbank 1.2-15-Ch 1 Question 4

:TITLE:Testbank 1.2-18-Ch 1 Question 5

:TITLE:Testbank 1.2-20-Ch 1 Question 6

:TITLE:Testbank 1.2-28-Ch 1 Question 7

:TITLE:Testbank 1.2-36-Ch 1 Question 8

:TITLE:Testbank 1.2-38-Ch 1 Question 9

:TITLE:Testbank 1.2-40-Ch 1 Question 10

[geoB]

The logic would be:

From: ":TITLE:Testbank 1.2-1"
To: ":TITLE:Chapter 1, Question 1"

From: ":TITLE:Testbank 1.2-4"
To: ":TITLE:Ch 1 Question 2"

and so on.

The string :TITLE: is used by the course management system to specify that what follows is the name of the question in the quiz.

btw, the code in my original post performs the trick, just not as elegantly as it might.

g

[geoB]

A small modification to make fumei's code work:
With r.Find
Do While .Execute(FindText:=TITLE, _
Forward:=True, Replace:=wdReplaceOne) = True
strReplace = TITLE & strQLabel & i & _
vbCrLf
With r
.MoveEnd unit:=wdParagraph
.Text = strReplace
.Collapse Direction:=wdCollapseEnd
End With
i = i + 1
Loop
End With

Move the strReplace line inside the do loop so that incrementing i has some effect. No wonder it was stuck at 1.

g

And thanks!

[Norie]

George

So is the logic I posted correct or not, please ignore the non-inclusion of TITLE.

And also clarify whether it's Chapter or Ch.

[geoB]

It is not at all clear how my reply does not answer the question. The logic is to replace all the text between the string ":TITLE:" and the end-of-paragraph on the line in which the string ":TITLE:" appears with a string supplied by the user concatenated with an incrementing integer. The loop as modified in #13 above performs this task. Perhaps its logic is clearer than my text.

g

[Norie]

George

From: ":TITLE:Testbank 1.2-1"
To: ":TITLE:Chapter 1, Question 1" - here you use Chapter.

From: ":TITLE:Testbank 1.2-4"
To: ":TITLE:Ch 1 Question 2" - here you use Ch.

Is one or the other a typo?

Option Explicit
Sub ConvertQs()
Dim strFile As String
Dim strFileNew As String
Dim strLine As String
Dim strNewLine As String
Dim pos As Long
Dim FF1 As Long, FF2 As Long
Dim I As Long
strFile = "C:\ch1_webct.txt"
strFileNew = "C:\ch1_webctnew.txt"

FF1 = FreeFile()

Open strFile For Input As #FF1

FF2 = FreeFile()

Open strFileNew For Output As #FF2

While Not EOF(FF1)
Line Input #FF1, strLine

pos = InStr(strLine, "TITLE")

If pos Then
I = I + 1
strNewLine = ":TITLE: Chapter 1 Question " & I
Else
strNewLine = strLine
End If

Print #2, strNewLine

Wend


Close #FF2

Close #FF1

End Sub

[geoB]

Thanks for the alternate approach.



Is one or the other a typo?

Neither is a typo. The replacement text is a user-supplied string concatenated with an incremented integer. I can readily modify your code to prompt for the string.

g