Retrieving list of worksheets programmatically

  • Thread starter Thread starter Scott Bass
  • Start date Start date
S

Scott Bass

Hi,

Sorry if this is a FAQ. I did search a number of Excel newsgroups and
Google hits before posting.

Say I have an Excel spreadsheet C:\Temp\My_Worksheet.xls, and it contains
the worksheets "Foo", "Bar", and "Foo Bar". Is there a simple script I can
write that will retrieve the names of the worksheets?

Something like:

VB:
wscript GetWorksheetNames.vbs "C:\Temp\My_Worksheet.xls"

C#:
GetWorksheetNames.exe "C:\Temp\My_Worksheet.xls"

In both cases, it would return:

Foo
Bar
Foo Bar

to stdout.

I'd like it to be as fast and efficient as possible, so would prefer an .exe
over a script if possible. However, I'm a novice with VB and C#
programming, but otherwise an OK programmer.

FYI, this is just to integrate the Excel data with another language (SAS).
SAS can import Excel data automatically, but cannot determine the name of
the worksheets; you have to hardcode the worksheet names. If I can write
some code that queries the spreadsheet for its worksheet names, I can use
that as "glue" to build the proper syntax in SAS to import the Excel data.
So, I don't need to get at the data itself, just the worksheet names.

Thanks for any input you can provide.

Regards,
Scott
 
For Each sh In Workbooks("My_Worksheet.xls").Worksheets
Debug.Print sh.Name
Next sh

The workbook in question needs to be open.
 
Thanks Bob, I appreciate the reply.

I've trolled around on Google some more, and this is what I've got so far:

Option Explicit
Dim filePath, oExcel, oSheet, sheetName

filePath = WScript.Arguments(0)
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
sheetName = oSheet.Name

Dim StdIn, StdOut
Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

StdOut.WriteLine (sheetName)

oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
set oSheet = Nothing
Set oExcel = Nothing

Can someone help me wrap this in a loop? In pseudocode:

Do i=1 to <number of worksheets>
Set oSheet = oExcel.ActiveWorkbook.Worksheets(i)
sheetName = oSheet.Name
StdOut.WriteLine (sheetName)
Loop ' end loop

Like I said, I don't know VB, so if you want to provide error checking for
missing argument and argument file doesn't exist, that would be fantastic.

Lastly, can I plug the code into VisualStudio .Net and create an EXE for
this?

[Slightly off topic: I have to invoke this as:
cscript //B getWorksheetNames.vbs C:\Temp\test.xls

What's the difference between wscript and cscript? It took me a while to
discover that the above barfs in wscript (the default) but works in cscript.
Why are there two scripting engines?]

Thanks
Scott
 
Scott Bass said:
Thanks Bob, I appreciate the reply.

I've trolled around on Google some more, and this is what I've got so far:

Option Explicit
Dim filePath, oExcel, oSheet, sheetName

filePath = WScript.Arguments(0)
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
sheetName = oSheet.Name

Dim StdIn, StdOut
Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

StdOut.WriteLine (sheetName)

oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
set oSheet = Nothing
Set oExcel = Nothing

Can someone help me wrap this in a loop? In pseudocode:

Do i=1 to <number of worksheets>
Set oSheet = oExcel.ActiveWorkbook.Worksheets(i)
sheetName = oSheet.Name
StdOut.WriteLine (sheetName)
Loop ' end loop
Click to expand...

It's almost done.

For i = 1 To oExcel.ActiveWorkbook.Worksheets.Count
Set oSheet = oExcel.ActiveWorkbook.Worksheets(i)
sheetName = oSheet.Name
StdOut.WriteLine (sheetName)
Next i

Like I said, I don't know VB, so if you want to provide error checking for
missing argument and argument file doesn't exist, that would be fantastic.

Lastly, can I plug the code into VisualStudio .Net and create an EXE for
this?
Click to expand...

No idea, don't do .Net

[Slightly off topic: I have to invoke this as:
cscript //B getWorksheetNames.vbs C:\Temp\test.xls

What's the difference between wscript and cscript? It took me a while to
discover that the above barfs in wscript (the default) but works in cscript.
Why are there two scripting engines?]
Click to expand...

There aren't. cscript.exe is a console application that runs inside an
MS-DOS box, whereas wscript.exe is a windows application.As I understand,
there are no specific differences between them, they are just targeted at
different platforms.
 
Back
Top