Hey, Scripting Guy! How can I read lines 56 to 62 in one XML file, and replace those with lines 56 to 62 taken from another XML file?

— JA

Hey, JA. First of all, we’d like to thank you for your question; in addition, we’d like to welcome you and everyone else to today’s Open House in honor of the 501^st Hey, Scripting Guy! column. We know that – sorry, could we put you on hold for a second?

Hmmm …. The Scripting Editor has pointed out that we just had an Open House in honor of the 500^th column and that there is no way we’re having another one, not this soon anyway. To tell you the truth, that doesn’t make much sense to us: if 500 columns are considered noteworthy, well, then wouldn’t 501 columns be even better? But we aren’t going to argue with her; we don’t really want her turning us into newts.

Like she did to poor Dean.

(Editor’s Note: The fact that Dean bears a striking resemblance to a newt has no connection to any special powers the Scripting Editor may or may not have.)

So now what do we do? Seeing as how we aren’t going to have another big party today, we might as well try to come up with a script that can replace selected lines in an XML file. A script like this one:

Const ForReading = 1
Const ForWriting = 2

Set objFSo = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.xml”, ForReading)
For i = 1 to 55
    objFile.SkipLine
Next
For i = 1 to 7
    strText = strText & objFile.ReadLine & vbCrLf
Next
objFile.Close
Set objFile = objFSO.OpenTextFile(“C:\Scripts\New_test.xml”, ForReading)
For i = 1 to 55
    strNewText = strNewText & objFile.ReadLine & vbCrLf
Next
strNewText = strNewText & strText
For i = 1 to 7
    objFile.SkipLine
Next
Do Until objFile.AtEndOfStream
    strNewText = strNewText & objFile.ReadLine & vbCrLf
Loop
objFile.Close
Set objFile = objFSO.OpenTextFile(“C:\Scripts\New_test.xml”, ForWriting)
objFile.WriteLine strNewText
objFile.Close

The first thing you might have noticed about this script is that we don’t use any fancy XML coding; instead we use the FileSystemObject and treat this XML file as if it was just another text file. Why do we do that? That’s simple: an XML file is just another text file. Obviously there are times when you need to use the XML object model to manipulate an XLM file; this isn’t one of those times.

So what do we do with this XML file? Well, we start off by defining a pair of constants: ForReading and ForWriting. We’ll use these two constants to tell the FileSystemObject which mode (for reading or for writing) we want to use when opening the files. After defining the two constants we create an instance of the Scripting.FileSystemObject and then use the OpenTextFile method to open the file C:\Scripts\Test.xml, the file that contains the text we want to copy.

Pretty easy so far, right? Of course, at this point it gets just a teeny-tiny bit tricky. We’ve already decided that we don’t want lines 1 through 55 of this file; we want only lines 56 through 62. Therefore, we set up a For Next loop that runs 55 times; inside that loop, we use the SkipLine method to simply skip past the line in the text file:

For i = 1 to 55
    objFile.SkipLine
Next

What does that do for us? That enables us to skip past the first 55 lines, leaving us exactly where we want to be: line 56. Having reached the promised land (or at least the promised line), we then set up another For Next loop, this one running from 1 to 7:

For i = 1 to 7

Why 1 to 7? That’s easy: our goal now is to copy 7 lines out of the file: lines 56, 57, 58, 59, 60, 61, and 62. Thus a loop that runs from 1 to 7.

Inside that loop we execute this line of code:

strText = strText & objFile.ReadLine & vbCrLf

What we’re doing here is storing those 7 lines of text into a variable named strText. To do that we set the value of strText to the existing value of strText plus the current line in the text file (which we can retrieve using the ReadLine method), plus a carriage return-linefeed. For example, suppose lines 56 and 57 in the XML file consist of the following:

<computer>
    <name>

The first time through the loop strText will be assigned the following:

The second time through the loop strText will be assigned the following:

Etc., etc.

After we’ve read lines 56-62 we’re finished with Test.xml; therefore, we use this line of code to close the file and get on with our lives:

objFile.Close

So what exactly are we going to do with the rest of our lives? Well, the first thing we’re going to do is open the file C:\Scripts\New_test.xml; this is the file where we want to insert the lines we just read in. Notice, however, that we don’t open the file for writing; instead, we open it for reading:

Set objFile = objFSO.OpenTextFile(“C:\Scripts\New_test.xml”, ForReading)

What sort of madness is that? Well, unfortunately, we can’t simply replace text in a text file; the FileSystemObject won’t allow that. Therefore, what we need to do is construct a replacement file in memory: we need to read lines 1 through 55 in the current file, add the seven lines we copied from the first file, skip lines 56-62, and then add in the rest of New_test.xml. Confusing? You bet. But let’s walk through it and see if we can explain how that all works.

Reading lines 1 through 55 are easy: we just use this block of code, stashing the results in a variable named strNewText:

For i = 1 to 55
    strNewText = strNewText & objFile.ReadLine & vbCrLf
Next

Like we said, that was easy; we just did something very similar a few minutes ago. Once we have lines 1 through 55 safely stored away we can use this code to append the lines we copied from our first XML file:

strNewText = strNewText & strText

That gives us – in memory – a 62-line text file: the first 55 lines having been read in from New_test.xml, the last 7 lines having been copied from Test.xml. What we need to do now is grab the rest of New_test.xml, beginning with line 63 and continuing to the end of the file.

Why “beginning with line 63?” Well, remember, lines 56 through 62 are already in place; that’s what we copied from Test.xml. We need to skip lines 56 to 62 in New_test.xml, something we do using this block of code:

For i = 1 to 7
    objFile.SkipLine
Next

And then to get line 63 on through the end of the file we use this code:

Do Until objFile.AtEndOfStream
    strNewText = strNewText & objFile.ReadLine & vbCrLf
Loop

Make sense? If not, walk yourself through the script line-by-line. It might take a time or two, but it should become clear soon enough.

Once we’ve read through the rest of New_test.xml we close the file; that’s because the file was originally opened for reading, which means we have to close the thing and then immediately reopen it for writing. (It sounds silly to us, too. But it is what it is.) We use these two lines of code to close and reopen the file:

objFile.Close

Set objFile = objFSO.OpenTextFile(“C:\Scripts\New_test.xml”, ForWriting)

All we have to do now is write the contents of the variable strNewText to the file, close the file one last time, and then call it good.

Granted, it seems a little convoluted, but it works. And, again, the same basic approach works on any text file, not just on XML files.

We hope that helps, JA. And we hope that you – and everyone else – will come back tomorrow. As you probably know, tomorrow will be the 502^nd Hey, Scripting Guy! column, and we have a huge celebration planned.

Well, as long as you-know-who doesn’t find out, that is.

(Editor’s Note: Has anyone ever noticed that Greg has always kind of resembled a toad….)