Solved

Make a file test.vbs using vbscript

Posted on 2016-10-27
5
42 Views
Last Modified: 2016-10-28
Hello,

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:
https://www.experts-exchange.com/questions/28955794/Create-a-list-of-all-sheet-tab-names-in-a-given-Excel-workbook.html

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

Thanks
0
Comment
Question by:WeThotUWasAToad
  • 3
  • 2
5 Comments
 
LVL 51

Accepted Solution

by:
Joe Winograd, EE MVE earned 500 total points
Comment Utility
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:

D:
cd \temp
cscript /nologo test.vbs

Regards, Joe
0
 

Author Comment

by:WeThotUWasAToad
Comment Utility
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).
0
 
LVL 51

Assisted Solution

by:Joe Winograd, EE MVE
Joe Winograd, EE MVE earned 500 total points
Comment Utility
> 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:

InputFile:="c:\temp\test.xlsx"
OutputFile:="c:\temp\test.txt"
FileDelete,%OutputFile%
oWorkbook:=ComObjGet(InputFile)
WorkheetsCount:=oWorkbook.Worksheets.Count
Loop,%WorkheetsCount%
{
  WorksheetName:=oWorkbook.Worksheets(A_Index).Name
  FileAppend,%WorksheetName%`n,%OutputFile%
}
MsgBox,4096,Number of worksheets: %WorkheetsCount%,Created this text file with worksheet names:`n%OutputFile%
ExitApp

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
0
 

Author Comment

by:WeThotUWasAToad
Comment Utility
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.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
You're very welcome, Steve. The Component Object Model (COM) native support is excellent in AHK. The documentation for it begins here:
https://autohotkey.com/docs/commands/ComObjActive.htm

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:
https://autohotkey.com/boards/viewtopic.php?f=7&t=8978

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:
https://www.experts-exchange.com/questions/28970468/Software-to-count-how-many-instances-of-words.html

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This article will show, step by step, how to integrate R code into a R Sweave document
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now