Make a file test.vbs using vbscript

Posted on 2016-10-27
Medium Priority
Last Modified: 2016-10-28

This is a bit embarrassing but a while back I received a solution from an expert but now I can't remember how to use it.

The thread is here:

I understand how to copy & paste the code :) and how to "change line 3" to my Excel filename but I don't know how to do this:
Using vbscript, make a file test.vbs with this code:

or this
Run it…
cscript /nologo test.vbs

Question by:WeThotUWasAToad
  • 3
  • 2
LVL 58

Accepted Solution

Joe Winograd - EE Fellow & MVE earned 2000 total points
ID: 41863342
Hi Steve,

> Using vbscript, make a file test.vbs with this code:

What he meant by "Using vbscript" is that he was using VBScript as the coding language. He did not mean to use VBScript to create the test file. The test.vbs file is simply a plain text file with the VBS code (the program) in it. You create that with any plain text editor, such as Notepad. In other words, run Notepad, copy/paste his code into it, and do a Save As to test.vbs (or whatever you want to call it, as long as the file extension is vbs).

> Run it ... cscript /nologo test.vbs

Open up a command prompt via Start>All Programs>Accessories>Command Prompt or via Start>Run>cmd or however you like to do it. Then enter this in the command prompt window:

cscript /nologo test.vbs

You should either fully qualify the script's file name with the full path, such as:

cscript /nologo d:\temp\test.vbs

or first navigate in the command prompt to the folder that has the script, such as:

cd \temp
cscript /nologo test.vbs

Regards, Joe

Author Comment

ID: 41863864
Thanks a bunch Joe. That did the trick.

After following your steps, the list of sheet tab names appeared in the command prompt window. So now the question I have is:

How do I get that list into a text file so it's usable?

I tried double-clicking the test.vbs file thinking the list might be there but that resulted in a long sequence of small message boxes (one for each sheet tab name).
LVL 58

Assisted Solution

by:Joe Winograd - EE Fellow & MVE
Joe Winograd - EE Fellow & MVE earned 2000 total points
ID: 41864006
> That did the trick.

Glad to hear it.

> How do I get that list into a text file so it's usable?

I'm not a VBS expert, so can't help you with that. I took the liberty of adding VBScript and Scripting Languages as Topics to your question, which should attract the VBS experts. Also, I know that you use AutoHotkey (my scripting language of choice), so I wrote the script you're looking for in AHK:

MsgBox,4096,Number of worksheets: %WorkheetsCount%,Created this text file with worksheet names:`n%OutputFile%

Open in new window

As with VBS, simply copy/paste that code into Notepad (or whatever plain text editor you use) and do a Save As to whatever file name you want, but make sure the file type is AHK. Then run it as you would any other AHK script (probably just double-clicking it, if you did a standard AutoHotkey install). Of course, change the InputFile and OutputFile variables to whatever you want.

Btw, if you send NVIT a message via the EE Message system with a link to this question, I'm confident that he'll jump in here. Regards, Joe

Author Comment

ID: 41864604
Ahhh, that puts me on familiar ground Joe.

I've never used AHK for something like this before so I'm doubly happy because you have given me several new commands to learn.

I did recognize enough of the script you posted to get the job done so now I've got a great new tool in my bag of tricks.

I sometimes find myself with more Excel worksheet tabs than I can keep track of. So in those cases, I like to create an Index sheet and then use =HYPERLINK() to create links to all the other tabs. Getting the list of sheet tab names in the first place is the first step so this will save me a bunch of time.

Thanks as always for your solid and well-explained comments.
LVL 58

Expert Comment

by:Joe Winograd - EE Fellow & MVE
ID: 41864617
You're very welcome, Steve. The Component Object Model (COM) native support is excellent in AHK. The documentation for it begins here:

The doc is good, with many examples, but an even better place to start learning about it is kon's tutorial, MS Office COM Basics:

You may use COM for more than just Excel, including Outlook, PowerPoint, Publisher, and Word. For example, here's an EE thread that I'm participating in where I wrote an AHK script that utilizes Word's COM calls:

As you noted, COM is a great new tool to have in your bag of tricks! Regards, Joe

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question