Hey, Scripting Guy! I have an HTA that includes five text boxes. How can I save the values in those text boxes to a tab-separated values file?
— EL
Hey, EL. According to their itinerary, the Scripting Guys should land in Paris sometime around 8:00 AM on Friday, November 9th. OK, technically, Paris isn’t exactly Barcelona, and the Scripting Guys are supposed to be headed for Barcelona and TechEd IT Forum. However, in order to keep travel costs to a minimum they had to stop in Paris on the way and, seeing as how Scripting Guy Jean Ross has never been to Paris, they decided to take in the sights before moving on to Barcelona.
With that in mind, their plan is to check into their hotel, then spend the day seeing the city: Notre Dame, the Louvre, and, last but surely not least, the Eiffel Tower.
|
Note. Assuming, of course, that Scripting Guy Jean Ross can fight her way past the paparazzi and the throngs of adoring fans. And no, Scripting Guy Greg Stemp doesn’t expect to have the same problem. Or at least he didn’t the first time he visited Paris. |
The Scripting Guys have always had a soft spot in their hearts for the Eiffel Tower. After all, no sooner was it built than the Eiffel Tower was branded an eyesore, and angry Parisians demanded that it be torn down. According to legend, novelist Guy de Maupassant ate lunch at the Tower restaurant every day. When asked why, he replied that the restaurant was the one place in Paris he could sit without having to see the Tower.
Today, of course, the Eiffel Tower is the symbol of Paris, and one of the most recognized and beloved structures in the world.
So why do the Scripting Guys care about that? Well, the story of the Eiffel Tower so closely parallels the story of the Scripting Guys and the Script Center. No sooner did the Scripting Guys put up the Script Center then it was branded an eyesore, and angry Microsoft employees demanded that it be torn down. Today, of course, the Script Center is one of the most belov — well, anyway, at least it’s still up, huh?
Incidentally, he Tower itself consists of 18,038 pieces of iron held together with well over two million rivets. That’s an impressive piece of work. But, to be honest, it’s nowhere near as impressive as a script that can save HTA text box values to a tab-separated values file:
<html> <head> <title>Save as TSV File</title> </head><SCRIPT Language=”VBScript”> Sub SaveData Set objFSO = CreateObject(“Scripting.FileSystemObject”) If objFSO.FileExists(“C:\Scripts\Test.tsv”) Then Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.tsv”, 8) strLine = UserName.Value & vbTab & Email.Value & vbTab & _ Title.Value & vbTab & Country.Value & vbTab & Product.Value objFile.WriteLine strLine objFile.Close Else Set objFile = objFSO.CreateTextFile(“C:\Scripts\Test.tsv”) strLine = UserName.Value & vbTab & Email.Value & vbTab & _ Title.Value & vbTab & Country.Value & vbTab & Product.Value objFile.WriteLine strLine objFile.Close End If End Sub </SCRIPT>
<body> <input type=”text” name=”UserName” size=”25″><p> <input type=”text” name=”Email” size=”25″ ><p> <input type=”text” name=”Title” size=”25″><p> <input type=”text” name=”Country” size=”25″><p> <input type=”text” name=”Product” size=”25″><p> <input type=”button” value=”Run Button” onClick=”SaveData”> </body>
This is actually a much simpler script than it might first appear. To begin with the <BODY> of the HTA consists of 5 text boxes and a button. All the text boxes have a unique name (like UserName) and just for the heck of it, each text box has been given a size of 25 characters. (Which simply determines the width of the box, and has nothing to do with the number of characters than can be typed in the text box.) For example, here’s the tag for the UserName text box:
<input type=”text” name=”UserName” size=”25″><p>
Our button is equally simple: it has the value (label) Run Button, and is configured to respond to the onClick event:
<input type=”button” value=”Run Button” onClick=”SaveData”>
As you can see, each time the button is clicked, the SaveData subroutine will be executed.
Coincidentally enough, we were just going to talk about the SaveData subroutine. The first thing we do inside this subroutine is create an instance of the Scripting.FileSystemObject; that’s the object we need to use in order to write to a text file. Once we have this object in hand, we then use the FileExists method to determine whether or not the file C:\Scrips\Test.tsv already exists:
If objFSO.FileExists(“C:\Scripts\Test.tsv”) Then
Why do we bother with that? Well, if the file exists then we want to simply append new data to the end of the file; if the file doesn’t exist then we need to first create the file and then add our data to it. The FileSystemObject, as many of you know, is a fickle little thing: if you aren’t careful about how you use the FileSystemObject to open a text file you might get very unexpected results. As system administrators, we don’t like very unexpected results. Thus we first check to see whether or not Test.tsv exists, then take the appropriate course of action.
Let’s assume that Test.tsv does exist. In that case, we use this line of code to open the file for appending (that’s what the second parameter, the 8, is for):
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.tsv”, 8)
Once the file is open we use this line of code to construct the line to be appended to the file:
strLine = UserName.Value & vbTab & Email.Value & vbTab & _
Title.Value & vbTab & Country.Value & vbTab & Product.Value
All we’re doing here is stringing together the Values of our five text boxes, taking care to separate each value by a tab (that’s what the VBScript constant vbTab is for). There’s nothing fancy here at all: we’re taking the Value of the UserName textbox and then adding a tab onto the end of that value; then adding the value of he Email textbox and adding a tab onto the end of that; then – well, you can probably figure out the rest for yourself. When we’re all done, that means the variable strLine will have a value similar to this:
Kenmyer kmyer@fabrikam.cim Manager US X492
From there we call the WriteLine method to append this value to the text file, then call the Close method to close that text file.
The process is similar if the file Test.tsv doesn’t exist. The only real difference is that, in that case, we don’t open the file for appending. (Why not? Because the file doesn’t exist.) Instead, we call the CreateTextFile method and create the file Test.tsv:
Set objFile = objFSO.CreateTextFile(“C:\Scripts\Test.tsv”)
Once we’ve done that we can construct the line to be added to the file, use WriteLine to write that line to the file, and then use the Close method to close the file. Game, set, and match.
As for the Scripting Guys, well, while you’re sitting in your office, drinking cold coffee and pretending to work, with any luck at all the Scripting Guys are eating crepes and headed towards the Louvre. What’s that? Really? You say that the Mona Lisa is at the Louvre? Well, whatta you know? To be honest, we weren’t planning on staying that long; we figured we’d only stay long enough to drop off today’s column, give the curators permission to display the thing, and then head for Notre Dame. But maybe we’ll have to see what else is at the Louvre besides Scripting Guys scripts. Might be fun.
And after that? Then it’s on to Barcelona!
Well, after we go to the Eiffel Tower, that is. And Versailles, we have to go out to Versailles. And after we eat a few more crepes, maybe a lot more crepes. But, sooner or later, it’s on to Barcelona. See you all on Monday!
0 comments