スポンサーリンク

Excelブックの「シート目次」を,自動的に作成するバッチ (WSH/JScriptで,各シートへのリンク付きの目次を自動生成)


Excelブックの目次を,自動生成するバッチ。


目次の作り方としては,下記のバッチに対してExcelブックのファイル名を渡すだけ。

ワンクリックで,1枚目のシートに目次情報が書き込まれる


書きこまれる目次情報は,全シートの…

  • シート名
  • シート番号
  • シートへのハイパーリンク

ハイパーリンクつきなので,目次が「リンク集」の役目も果たす。

バッチのソースコード

/*
	Excelファイルに目次を作成するWSHバッチ

	使い方:
		・jsファイルをExcelファイルと同一フォルダに設置
		・対象ブックは閉じてあること
		・下記のコマンドを実行
			cscript create_excel_index.js ファイル名
	
	備考:
		・Excel 2003, 2007両方対応
*/


/* --------------- 設定項目 --------------- */


// 目次シートは1シート目に存在すると想定

// 目次シートの何行目からシート名の記載を開始するか
var index_y_offset = 4;

// 目次を記載する範囲
var str_index_cells_range = "B" 
	+ index_y_offset 
	+ ":D" 
	+ ( index_y_offset + 100 )
;


/* --------------- セットアップ --------------- */


// 引数取得
if( WScript.Arguments.length == 0 )
{
	WScript.Echo("引数がありません。");
	WScript.Quit();
}
var filename = WScript.Arguments.Unnamed(0);
var ws = WScript.CreateObject("WScript.Shell");
var cwd = ws.CurrentDirectory;
var filepath = cwd + "\\" + filename;



// Excelブックを開く
var excel = WScript.CreateObject("Excel.Application");
excel.Visible = true;
excel.Workbooks.Open( filepath );
var book = excel.Workbooks( excel.Workbooks.Count ); // ワークブックを取得
var xlMaximized = -4137;
excel.ActiveWindow.WindowState = xlMaximized; // 最大化


/* --------------- 情報取得 --------------- */


// 全シートをスキャンして,シート名を調査
var sheets = book.WorkSheets;
var e = new Enumerator( sheets );
var sheet_counter = 0;
var index_sheet = null;
var arr_sheet_names = [];
for( ; ! e.atEnd() ;  e.moveNext() )
{
	// シートを取得
	var sheet = e.item();
	
	if( sheet_counter == 0 )
	{
		// 目次シートであれば,シート自体を保持
		index_sheet = sheet;
	}
	else
	{
		// シート名を保持
		arr_sheet_names.push( sheet.Name );
	}
	
	// 次のシートへ
	sheet_counter ++;
}


/* --------------- 目次作成 --------------- */


// 目次シートに対して,目次を書き込み
var index_range = index_sheet.Range( str_index_cells_range );
index_range.ClearContents(); // 内容をクリア
index_range.Resize(1, 1).Select(); // rangeの左上を選択
var xlCenter = -4108;
for( var i = 0; i < arr_sheet_names.length; i ++ )
{
	var sheet_name = arr_sheet_names[ i ];
	var active_cell = excel.ActiveCell;
	
	// 番号
	active_cell.Value = ( i + 1 );
	
	// シート名
	active_cell.Offset( 0, 1 ).Value = sheet_name;
	
	// リンク
	active_cell.Offset( 0, 2 ).Formula = '=HYPERLINK("#\''
		+ sheet_name
		+ '\'!A1", "●")'
	;
	active_cell.Offset( 0, 2 ).HorizontalAlignment = xlCenter;
	
	
	// 次の行へ
	active_cell.Offset( 1, 0 ).Select();
}


/* --------------- 終了 --------------- */


// ブックを保存して終了
excel.DisplayAlerts = false; // 既存ファイルがあっても上書きする
book.SaveAs( filepath );
excel.Quit();
excel = null;
WScript.Echo( "目次の作成が終了しました。" );


参考:

JScript/WSH で,Excelファイルを読み書きしよう
http://language-and-engineering.hatenablog.jp/entry/20090717/p1

  • WScript.CreateObject("Excel.Application") の基本的な使い方


CSVをExcelファイルに変換するスクリプト (JScript/WSHで,カンマ区切りCSVファイルから.xlsを生成)
http://language-and-engineering.hatenablog.jp/entry/20090718/p1

  • WScript.Argumentsの使い方


Excelのマクロを外部に吸い出して保存するバッチ (WSH/JScriptで,VBAのソースコードだけを抜き取る)
http://language-and-engineering.hatenablog.jp/entry/20100524/p1

  • VBのFor EachをJScriptのEnumeratorで代用するサンプル


右隣のセルを参照する
http://moug.net/tech/exvba/0050161.html

  • .Offset(0, 1)


[VB-TIPS] EXCELでシート名を文字列として扱う制限について
http://d.hatena.ne.jp/chaichanPaPa/20...

  • シート名に中括弧やスペースが入っている場合,シート名をシングルクォートすれば,ハイパーリンクで参照可能


how to set center alignment for a cell using VBScript
http://stackoverflow.com/questions/14...

  • HorizontalAlignment = xlCenter

補足:なぜ,VBAマクロとして,処理をブック内に埋め込まなかったのか?

VBAマクロとして,処理をブック内に埋め込むこともできる。

しかし,それはしない。


なぜかというと,Office 2007以降では,

マクロを含んでいるか・いないかでファイルの形式が異なり,

非常にうるさいからだ。

(拡張子を.xlsxから.xlsmに変更する必要が生じてしまう)


マクロを含まないブックは,できればそのままの状態にしておいてあげたい。

内部にはマクロを埋め込まずに,外部のスクリプトが処理する方式にしておけば,

ファイルの変更がなくて楽だし,セキュリティ的にも安心。