Solved

Make a file test.vbs using vbscript

Posted on 2016-10-27
5
62 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 52

Accepted Solution

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

D:
cd \temp
cscript /nologo test.vbs

Regards, Joe
0
 

Author Comment

by:WeThotUWasAToad
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).
0
 
LVL 52

Assisted Solution

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

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
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.
0
 
LVL 52

Expert Comment

by:Joe Winograd, EE 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:
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

937 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

4 Experts available now in Live!

Get 1:1 Help Now