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


投稿日:2018年4月27日
  • 0
  • 7



テキスト形式の住所録を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行目visibleTrueにしています。
visibleEXCELウィンドウの表示、非表示をコントロールするものです。
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の作業を自動化する方法を解説します。



現在のページを共有する



現在のページに関連する記事

UWSCでEXCELの作業を自動化する方法 日々の単純作業を自動化できるUWSCの使い方(入門・書式編)
UWSCでEXCELの作業を自動化する方法 UWSCでPhotoshopの作業を自動化する方法
UWSCでEXCELの作業を自動化する方法 Firefox機能拡張のコンテキストメニューについて
UWSCでEXCELの作業を自動化する方法 ページ編集の手間を劇的に減らす、Custom Field Templateでカスタムフィールドを作る方法
UWSCでEXCELの作業を自動化する方法 tsharkのインストールとフィルタ・自動停止オプションの使い方まとめ
UWSCでEXCELの作業を自動化する方法 Norikraの使い方をサンプルのクエリとイベントを交えて解説
UWSCでEXCELの作業を自動化する方法 iptablesの設定ファイルをシェルスクリプトを利用して動的に作成

おすすめの記事

誤って削除したGoogle Chromeのブックマークを復元する方法

誤って削除したGoogle Chromeのブックマークを復元する方法

fluentdとNorikraでDoS攻撃を遮断し、メールで通知する方法

fluentdとNorikraでDoS攻撃を遮断し、メールで通知する方法

本気で作りたい人向け、WordPressプラグインの作成方法

本気で作りたい人向け、WordPressプラグインの作成方法

Apacheのmod_rewriteモジュールの使い方を徹底的に解説

Apacheのmod_rewriteモジュールの使い方を徹底的に解説

iptablesで設定したパケットフィルタリングが正しく動作しているかテスト

iptablesで設定したパケットフィルタリングが正しく動作してい…

SNSのカウントをキャッシュするWordPressのプラグインを作成しました

SNSのカウントをキャッシュするWordPressのプラグインを作成し…

Apacheのmod_deflateでコンテンツを圧縮してサイトを高速化する方法

Apacheのmod_deflateでコンテンツを圧縮してサイトを高速化する…

公式ドキュメントよりも詳しいTinyMCEの使い方(基本編)

公式ドキュメントよりも詳しいTinyMCEの使い方(基本編)


コメントを残す

コメントは認証制のため、すぐには反映されません。

プログラミングに関する質問は「日本語でプログラミングの悩みを解決するQ&Aサイト sukegra」をご利用ください。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください