Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Make a file test.vbs using vbscript

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
WeThotUWasAToad
Asked:
WeThotUWasAToad
  • 3
  • 2
2 Solutions
 
Joe Winograd, EE MVE 2015&2016DeveloperCommented:
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
 
WeThotUWasAToadAuthor Commented:
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
 
Joe Winograd, EE MVE 2015&2016DeveloperCommented:
> 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
 
WeThotUWasAToadAuthor Commented:
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
 
Joe Winograd, EE MVE 2015&2016DeveloperCommented:
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: 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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now