Hey, Scripting Guy! How Can I Load a Drop-Down List From a Text File?

Hey, Scripting Guy! Question

Hey, Scripting Guy! I have a text file that has a list of different store numbers and I would like to be able to populate a drop down list of an HTA.

— MH

SpacerHey, Scripting Guy! AnswerScript Center

Hi MH,

The basic task of reading data from a text file and using it to populate a drop-down list is pretty straightforward. Below is the code for an HTML Application (HTA) that does just that. It gets the phone number data from a file named numbers.txt. The numbers.txt file includes a single phone number on each line. this:

555-333-3833
555-666-6666
555-777-7777

Note: If you aren’t already familiar with HTA’s, this code might look more complicated than it actually is. Much of the code is just boilerplate that is required in any HTA. Click here to visit the HTA Developers Center and check out the HTA for Beginners series to get up to speed quickly.

<html>
<head>
<title>Phone Numbers</title>

<HTA:APPLICATION ID=”objHTA” APPLICATIONNAME=”PhoneNumbers” SCROLL=”yes” SINGLEINSTANCE=”yes” > </head>

<script language=”VBScript”>

Sub Window_Onload LoadDropDown End Sub

Sub LoadDropDown Set objFS = CreateObject(“Scripting.FileSystemObject”) Set objFile = objFS.OpenTextFile(“numbers.txt”) strPhoneNumbers = objFile.ReadAll objFile.Close arrPhoneNumbers = Split(strPhoneNumbers,vbNewLine) For Each strNumber in arrPhoneNumbers Set objOption = Document.createElement(“OPTION”) objOption.Text = strNumber objOption.Value = strNumber PhoneNumbers.Add(objOption) Next End Sub

</script>

<body> <select name=”PhoneNumbers”> </select> </body> </html>

The real work in the script takes place in the LoadDropDown subroutine. Here we use the FileSystemObject to open the file and read all of its contents into the strPhoneNumbers variable. We then close the file. We have what we want from it and don’t want to hold it open unnecessarily. Then we use the VBScript Split function to parse it into lines, which we store in the arrPhoneNumbers variable.

Lastly, we use the For Each construct to do something to each of the lines, which in this case contain phone numbers. We use Document.createElement to make a new option corresponding to each of the numbers and then we set the value of both the Text and Value properties of the option to the phone number currently being processed. The Add method is used to actually put the newly-constructed option in the drop-down list.

This appears to work fine and we could probably just stop here and call the question answered. But, there are potential problems with this simple solution. Notice that we call our LoadDropDown subroutine from within the Window_Onload event handler. That means that this text file is read and the drop-down list is populated only when the window loads. What if someone or some program changes the data in the numbers.txt file? Your HTA will still happily display the stale data. This might cause serious problems, depending on exactly what the HTA is being used for.

Let’s assume that we need the HTA drop-down to be kept reasonably in-sync with the data in the text file. There’s more than one approach we could take. (And none of them involve removing a feline’s skin.) We’ll begin by working through a polling approach. Not because I think it’s the best for all (or even most) situations. But because it could be useful in some situations and it surfaces a number of additional little quirks for us to work through. And it’s exactly those sorts of quirks that you encounter when you are trying to script a real-world problem.

The following script is the same as the previous one, except it includes the following line of code.

iTimerID = window.setInterval(“LoadDropDown”, 5000)

This instruction is run when the window loads and it causes the LoadDropDown subroutine to run every five seconds (5000 milliseconds) thereafter. Notice that we still call LoadDropDown directly before this new instruction to load the list the first time.

<html>
<head>
<title>Phone Numbers</title>

<HTA:APPLICATION ID=”objHTA” APPLICATIONNAME=”PhoneNumbers” SCROLL=”yes” SINGLEINSTANCE=”yes” > </head>

<script language=”VBScript”>

Sub Window_Onload LoadDropDown iTimerID = window.setInterval(“LoadDropDown”, 5000) End Sub

Sub LoadDropDown Set objFS = CreateObject(“Scripting.FileSystemObject”) Set objFile = objFS.OpenTextFile(“numbers.txt”) strPhoneNumbers = objFile.ReadAll objFile.Close arrPhoneNumbers = Split(strPhoneNumbers,vbNewLine) For Each strNumber in arrPhoneNumbers Set objOption = Document.createElement(“OPTION”) objOption.Text = strNumber objOption.Value = strNumber PhoneNumbers.Add(objOption) Next End Sub

</script>

<body> <select name=”PhoneNumbers”> </select> </body> </html>

Do you see any problems with the logic in the above script? If you don’t, run the HTA and wait 30 seconds or so – then look at the contents of the drop-down list. It’ll be longer then we were aiming for. This is an example of one of those little quirks I mentioned.

The script calls LoadDropDown periodically, which uses Add each time to add items to the drop-down list. But it doesn’t check to see if the items being added are already there. So redundant items are added every five seconds and the list would be pretty darn long after a few hours. Given that the drop-down list isn’t going to contain that many numbers, the cleanest approach is to just clear the list at the beginning of the LoadDropDown subroutine.

The following subroutine does that:

Sub ClearListbox
    For Each objOption in PhoneNumbers.Options
       objOption.RemoveNode
    Next
End Sub

So, we add the code for that new subroutine and call it at the beginning of LoadDropDown, giving us this script.

<html>
<head>
<title>Phone Numbers</title>

<HTA:APPLICATION ID=”objHTA” APPLICATIONNAME=”PhoneNumbers” SCROLL=”yes” SINGLEINSTANCE=”yes” > </head>

<script language=”VBScript”>

Sub Window_Onload LoadDropDown iTimerID = window.setInterval(“LoadDropDown”, 5000) End Sub

Sub LoadDropDown ClearListBox Set objFS = CreateObject(“Scripting.FileSystemObject”) Set objFile = objFS.OpenTextFile(“numbers.txt”) strPhoneNumbers = objFile.ReadAll objFile.Close arrPhoneNumbers = Split(strPhoneNumbers,vbNewLine) For Each strNumber in arrPhoneNumbers Set objOption = Document.createElement(“OPTION”) objOption.Text = strNumber objOption.Value = strNumber PhoneNumbers.Add(objOption) Next End Sub

Sub ClearListbox For Each objOption in PhoneNumbers.Options objOption.RemoveNode Next End Sub

</script>

<body> <select name=”PhoneNumbers”> </select> </body> </html>

This is almost good enough. Test it out. Make some changes to numbers.txt and see if the HTA updates the drop-down list accordingly – and without duplicates. What? Well, it worked on my computer at least once!

But, there is yet another quirk. It’s the kind of thing that would be perfectly obvious to any user almost right away, but that you could easily miss as the script writer. Once you see that the drop-down is displaying the contents of the text file, you’re so happy that you got it to work that you, unconsciously, don’t want to find out what is still not working about it. At least that’s what I do. But you have to get past that. As soon as you get something doing what you want it to do, you should have a small celebration; perhaps just take a leisurely walk to the coffee machine or maybe sip from a bottle of good Cognac. Whatever works for you. When you return from the celebration, you should attack your work and figure out what’s wrong with it. It’s an immutable law of the universe: There is something wrong with the script you just wrote. Follow Smokey the Scripting Bear’s motto, “Accept the Law and Fix the Flaw.”

Our flaw (OK, one of our flaws that I noticed – admittedly, after the Cognac) surfaces when we select a telephone number in the drop-down list and then wait for a refresh of the data. Our selection is lost and the first phone number in the list appears selected by default. When we reload the drop-down list, we need to retain information about which value was selected and then restore that selection after the data update.

We could certainly go down that road, but as we mentioned earlier, there are other approaches we could take to this problem. And when enough quirks start surfacing in your chosen path, that’s exactly when you should at least consider another path. The quirk in our polling approach gives us a strong hint about what else we might try.

We don’t really want the drop-down data changing every five seconds. Ideally, after fixing the quirk so that the selected option remained selected, we would not even know the data was updated. Not until we select the drop-down. Hey, wait a minute. Maybe that’s what we should do. Let’s update the drop-down data only when the user actually selects the drop-down. That takes care of retaining the current selection and it simplifies our code.

Here’s the script with that change incorporated:

<html>
<head>
<title>Phone Numbers</title>

<HTA:APPLICATION ID=”objHTA” APPLICATIONNAME=”PhoneNumbers” SCROLL=”yes” SINGLEINSTANCE=”yes” > </head>

<SCRIPT Language=”VBScript”>

Sub Window_Onload LoadDropDown End Sub

Sub LoadDropDown

ClearListBox Set objFS = CreateObject(“Scripting.FileSystemObject”) Set objFile = objFS.OpenTextFile(“numbers.txt”) strPhoneNumbers = objFile.ReadAll objFile.Close arrPhoneNumbers = Split(strPhoneNumbers, vbNewLine) For Each strNumber in arrPhoneNumbers Set objOption = Document.createElement(“OPTION”) objOption.Text = strNumber objOption.Value = strNumber PhoneNumbers.Add(objOption) Next

End Sub

Sub ClearListbox For Each objOption in PhoneNumbers.Options objOption.RemoveNode Next End Sub

</SCRIPT>

<body> <select onActivate=LoadDropDown name=”PhoneNumbers”> </select> </body> </html>

We now load the data when the window is loaded (Window_OnLoad) and when the user selects the drop-down (onActivate). Of course, we’ve removed the setInterval statement that triggered the polling.

This is a decent solution. Try it. Update the data in the file and then select the drop-down. Tada! Fresh, just-in-time data. And if we have something selected and don’t mess with it, it’ll stay selected. But what if the thing we have selected is changed in the underlying data file? Geesh! You had to ask that. Well, you can click on the drop-down anytime you want to ensure that you’re looking at the latest data. Or, using what we just learned about polling, you could script up something more refined. We’d like to help, but we’re anything but refined around here and, besides, it’s time for another one of those little celebrations.

0 comments

Comments are closed. Login to edit/delete your existing comments