{"id":66183,"date":"2006-10-25T11:32:00","date_gmt":"2006-10-25T11:32:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/10\/25\/how-can-i-repeatedly-prompt-a-user-to-enter-a-file-name\/"},"modified":"2006-10-25T11:32:00","modified_gmt":"2006-10-25T11:32:00","slug":"how-can-i-repeatedly-prompt-a-user-to-enter-a-file-name","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-repeatedly-prompt-a-user-to-enter-a-file-name\/","title":{"rendered":"How Can I Repeatedly Prompt a User to Enter a File Name?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! I have approximately 100 Excel files (all with names like 9365.xls) in a folder. I\u2019d 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\u2019d then like to prompt the user to enter another file name. How can I do that?

— TW<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, TW. You know, even though we\u2019ve 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\u2019s all he needed, right?<\/P>\n

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 \u2013 the part he needed \u2013 had been washed down the drain. Gee; how \u2026 amusing \u2026.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Fortunately, the Scripting Guy who writes this column is used to doing things like that; in fact, he finds himself running back to the store so often that they\u2019ve given him his very own parking place.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

What that all boils down to is this: if you\u2019d rather withdraw your question, TW, and ask someone a bit smarter, well, we understand; no hard feelings. If you\u2019re willing to take a chance, however, here\u2019s a script that (we hope) will do the trick:<\/P>

Do Until i = 1\n    strFile = InputBox(“Please enter the file name: “)<\/p>\n
    If strFile = “” Then\n        Exit Do\n    End If<\/p>\n
    strPath = “C:\\Scripts\\” & strFile<\/p>\n
    Set objExcel = CreateObject(“Excel.Application”)\n    Set objWorkbook = objExcel.Workbooks.Open(strPath)\n    Set objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
    Wscript.Echo objWorksheet.Range(“Status”)\n    objExcel.Quit\nLoop\n<\/PRE>\n
Let\u2019s 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<\/I> is equal to 1. This is how we\u2019re able to re-prompt the user over again: because the variable i<\/I> will never<\/I> be equal to 1 this loop will cause the script to run either for forever or until the user clicks Cancel<\/B> in the InputBox or clicks OK<\/B> without entering a file name, whichever comes first. <\/P>\n
Speaking of the InputBox, the very first thing we do inside the Do loop is use the InputBox<\/B> method to prompt the user to enter a file name:<\/P>
strFile = InputBox(“Please enter the file name: “)\n<\/PRE>\n
The InputBox itself stays onscreen until the user clicks OK<\/B> or Cancel<\/B>; when that happens the results are stored in a variable named strFile. If the user clicks OK<\/B> strFile will contain the file name entered into the InputBox; if the user clicks Cancel<\/B> 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 (\u201c\u201d\u201d) we call the Exit Do<\/B> statement and exit the loop:<\/P>
If strFile = “” Then\n    Exit Do\nEnd If\n<\/PRE>\n
But what if strFile isn\u2019t<\/I> equal to nothing? In that case we append the file name (that is, the value of strFile) to the string C:\\Scripts\\<\/B>; this enables us to construct a full path to the file we want to open. (For example, if the user types 9365.xls<\/B> into the InputBox the path we construct will end up being C:\\Scripts\\9365.xls<\/B>.) We then use this block of code to create an instance of the Excel.Application<\/B> object, open the file whose path has been stored in strPath, and then bind to the first worksheet in the workbook:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nSet objWorkbook = objExcel.Workbooks.Open(strPath)\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
With the worksheet open our next task is to echo back the desired value. In this script, we\u2019re assuming you\u2019ve created a one-cell range named Status<\/I> which contains the status information of interest. If that\u2019s the case you can echo back the status information using code like this:<\/P>
Wscript.Echo objWorksheet.Range(“Status”)\n<\/PRE>\n
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:<\/P>
Wscript.Echo objWorksheet.Cells(10,1).Value\n<\/PRE>\n
We then close Excel, loop around, and repeat the process. And no, Excel never does<\/I> appear onscreen; instead, it runs invisibly in the background. That\u2019s because we never set the Visible<\/B> property to True.<\/P>\n
That should get you started, TW. Ideally, you\u2019d include some error-checking in here; for example, the script is doomed to fail if the file entered by the user doesn\u2019t actually exist. Even better, you might consider popping up a File Open<\/B> dialog box and allowing users to select a file without having to type in a file name. We won\u2019t discuss this modified script today; for more information on adding a File Open<\/B> dialog box to a script see our previous column<\/B><\/A> on this very subject. As long as you\u2019re running on Windows XP, however, you can accomplish the same feat using this<\/I> script:<\/P>
Do Until i = 1\n    Set objDialog = CreateObject(“UserAccounts.CommonDialog”)\n    objDialog.Filter = “Excel Spreadsheets|*.xls”\n    objDialog.FilterIndex = 1\n    objDialog.InitialDir = “C:\\Scripts”\n    intResult = objDialog.ShowOpen<\/p>\n
    If intResult = 0 Then\n        Exit Do\n    Else\n        strPath = objDialog.FileName\n    End If<\/p>\n
    Set objExcel = CreateObject(“Excel.Application”)\n    Set objWorkbook = objExcel.Workbooks.Open(strPath)\n    Set objWorksheet = objWorkbook.Worksheets(1)\n    Wscript.Echo objWorksheet.Range(“Status”)\n    objExcel.Quit\nLoop\n<\/PRE>\n
Incidentally, if anyone out there needs an egg yolk just let us know; we \u2013 alas \u2013 happen to have one we aren\u2019t using.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have approximately 100 Excel files (all with names like 9365.xls) in a folder. I\u2019d 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\u2019d then like to prompt the user to […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,22,3,4,5],"class_list":["post-66183","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-retrieving-input","tag-scripting-guy","tag-scripting-techniques","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have approximately 100 Excel files (all with names like 9365.xls) in a folder. I\u2019d 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\u2019d then like to prompt the user to […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66183","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66183"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66183\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}