UWSCでEXCELの作業を自動化する方法

oxy

6年 ago

テキスト形式の住所録をEXCELにコピーペーストする作業を自動化

前回のページでUWSCの基礎について解説しました。

このページでは実際の業務をUWSCで自動化する流れを解説します。
よくあるパターンとして、テキストファイルとEXCELを行き来してひたすらコピーペーストするという単純作業を自動化します。

UWSCで作業を自動化するための準備

今回は自動化のサンプルとして、テキスト形式の住所録をEXCELへコピーペーストします。

具体的には以下のような「テキスト形式の住所録が100件ある」という想定です。（ダミーデータ）
このデータをコピペして100行ほどに増やし「住所録.txt」という名前をつけてデスクトップに保存してください。

原宿 太郎（〒150-0001 東京都渋谷区神宮前 3-12-8、00-1234-5678）
佐藤 菜穂子（〒150-0042 東京都渋谷区宇田川町 1-xx-x、00-0001-1234）
田中 浩一郎（〒131-0031 東京都墨田区墨田 3--yy-yyy、00-2345-6789）
太田 勉（〒253-0013 神奈川県茅ヶ崎市赤松町 4-aa-aaa、00-3456-7890）
原田 良子（〒421-0216 静岡県焼津市相川 5-bb-bbb、00-4567-8901）
山田 太郎（〒616-8171 京都府京都市右京区太秦青木ヶ原町 3-dd、00-5678-9012）
山内 雅夫（〒206-0035 東京都多摩市唐木田 2-e-eee、00-6789-0123）
安藤 秀雄（〒103-0014 東京都中央区日本橋蛎殻町 4-f-ff、00-7890-1234）
竹内 貞夫（〒065-0010 北海道札幌市東区北十条東 5-g、00-8901-2345）
野口 さやか（〒150-0045 東京都渋谷区神泉町 3-i-ii、00-2345-6789）

このデータを元にEXCELへ「氏名、郵便番号、住所」という順番で貼り付ける、という作業を自動化します。

UWSCでテキスト形式の住所録をEXCELにコピーペーストするサンプルスクリプト

では早速UWSCのスクリプトを紹介します。

EXCEL = CREATEOLEOBJ("Excel.Application") // COMオブジェクトの作成
EXCEL.visible = True // ウィンドウをアクティブに
EXCEL.Workbooks.add() // 新しいワークシートを開く

FILE_ID = FOPEN("C:\Users\ユーザー名\Desktop\住所録.txt", F_READ) // 読み込みモード
CNT = FGET( FILE_ID, F_LINECOUNT ) // 行数を取得

FOR i = 1 TO CNT
	LINE = FGET( FILE_ID, i )

	Re = CreateOleObj("VBScript.RegExp")
	Re.Global = True //文字列全体を検索。trueでする(規定)、falseでしない(マッチする文字列が見つかったらそこで終了)。
	Re.IgnoreCase = True //大文字・小文字を区別。trueでしない(規定)、falseでする。

	Re.pattern = ".*（" // 名前, カッコより前の部分
	NAME = Re.Execute( LINE )
	NAME = NAME.Item(0).Value
	NAME = COPY( NAME, 0, LENGTH(NAME)-1 )

	Re.pattern = "[〒^](.*?)\s"// 郵便番号, 〒からスペースまで
	POST = Re.Execute( LINE )
	POST = POST.Item(0).Value
	POST = COPY( POST, 2, LENGTH(POST)-2 )

	Re.pattern = "\d\s.*、"// 住所, スペースから、まで
	ADDRESS = Re.Execute( LINE )
	ADDRESS = ADDRESS.Item(0).Value
	ADDRESS = COPY( ADDRESS, 3, LENGTH(ADDRESS)-3 )

	EXCEL.ActiveCell.value = NAME // セルに値をセット
	EXCEL.Selection.Offset(0, 1).Select // 右へ移動
	EXCEL.ActiveCell.value = POST
	EXCEL.Selection.Offset(0, 1).Select // 右へ移動
	EXCEL.ActiveCell.value = ADDRESS
	EXCEL.Selection.Offset(1, -2).Select // 次の行の一番目へ移動

NEXT

FCLOSE(FILE_ID) // ファイルを閉じる

5行目の「C:\Users\ユーザー名\Desktop\住所録.txt」の部分は自分の環境に合わせて適宜変更してください。
このスクリプトを保存して実行すると以下のようにEXCELが起動して、住所録が自動で作成されます。

スクリプトを実行してから貼り付けが完了するまで、わずか2秒です。
UWSCを利用した自動化がいかに強力であるか、理解していただけると思います。

スクリプトの解説

では、スクリプトの内容を上から順番に見ていきます。

1行目、CREATEOLEOBJ(“Excel.Application”)とあります。
これでCOMオブジェクトを作成しています。
COMオブジェクトとは「Component Object Model」の頭文字を取ったものです。噛み砕いて言えばMicrosoft Office製品と特定のアプリケーション間でデータをやり取りしたり、操作したりするための仕組みです。

COMの詳細について解説すると何ページあっても足りないので、今回は必要な記述についてのみ解説します。
一般の業務で必要になるような操作方法は検索すればたいてい見つかります。根気よく調べてみてください。

2行目、visibleをTrueにしています。
visibleはEXCELウィンドウの表示、非表示をコントロールするものです。
TrueにすることでEXCELのウィンドウをアクティブにしています。

3行目、Workbooks.add()は読んで時のごとく、新しいワークシートを作成しています。

5～6行目、FOPENでファイルを読み込み、FGETでテキストファイルの行数を取得します。39行目でFCLOSE(FILE_ID)として閉じています。

8～37行目、FOR-NEXTで行数の数だけループ処理をしています。

11～13行目、CreateOleObj(“VBScript.RegExp”)とありますが、これはVBScriptの正規表現を利用するための記述です。
Global = Trueで文字列全体を検索、IgnoreCase = Trueで大文字小文字を区別しない、という指定です。

VBScriptオブジェクトのプロパティ等を詳しく知りたい方は公式ページ（Regular Expression オブジェクトのプロパティとメソッド）で確認してください。

15～18行目、patternで名前の部分を切り取るための正規表現のパターンを指定。
Executeメソッドで検索、Item(0).Valueで一致した項目（名前部分）をNAMEに渡しています。

このままだとカッコの部分まで代入されてしまいます。1行目で言えば「原宿太郎（」となります。そこで必要のない文字を切り取りたいのですが、UWSCには文字列を切り取る専用の関数はありません。そこでCOPY関数を利用して、第3引数のコピー文字数に「文字列の数-1」を指定して切り取っています。

20～28行目、郵便番号、住所についても同じように処理。

30～35行目、再びCOMオブジェクトでEXCELを操作します。
ActiveCell.valueでアクティブなセルに値を入力。
Selection.Offset(0, 1).Selectで右のセルへ移動。Selection.Offset()の第1引数は縦軸、第2引数は横軸の移動です。

以上で、EXCELの作業を自動化できました。

今回は100行でしたが、これが1,000行、10,000行となれば、数日がかりの作業になります。なにより、手動でのコピーペース作業は必ずミスが起こります。それがものの数分で自動処理できるとなれば導入しない手はありません。

初めはUWSCやCOMオブジェクトの扱いに戸惑うかもしれませんが、慣れれば大抵のスクリプトは数分で書けます。
日々のルーチンワークにすばやく正確に処理したいという方は、ぜひ挑戦してみてください。

では、次のページでPhotoshopの作業を自動化する方法を解説します。