プログラミング

【VBAで文字列を一括置換】Excelのセルとオートシェイプ図形

  • このエントリーをはてなブックマークに追加
  • LINEで送る

あなたは日々のお仕事で

『Excelの図形の文字をチョット置換したいだけ』

なんだけど…

『置換する数が膨大でウンザリ!』

といった経験はないでしょうか?

こんな時は、プログラムで一気に置換してササッと仕事を終わりたいですよね!

ササッと終わる手段として、職場でインストールされてるExcelのVBAが使えます。

『VBAでExcelの文章や図形テキストを一括置換する方法は?』

このような疑問に答えます。


SEおっさん
SEおっさん
SE歴20年のオッサンが詳しく解説するぞい!

※保有資格:OracleMasterGold/JavaWebコンポーネントディベロッパ/応用情報技術者/簿記2級

VBAでExcel・オートシェイプ図形のテキスト文字列を一括置換

Excelに書かれた文字は、大きく分けて2パターンあります。

  1. セル内に書かれた文字
  2. オートシェイプに書かれた文字
    (オートシェイプ=図形)

文字を置換する2つのパターンです。

2つのパターン
  1. セルの文字を置換

    RangeオブジェクトのReplaceメソッド

  2. オートシェイプの文字を置換
    • Shape オブジェクトのTextFrame2プロパティのTextRangeプロパティのTextプロパティ
    • Replace関数

この2パターンの解決策を組み合わせて、Excelのセルや図形テキストの文字を一括置換します。

※Replace関数とReplaceメソッドは、名前は似ていますが全く別物です。

SEおっさん
SEおっさん
メソッドとは…
オブジェクトが持つ関数のコトじゃ
メソッドはオブジェクトを介して
呼び出されるということだね!
たろちゃん
たろちゃん
SEおっさん
SEおっさん
そうじゃ!
関数はオブジェクトを介さず、直接呼び出すぞい

また、

Excelファイル操作するメソッドも併せて使用します。

こちらもテキストファイル操作のステートメントとは全く別物です。

Excel専用のファイル操作命令があるってコトだね
たろちゃん
たろちゃん

Excelのオートシェイプ図形のテキスト文字列を置換

Excelに入力する文字はセルだけではありません。

オートシェイプの図形でも文字を頻繁に入力します。

図形の文字はExcelの機能で一括置換できません。
(セルの文字置換は、1ファイルだけならExcelの機能で置換可能)

図形の文字を置換したいけど、置換箇所が大量の場合は『超メンドクサイ』ですよね。

図形の文字を一括置換するには、VBAを使用すれば解決できます。

VBAの一括置換は『プログラム設計書で図形のテキスト文字をチョット置換したいだけど、置換対象が大量にある』というシーンでも有効です。

VBAでExcelの図形(オートシェイプ)のテキスト文字列を置換する流れです。

  1. 図形テキストより文字を取得
  2. 取得した文字列をReplace関数で置換
  3. 置換した文字列を図形テキストに設定

まず、図形テキスト文字の置換を行うためには、図形テキスト文字を取得する必要があります。

SEおっさん
SEおっさん
順番に説明するぞい♪

図形テキストより文字を取得する

VBAではExcelの図形(オートシェイプ)は、Shape オブジェクトです。

図形テキストより文字を取得するには…

Shape オブジェクトのTextFrame2プロパティのTextRangeプロパティのTextプロパティを使用します。

わっ、長っ
呪文みたい…
たろちゃん
たろちゃん
SEおっさん
SEおっさん
1つ1つは簡単じゃよ
いまから詳しく説明するぞい

まず、Shapeオブジェクトから説明します。

Shape オブジェクト

Shape オブジェクトは、図形などの描画オブジェクトを表します。
(描画オブジェクト…オートシェイプ/フリーフォーム/OLE オブジェクト、図など)

Shape オブジェクトは、 Shapes コレクションのメンバーです。

Shapes コレクションには、ブックのすべての図形が含まれます。

つまり…

Excelブックにある図形の数だけ、Shapes コレクションにShapeオブジェクトが格納されている

ということです。

難しいな…
たろちゃん
たろちゃん
SEおっさん
SEおっさん
大丈夫じゃ!
コレクションは後で詳しく説明するぞい

また、

ShapeオブジェクトのTextframe2プロパティでTextframe2オブジェクトを取得できます。

オブジェクトの中にオブジェクトを持てる
ってコトだね!
たろちゃん
たろちゃん
SEおっさん
SEおっさん
そうじゃ!
オブジェクトのプロパティやメソッドで
オブジェクトを取得できるから階層化される訳じゃ

TextFrame2 オブジェクト

TextFrame2 オブジェクトは、Shape/ShapeRange/chartformat オブジェクトのレイアウト枠を表します。

このオブジェクトには、レイアウト枠の配置およびアンカーを制御するプロパティやメソッドだけでなく、レイアウト枠のテキストが含まれています。

TextFrame2 オブジェクトのHasTextプロパティは、指定したテキストフレームにテキストがあるかどうか(ある:True/なし:False)を返します。

矢印の図形はテキストを設定できません。

矢印のShapeオブジェクトからTextプロパティを参照しようとすると、エラーとなります。

エラーを回避するためには、図形(シェイプ)がテキスト設定可能か判断する必要があるので、HasTextプロパティで確認する訳ですね。

TextFrame2オブジェクトのTextRangeプロパティでTextRange2オブジェクトを取得できます。

SEおっさん
SEおっさん
TextRangeプロパティでTextRange2オブジェクトを取得じゃ
ここだけは、ややこしいのぅ…

TextRange2オブジェクト

TextRange2オブジェクトのTextプロパティを使って図形テキスト文字の取得・設定を行います。

たろちゃん
たろちゃん
確かに1つずつ見れば解る!
落ち着いてプログラミングだね♪

オートシェイプ図形のテキスト文字列をReplace関数で置換

Shapeオブジェクトより取得した文字列を、Replace関数で置換します。

Replace関数の基本形

置換後の文字列 = Replace(置換前の文字列, 検索文字列, 置換文字列)

詳細は下記を参照ください。

【VBAで文字列を一括置換】複数のテキストファイルを1回で変更
【VBAで文字列を一括置換】複数のテキストファイルを1回で変更
あなたは日々のお仕事で 『テキストファイルの中身をチョット置換したいだけ』 なんだけど… 『置換するファイル数が膨大でウンザリ!』 といった経験はないでしょうか.....

置換した文字列をExcelのオートシェイプ図形のテキストに設定

置換した文字列をExcelのオートシェイプ図形のテキストに設定します。

図形テキストに文字列を設定するには…

Shape オブジェクトのTextFrame2プロパティのTextRangeプロパティのTextプロパティを使用します。

オートシェイプのテキスト文字列の取得と同じだね!
たろちゃん
たろちゃん

次は、Excelのセルに入力された文字列を置換するReplaceメソッドについて説明いたします。



スポンサーリンク

Excelのセル文字をReplaceメソッドで置換

Excelのセルに記載された文字を置換するには、RangeオブジェクトのReplaceメソッドを使用します。

Replaceメソッドの基本形です。

Replaceメソッドの基本形

Range.Replace(検索文字列, 置換文字列,一致モード)

ReplaceメソッドはRangeオブジェクトの持ち物ってことだね
たろちゃん
たろちゃん

Replaceメソッドの引数です。

任意引数は引数3(LookAt)と引数5(MatchCase)が重要です。
引数4と引数6~8は、ほとんど使用しません。

引数

【必須】

  • 引数1:What
    Excelで検索する文字列
  • 引数2:Replacement
    置き換える文字列

【任意:省略可】

  • 引数3:LookAt
    一致モード
    xlPart(部分一致)/xlWhole(完全一致)
  • 引数4:SearchOrder
    検索方向
    xlByRows(横)/xlByColumns(縦)
  • 引数5:MatchCase
    大文字と小文字の区別
    True(大文字と小文字を区別する)
    False(大文字と小文字を区別しない)
  • 引数6:MatchByte
    Microsoft Excel で2バイトの言語サポートを選択またはインストールした場合にのみ使用
    True( 2 バイト文字が 2 バイト文字とだけ一致)
    False(2 バイト文字が同等の 1 バイト文字とも一致)
  • 引数7:SearchFormat
    検索書式
  • 引数8:ReplaceFormat
    置換書式

VBAのReplace関数とExcelのReplaceメソッドを使用して、Excel内の文字列と図形テキストを置換して出力する方法をご紹介します。

Replaceメソッドの使用例

Replaceメソッドの使用例です。

Worksheets("Sheet1").UsedRange.Replace What:="おはよう", Replacement:="こんにちは", LookAt:=xlPart

Excetシート「Sheet1」に記載された全ての”おはよう”を”こんにちは”に置換します。

UsedRangeプロパティは、シート内のセルに入力された全データのセル範囲を取得します。

つまり、Excetシート「Sheet1」にある全セルのデータが対象になる訳です。

SEおっさん
SEおっさん
ここまでが、
セル文字と図形文字を置換する方法じゃ
わかったかのぅ

※VBAでExcelファイルの操作ができる方
ここまでの情報を組み合わせて一括置換することが出来ると思います。
次の章は飛ばして一括置換するサンプルコードへ

※VBAでExcelファイルの操作を知らない方
いきなり一括置換するサンプルコードを見ても
『わけわからん!』
かもなので、これからご説明いたします。

VBAでExcelファイルを操作するメソッド

一括置換に必要なExcelファイルを操作するメソッドを説明します。

Openメソッド(Excelファイルを開く)

WorkboksコレクションのOpenメソッドを使用して、Excelブックを開きます。

コレクションとは

コレクションとは、種類が特定されたオブジェクトの集まりです。

Workbooksコレクションというのは、Workbookオブジェクトの集まりということになります。

WorkbookオブジェクトとはExcelブックのことです。

WindowsではExcelを何個も起動することができますよね。

例えば、Excelを3個起動していたら…

Workbookコレクション内のWorkbookオブジェクトは3つ存在している

ということになります。

コレクションは重要な概念なので、覚えておいて損はありません。

つまり、Excelファイルを開くということは…
Workbooksコレクションに、開いたExcelをWorkbookオブジェクトとして追加することです。

ExcelはWorkbooksコレクションで
管理されてるんだね!
たろちゃん
たろちゃん

WorkbooksコレクションのOpenメソッドの基本形です。

Openメソッドの基本形

Workbook = Workbooks.Open 開くブックのファイル名

Openメソッドの引数です。

引数【任意:省略可能】
  • 引数1:FileName
    開くExcelのファイル名です。
    フルパスで指定します。

Openメソッドの使用例

以下のOpenメソッドでCドライブに存在するSample_BookというExcelファイルを開きます。

Workbooks.Open "C:Sample_Book.xlsx"

SaveAsメソッド(名前を付けて保存する)

WorkbookオブジェクトのSaveAsメソッドで、Excelファイルを保存します。

SaveAsメソッドの基本形です。

SaveAsメソッドの基本形

Wookbookオブジェクト.SaveAs ファイル名

SaveAsメソッドの引数です。

引数
  • 引数1:Filename
    保存するExcelのファイル名です。
    フルパスで指定します。

Closeメソッド(Excelファイルを閉じる)

WorkbookオブジェクトのCloseメソッドで、開いているExcelファイルを閉じます。

Closeメソッドの基本形です。

SaveAsメソッドの基本形

Wookbookオブジェクト.Close

Excelファイル操作の使用例も欲しいナ…
たろちゃん
たろちゃん
SEおっさん
SEおっさん
使用例を紹介するぞい

VBAでExcelファイルを操作するメソッドの使用例

VBAでExcelファイルを操作するメソッドの使用例です。

Dim wb As Workbook

'Excelブックを開く
Workbooks.Open Filename:="C:Sample_Book1.xlsx"
Set wb = ActiveWorkbook

'出力先へExcelブックを保存
wb.SaveAs Filename:="C:Sample_Book2.xlsx"

'Excelブックを閉じる
wb.Close

VBAでExcelのセルと図形文字を一括置換!サンプルコードとフローチャート

VBAでExcel文章や図形テキストを一括置換して保存する関数(サンプルソース)とフローチャートです。

ソースコードを見ても理解できない時、フローチャートが理解を助けてくれます。

プログラミング初心者にフローチャートは必要【実証済のメリット5つ】

【記号は5つ】フローチャートの書き方【初心者向け簡単マニュアル】

VBAでExcel文章や図形テキストを一括置換して保存するフローチャート

VBAでExcel文章や図形テキストを一括置換して保存するフローチャートです。

SEおっさん
SEおっさん
フローチャートで流れは掴めたかのぅ

VBAでExcel文章や図形テキストを一括置換して保存する関数

VBAでExcel文章や図形テキストを一括置換して保存するサンプルコードです。

関数名は(XLS_IN_OUT)としています。

#サンプルソースはGithubにあります。

'入力ブックから出力ブックへ文字列を置換して出力
'引数1:入力Excelブックパス
'引数2:出力Excelブックパス
'引数3:置換前文字列
'引数4:置換後文字列
Private Sub XLS_IN_OUT(ByVal path_i As String, ByVal path_o As String, search_str As String, replace_str As String)

	Dim wb As Workbook
	Dim ws_io As Worksheet
	Dim spShape As Shape

	'Excelブックを開く
	Workbooks.Open Filename:=path_i
	Set wb = ActiveWorkbook

	'開いたブック内の全シート分ループ
	For Each ws_io In wb.Worksheets
		'ワークシートの文字置換(xlPart:セルの部分一致)
		ws_io.UsedRange.Replace What:=search_str, Replacement:=replace_str, LookAt:=xlPart

		'図形(シェイプ)の文字置換
		For Each spShape In ws_io.Shapes
			'テキストを持つ図形か判断
			If spShape.TextFrame2.HasText Then
			    spShape.TextFrame2.TextRange2.Text = Replace(spShape.TextFrame.TextRange2.Text, search_str, replace_str)
			end if
		Next spShape
	Next ws_io

	'出力先へExcelブックを保存
	wb.SaveAs Filename:=path_o

	'Excelブックを閉じる
	wb.Close

End Sub

図形(シェイプ)の文字置換の前に、TextFrame2.HasTextプロパティでテキストを持つ図形か確認してから、テキスト置換を行っています。
⇒確認しなければ、矢印などのテキストを持たない図形でエラーが発生するため

上記の自作関数(XLS_IN_OUT)の呼び出し例です。

call XLS_IN_OUT("D:sample_input.xlsx", "D:sample_output.xlsx", "おはよう", "こんにちは")

sample_input.xlsxに記載された全ての”おはよう”を”こんにちは”に置換してsample_output.xlsxに出力します。

SEおっさん
SEおっさん
ここまでが、VBAでExcel内の文章や図形を一括置換して保存する方法じゃよ!
わかったかのぅ

VBAでExcelのセルと図形文字を一括置換!まとめ

いかがでしたでしょうか?

VBAを利用したExcelのセルと図形文字を一括置換する方法をご紹介しました。

ポイントを少し振り返ってみましょう。

ポイントまとめ!
  • Excelのセルの文字列を一括置換
    Excelのファイル操作メソッドとReplaceメソッドを使用する
  • Excelの図形テキストを一括置換
    Excelのファイル操作メソッドとReplace関数を使用する
    図形テキストの置換の前に、TextFrame2.HasTextプロパティでテキストを持つ図形か確認する

VBAの置換で、速く仕事が終われば良いですね!

#サンプルソースはGithubにあります。

SEおっさん
SEおっさん
ここまで読んでくれてありがとう!




スポンサーリンク
  • このエントリーをはてなブックマークに追加
  • LINEで送る

コメントを残す

*