Solved

Make a file test.vbs using vbscript

Posted on 2016-10-27
5
82 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 53

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 53

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 53

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

756 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