How Can I Repeatedly Prompt a User to Enter a File Name?

ScriptingGuy1

Hey, Scripting Guy! I have approximately 100 Excel files (all with names like 9365.xls) in a folder. I’d like to prompt a user for a file name, open the file, retrieve information from a specified cell, then close the file (ideally Excel would never appear on screen). I’d then like to prompt the user to enter another file name. How can I do that?

— TW

Hey, TW. You know, even though we’ve been writing this column for over two years now it never ceases to amaze us that people are brave enough to ask the Scripting Guys for help any time they need to do something practical. Why does that amaze us? Well, last night, for example, the Scripting Guy who writes this column decided it was time to bake something. The recipe he opted to use called for one egg white. Before going to the store for the rest of the ingredients he checked to see if the Scripting Family had any eggs; they had one. But that’s all he needed, right?

After returning from the store our hero began mixing the batter. Being as good a chef as he is a scripter, he broke his lone egg in half and, standing over the sink, carefully poured the egg from one eggshell to the other, meticulously separating the white from the yolk. When he was down, the egg had been perfectly separated: the yolk was left in the shell, and the egg white – the part he needed – had been washed down the drain. Gee; how … amusing ….

What that all boils down to is this: if you’d rather withdraw your question, TW, and ask someone a bit smarter, well, we understand; no hard feelings. If you’re willing to take a chance, however, here’s a script that (we hope) will do the trick:

Do Until i = 1
    strFile = InputBox(“Please enter the file name: “)

    If strFile = “” Then
        Exit Do
    End If
    strPath = “C:\Scripts\” & strFile
    Set objExcel = CreateObject(“Excel.Application”)
    Set objWorkbook = objExcel.Workbooks.Open(strPath)
    Set objWorksheet = objWorkbook.Worksheets(1)
    Wscript.Echo objWorksheet.Range(“Status”)
    objExcel.Quit
Loop

Let’s see if we can figure out how this baby works. As you can see, we start out by creating a Do loop designed to loop until the variable i is equal to 1. This is how we’re able to re-prompt the user over again: because the variable i will never be equal to 1 this loop will cause the script to run either for forever or until the user clicks Cancel in the InputBox or clicks OK without entering a file name, whichever comes first.

Speaking of the InputBox, the very first thing we do inside the Do loop is use the InputBox method to prompt the user to enter a file name:

strFile = InputBox(“Please enter the file name: “)

The InputBox itself stays onscreen until the user clicks OK or Cancel; when that happens the results are stored in a variable named strFile. If the user clicks OK strFile will contain the file name entered into the InputBox; if the user clicks Cancel strFile will be equal to an empty string. With that in mind our next task is to check the value of strFile. If strFile is equal to nothing (“””) we call the Exit Do statement and exit the loop:

If strFile = “” Then
    Exit Do
End If

But what if strFile isn’t equal to nothing? In that case we append the file name (that is, the value of strFile) to the string C:\Scripts\; this enables us to construct a full path to the file we want to open. (For example, if the user types 9365.xls into the InputBox the path we construct will end up being C:\Scripts\9365.xls.) We then use this block of code to create an instance of the Excel.Application object, open the file whose path has been stored in strPath, and then bind to the first worksheet in the workbook:

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(strPath)
Set objWorksheet = objWorkbook.Worksheets(1)

With the worksheet open our next task is to echo back the desired value. In this script, we’re assuming you’ve created a one-cell range named Status which contains the status information of interest. If that’s the case you can echo back the status information using code like this:

Wscript.Echo objWorksheet.Range(“Status”)

Alternatively, you can simply specify the address of the cell containing the status information. For example, if status is kept in cell A10 (that is, row 10, column 1) you can use this code to echo back the value of cell A10:

Wscript.Echo objWorksheet.Cells(10,1).Value

We then close Excel, loop around, and repeat the process. And no, Excel never does appear onscreen; instead, it runs invisibly in the background. That’s because we never set the Visible property to True.

That should get you started, TW. Ideally, you’d include some error-checking in here; for example, the script is doomed to fail if the file entered by the user doesn’t actually exist. Even better, you might consider popping up a File Open dialog box and allowing users to select a file without having to type in a file name. We won’t discuss this modified script today; for more information on adding a File Open dialog box to a script see our previous column on this very subject. As long as you’re running on Windows XP, however, you can accomplish the same feat using this script:

Do Until i = 1
    Set objDialog = CreateObject(“UserAccounts.CommonDialog”)
    objDialog.Filter = “Excel Spreadsheets|*.xls”
    objDialog.FilterIndex = 1
    objDialog.InitialDir = “C:\Scripts”
    intResult = objDialog.ShowOpen

    If intResult = 0 Then
        Exit Do
    Else
        strPath = objDialog.FileName
    End If
    Set objExcel = CreateObject(“Excel.Application”)
    Set objWorkbook = objExcel.Workbooks.Open(strPath)
    Set objWorksheet = objWorkbook.Worksheets(1)
    Wscript.Echo objWorksheet.Range(“Status”)
    objExcel.Quit
Loop

Incidentally, if anyone out there needs an egg yolk just let us know; we – alas – happen to have one we aren’t using.

ScriptingGuy1

Follow