スポンサーリンク

Excelシート内で,URLを含むセルを,一括してハイパーリンクに変換するバッチ

あちこちのセルにURLが散りばめられているExcelシート上で,

URLを含むセルを,全部,ハイパーリンクに変換したい。


その作業を自動化するためのバッチ。


下記のバッチファイルに対して,Excelファイルをドラッグ・ドロップする。

すると,Excelファイル内の最初のシート上で,「URLを含むセル」に,

ハイパーリンク(HYPERLINK関数によるリンク)が自動的に設定される。


ExcelシートのURLを一括してハイパーリンク化.bat

@if(0)==(0) ECHO OFF
cscript.exe //nologo //E:JScript "%~f0" %*
rem cscript.exe //nologo //E:JScript "%~f0" %* > %0\..\log.txt
echo 終了しました。
@pause
GOTO :EOF
@end


/*

Excelでは,1セル内に埋め込む事のできるHYPERLINKは1つだけという点に注意。
セル自体がリンクになる。
複数のリンクがある場合はセルを分割すること。

*/

// ソースコードの参考:
// http://language-and-engineering.hatenablog.jp/entry/20140112/GenerateAccessRankingTableHTMLFromHatenaCounter


function log(s){ WScript.Echo(s); }



// ---- 設定項目


// 範囲設定
var max_y = 100;
var max_x = 30;

// URLを表す正規表現
var regObjURL = /(http(s)?:\/\/[a-zA-Z0-9\/%\-=&\?\._\+#]+)/gi;




// ---- 引数取得


// 引数があるか
if( WScript.Arguments.length == 0 )
{
	log("同一フォルダ上のExcelファイルをドロップしてください。");
	WScript.Quit();
}

// ファイルパスを構築
var filename = WScript.Arguments.Unnamed(0);
var filepath = filename;
var fso = WScript.CreateObject("Scripting.FileSystemObject");
var filedir  = fso.GetParentFolderName( filepath );

// ファイルが存在するか
if( ! fso.FileExists( filepath ) )
{
	log( filepath + " は無効なファイルパスです。");
	log("同一フォルダ上のExcelファイルをドロップしてください。");
	WScript.Quit();
}
else
{
	log( filepath + " は有効なファイルです。");
}



// ---- Excel起動


var excel = null;
try
{
	// Excel(Kingsoft Spreadsheet)を立ち上げる
	excel = WScript.CreateObject("ET.Application");
}
catch(e)
{
	// Excel(MS Office)を立ち上げる
	excel = WScript.CreateObject("Excel.Application");
}
excel.Visible = true;


// 対象ブックを開く
excel.Workbooks.Open( filepath );
var book = excel.Workbooks( excel.Workbooks.Count );

// 最初のシートを処理の対象とする
var target_sheet = book.Worksheets(1);


// ---- 全セル内のURL文字列をリンクに変換


// セル内にURLを含んでいるか判定する関数
function includesURL( cell )
{
	var s = cell.Value;
	
	// 中身がない場合
	if( ! s )
	{
		return false;
	}
	
	// 中身の一部がURLにマッチする場合
	if( (s + "").match( regObjURL ) )
	{
		return true;
	}
	
	return false;
}


// セル内のURLをハイパーリンクに変換する関数
function modifyCellValue( cell )
{
	var str_orig = cell.Value + ""; // 単純な代入だけだと参照になってしまうので""を付与
	
	// リンクのラベル文字列を生成
	var link_label = str_orig
		.replace( new RegExp('"', "g"), '"""' )
		.replace( /\n/g, "\"&char(10)&\"" )
	;
	
	
	// リンクのラベル内からURLを削除して置換
	link_label = link_label
		.replace( regObjURL, '開く' )
	;
	
	
	// URLを抽出
	str_orig.match( regObjURL );
	var str_url = RegExp.$1;
		//var str_url = str_orig
		//	.replace( regObjURL, "$1" )
		//;
		// http://language-and-engineering.hatenablog.jp/entry/20080927/1222508705
	
	// セルの値を生成
	// ※1セルにつき1リンクである事に注意。
	var str_cell = "=HYPERLINK(\"" +
		str_url +
		"\",\"" +
		link_label +
		"\")" 
		// diary:この末尾に間違えて"\""を付与したら
		// 「実行時エラー:セミコロンがありません」になってはまった
	;
		// http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/hyper.htm
	
	log( "URLは:" + str_url );
	log( "ラベルは:" + link_label );
	log( "セルに書き込む数式は:" + str_cell );
	
	//cell.Formula = "";
	cell.Formula = str_cell ;
}



// 行方向のスキャン
for(var y = 1; y <= max_y; y ++)
{
	// 列方向のスキャン
	for(var x = 1; x <= max_x; x ++)
	{
		var cell = target_sheet.Cells( y, x );
		
		// 加工すべきセルか
		if( includesURL( cell ) )
		{
			log( "[" + y + "," + x + "] URLを含むセルです。処理を開始" )
			modifyCellValue( cell );
		}
		else
		{
			log( "[" + y + "," + x + "] URLなし。スキップします。" )
		}
	}
}
log("シート内の全セルの検査と加工が完了");


// ---- 終了


// ブックを保存
//excel.DisplayAlerts = false;
//book.SaveAs( filepath );


// Excelを閉じて終了
//excel.Quit();
//excel = null;

log("全処理が終了");


ソースコードを読むための参考情報:

ExcelのHYPERLINK関数は,セル内で1つだけ記述可能。複数個は無理
http://computer-technology.hateblo.jp/entry/20140115/p2


正規表現による文字列置換 String.replace(); - JavaScript スタイルシートサンプル集
http://javascript123.seesaa.net/artic...


JavaScriptの動かないコード (中級編) 正規表現で同じ文字の連続を検出したい - 置換前パターン中での後方参照
http://language-and-engineering.hatenablog.jp/entry/20080927/1222508705


エクセル「シート保護」をすると、「ハイパーリンク」が動きません | オフィス系ソフトのQ&A【OKWave】
http://okwave.jp/qa/q3426114.html

関連する記事:

Excelブックの「シート目次」を,自動的に作成するバッチ (WSH/JScriptで,各シートへのリンク付きの目次を自動生成)
http://language-and-engineering.hatenablog.jp/entry/20110921/p1


はてなダイアリーに執筆した記事一覧を,表形式に整理するブックマークレット (アーカイブページを,Excelに貼り付けやすく整形加工)
http://language-and-engineering.hatenablog.jp/entry/20140102/p1


Excelシートを読み取って,INSERT文SQLを生成するバッチ (Kingsoft SpreadsheetをWSH/JScriptで自動操作)
http://language-and-engineering.hatenablog.jp/entry/20121218/p1