Hey, Scripting Guy! How can I take the last line in a group of text files and paste that information into a Word document?

— JV

Hey, JV. You know, one thing we try to do in this column is focus on practical system administration tasks. We’ll deviate from that focus every now and then, but for the most part we try to address questions we feel a lot of people would be interested in. And, to be honest, when we first read this question, we didn’t believe it fell into that category.

And then we started thinking (something that we do do on occasion, following roughly the same schedule as Halley’s Comet). One thing we realized is that lots of log files include a bunch of detailed information and then, on the last line, have some sort of summarization. If you had a bunch of log files and you wanted to put together a summary report of some kind, one way to do that might be to grab the last line of each log file and then throw all those summary lines into a Word document. We don’t know if this is the kind of situation you’re facing, but we were so excited to have actually thought of something useful that we decided to go ahead and answer the question.

And here’s our answer:

Const ForReading = 1

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Logs’} Where ” _
        & “ResultClass = CIM_DataFile”)
For Each objFile In colFileList
    strFilePath = objFile.Name
    Set objTextFile = objFSO.OpenTextFile(strFilePath, ForReading)
    Do Until objTextFile.AtEndOfStream
        strLine = objTextFile.ReadLine
    Loop
    strMessage = strMessage & strLine & vbCrLf
    objTextFile.Close
Next
Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
Set objSelection = objWord.Selection
objSelection.TypeText strMessage

OK, so it’s a little long, but remember, we have several tasks to perform here: get all the files in a folder, open them up, grab the last line, start up Microsoft Word, and then paste all those lines into our Word document. Being able to do that in 20-some lines of code doesn’t seem too terribly bad.

The script starts out by defining a constant named ForReading and then assigning this constant the value 1; we’ll use ForReading each time we open and read a text file. Next we connect to the WMI service on the local computer. Which brings up an important question: does this script have to run on the local computer? And the answer is: not necessarily; after all, WMI can grab a collection of files off a remote computer just as easy as it can off the local computer.

Unfortunately, though, the FileSystemObject – the object we use to read the text files – doesn’t handle remote machines quite as nicely. We can still use it, but only if we reference the administrative share when opening the file. For example, to open the file Log1.txt in C:\Logs on the remote computer atl-fs-01 we’d have to use a path like this:

\\atl-fs-01\C$\Logs\Log1.txt

That’s doable, but you’ll have to add in some code to construct a path similar to that.

Now, let’s see, where were we … oh, right. After connecting to the WMI service we then use this query to retrieve a list of all the files in the C:\Logs folder:

Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Logs’} Where ” _
        & “ResultClass = CIM_DataFile”)

We should add that we’re assuming you want to read all the files in the folder. If that’s not the case, then you’ll need to add an additional line or two to filter out the files you don’t want. (For example, you might check the file extension and read the file only if it has a .log extension.)

As you might expect, the WMI query returns a collection of all the files in the C:\Logs folder; we then set up a For Each loop to walk through that collection. For each file in the collection we use this line of code to retrieve the file name (which is the WMI equivalent to the file path):

strFilePath = objFile.Name

Next we call the OpenTextFile method to open the file for reading. We then have this little block of code:

Do Until objTextFile.AtEndOfStream
    strLine = objTextFile.ReadLine
Loop

What are we doing here? Well, the FileSystemObject is not the most sophisticated text-parser in the world. For example, it can read a file only from the top to the bottom; there’s no way to tell it, “Just read the last line in this file.” Therefore, we have to read the file line-by-line. Each time we read a line we store the contents of that line in a variable named strLine (replacing whatever was previously in the variable). When we read the last line of the file (which is the line we want), the contents of that line (and only that line) will be stored in strLine.

Now that we have the last line of the file tucked away in the variable strLine, what next? Well, how about this line of code:

strMessage = strMessage & strLine & vbCrLf

Here we’re taking that last line and adding it to a variable named strMessage. Notice the equation: we’re assigning strMessage the value of whatever happens to be in strMessage plus the value of strLine plus a carriage return-linefeed (vbCrLf). In other words, suppose our first file has this as its last line:

File 1

The first time through the loop, strMessage will have the same value. Now, suppose the second file has the last line File 2. In that case, the second time through the loop strMessage will equal this:

File 1
File 2

As you can see, we’re simply constructing a list, in memory, of all the last lines as we go.

After adding the contents of strLine to the variable strMessage, we then close the first file, loop around, and repeat the process with the next file in the collection.

Once we’ve read all the files it’s time to start up Word. These three lines of code start an instance of the Word.Application object, make that instance visible onscreen, and then give us a new, blank document to work with:

Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
Set objDoc = objWord.Documents.Add()

All that’s left now is to create an instance of the Word Selection object and then use the TypeText method to type our text into the document:

Set objSelection = objWord.Selection
objSelection.TypeText strMessage

And what text are we typing? That’s right: the value of strMessage, the variable containing the last lines of each of our text files. Which means that, technically we’re not pasting that information into Word. But the net result is exactly the same. And considering the fact that we thought of this all by ourselves, well ….

Note. In testing this script we ran into a file that, when placed into Word, ended up with non-printing characters between each character in the line. To be honest, we didn’t have the time to investigate this more fully. However, if you run into that problem this revised version will largely fix things: it uses Word’s CleanString method to remove non-printing characters from the string. You might find the spacing to be a bit off, but the resulting line will still be easier to read.

Here’s the revised script, which includes only one additional line of code, the line that calls the CleanString method:

Const ForReading = 1

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Logs’} Where ” _
        & “ResultClass = CIM_DataFile”)
For Each objFile In colFileList
    strFilePath = objFile.Name
    Set objTextFile = objFSO.OpenTextFile(strFilePath, ForReading)
    Do Until objTextFile.AtEndOfStream
        strLine = objTextFile.ReadLine
    Loop
    strMessage = strMessage & strLine & vbCrLf
    objTextFile.Close
Next
Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
strMessage = objWord.CleanString(strMessage)
Set objSelection = objWord.Selection
objSelection.TypeText strMessage