Printing all spreadsheet names only in a workbook.

Question

Is it possible to print ONLY the spreadsheets names (the names at the bottom on the tabs)? I have a workbook with quite a lot of spreasheets (over 500, er, it's a catalogue of music... CDs, what's on them, composors, conductors, movements, times etc.) and would like to do a print out of just the names of, not all and every part of the workbook. Yes I know I should more than likely do this in Access, but I started it on a Commidore 64 years ago and have just been 'converting' it as I go along. Latest version is in Excel 2007. So esentially it's a '2D database' done using spreadsheets, rather than a '3D database' done in Access. Many regards.

Answer 1

You can do this using Perl with Spreadsheet::ParseExcel module installed.

use Spreadsheet::ParseExcel;
use strict;

my $filename = shift || "Book1.xls";
my $e = new Spreadsheet::ParseExcel;
my $eBook = $e->Parse($filename);
my $sheets = $eBook->{SheetCount};
my ($eSheet, $sheetName);

foreach my $sheet (0 .. $sheets - 1) {
    $eSheet = $eBook->{Worksheet}[$sheet];
    $sheetName = $eSheet->{Name};
    print "Worksheet $sheet: $sheetName\n";
}

Answer 2

Using VBA you can pretty easily dump the list of sheet names to a new sheet in your workbook.

Sub PrintSheetNames()
   i=1
   For Each sht in Sheets
      Cells(i,1).Value = sht.Name
      i=i+1
   Next
End Sub

Go to any blank worksheet, and then run this macro. It will output all the worksheet names in the first column. You can then just print the list.

Answer 3

Assuming you using VBA and not .NET you could write a sub routine similar to this. The same objects and names are available in .NET.

Sub ShowTabNames()
    Dim s As Worksheet
    Dim tabs As String

    For Each s In ActiveWorkbook.Worksheets
        If Len(tabs) > 0 Then tabs = tabs & ", "
        tabs = tabs & s.Name
    Next

    MsgBox tabs
End Sub

Answer 4

You can make a OleDbConnection to your Excel workbook then call GetOleDbSchemaTable to get list of all tables which are nothing but list of excel sheets

ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

Answer 5

You can do this using Python with the xlrd module installed:

import xlrd, sys
filename = (sys.argv[1:2] or ['Book1.xls'])[0]
book = xlrd.open_workbook(filename, on_demand=True)
for name in book.sheet_names():
    print name # name is in Unicode; may need encoding

Note: the on_demand arg is new in version 0.7.1; it allows saving time and memory by not parsing all the worksheets up front. If you have an earlier version, either upgrade or remove on_demand=True in the snippet above.