閉じたままのブックからデータを転記するには?(Tips-12)
「VBAで、閉じたままのブックからデータを転記する方法は無いかにゃー」
と思っている人のために、今回は ExecuteExcel4Macro メソッドを紹介します。
ExecuteExcel4Macro?
それは何かの最先端技術ですか?
いえ、違います。
新しいバージョンの Excel で、 Excel 4.0 時代のマクロ関数を使うことができるメソッドなのです。
PlayStation3 で PlayStation1 のゲームが遊べるようなものです。(←ぇ)
構文は次のとおりです。
引数は Excel 4.0 のマクロ関数を等号 (=) なしで指定します。
ゆえに、Excel 4.0 マクロ関数を知らないと使いこなすことができません。
今更、過去のものを覚えてもしょーがないですが、閉じたままのブックからデータを転記できる機能だけでも覚えておいて損はないと思います。
引数は次のとおりです。
- ExecuteExcel4Macro("'パス[ファイル名]シート名'!R1C1")
データを取得するセルはR1C1形式で書く必要があります。
んで、指定できるのは1セルのみです。複数範囲からデータを取得するにはループを回すなどの工夫が必要です。
実際にやってみましょう。
まず、1セルのみから。
1データ×500ブック分を転記してみる
下のようにA1セルにデータが入ったものを500ブック用意しました。(ファイル名は 1.xlsx ~ 500.xlsx)
ExecuteExcel4Macroメソッド を使って、これ(×500ブック)を転記先ブックの A1~A500 に転記させてみましょう。(処理時間の計測もしておきます)
Sub test1()
StartTime = Timer
For i = 1 To 500
Cells(i, 1) = ExecuteExcel4Macro("'" & ThisWorkbook.Path & "\[" & i & ".xlsx]Sheet1'!R1C1")
Next i
Debug.Print Timer - StartTime; "秒"
End Sub
A500までずらーっと転記されました。
かかった時間は
1.82秒でした。
同じことを、ExecuteExcel4Macro メソッドを使わずにやってみましょう。(1ブックずつ開いて転記する)
Sub test2()
Application.ScreenUpdating = False
StartTime = Timer
For i = 1 To 500
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & i & ".xlsx"
Cells(i, 1) = Workbooks(i & ".xlsx").Worksheets(1).Cells(1, 1)
Workbooks(i & ".xlsx").Close savechanges:=False
DoEvents
Next i
Debug.Print Timer - StartTime; "秒"
End Sub
かかった時間は
174.55秒でした。
さっきの100倍近くかかってしまいました。主にファイルを開いて閉じるのにかかった時間だと推測されます。
5データ×500ブック分を転記してみる
次に、下のように A1~E1 にデータが入ったものを500ブック用意しました。(データがさっきの5倍)
ExecuteExcel4Macroメソッドを使って、これ(×500ブック)を転記先ブックの A1~E500 に転記させてみましょう。
Sub test3() StartTime = Timer For i = 1 To 500 For
j = 1 To 5Cells(i, j) = ExecuteExcel4Macro("'" & ThisWorkbook.Path & "\[" & i & ".xlsx]Sheet1'!R1C" & j) Next j Next i Debug.Print Timer - StartTime; "秒" End Sub
E500までずらーっと転記されました。
かかった時間は
9.59秒でした。
単純に test1 の5倍くらいかかった計算になります。
同じことを、ExecuteExcel4Macro メソッドを使わずにやってみましょう。(1ブックずつ開いて転記する)
Sub test4()
Application.ScreenUpdating = False
StartTime = Timer
For i = 1 To 500
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & i & ".xlsx"
配列 = Workbooks(i & ".xlsx").Worksheets(1).Range("A1:E1")
Range(Cells(i, 1), Cells(i, 5)) = 配列
Workbooks(i & ".xlsx").Close savechanges:=False
DoEvents
Next i
Debug.Print Timer - StartTime; "秒"
End Sub
かかった時間は
168.21秒でした。
test2 とほぼ変わらずです。
ブックを開いた後は、新しいVBA マクロのほうが配列などで効率よく転記できるからですね。(1~5データ程度では違いが出ないことから、いかにファイルのオープン・クローズ処理のみに時間がかかっているか分かります)
まとめ
データ100個も追加計測し、処理方法・データ数・時間(秒)をまとめました。
データ1個 | データ5個 | データ100個 | |
---|---|---|---|
ブックを閉じたまま | 1.82 | 9.59 | 179.79 |
ブックを開く | 174.55 | 168.21 | 160.18 |
(すべて500ブックで実施)
1ファイル内での転記対象のデータが多くなると、あるポイントでブックを閉じたままの方法とブックを開く方法とで時間が逆転してしまいます。
なので、転記対象のデータ数とブック数を見比べ、状況に応じて速いほうを選択するのが良いかなと思います。
Excel 4.0 マクロがいつまでサポートされるのか分かりませんけど。
語彙的意味に基づく文字列比較とは?(Tips-11)
VBAでは文字列データの比較方法を以下の Option Compare ステートメントで宣言することができます。
Option Compare Binary(又は省略)
- 各文字列の文字コード数値が比較されます。
Option Compare Text
- システムのロケールによって決定され、語彙的意味に基づいて比較されます。
Option Compare Database
- Access でのみ使用するので今回は割愛します。
ふむふむ。よくわからん。
もう少し具体的に書いてみよう。
「Binary」では文字コードの数値が同じか否かを比較します。"A" = "A"であれば、41 = 41 でありTrue。"A" = "a"であれば、41 = 61 であり False。といった具合に。
「Text」では文字コードに加えて語彙的意味での比較が行われます。つまり、文字コードが違っても類縁の文字は等しいとみなされるということです。(半角の「1」と全角の「1」、ひらがなの「あ」とカタカナの「ア」など)
じゃあ、「1」は「1」の他にも「一」「壱」「①」「Ⅰ」「ⅰ」などにもマッチするのでしょうか?
?(・_・;?
するような気もするし、しないような気もする・・・・・・。
何がマッチし何がマッチしないのか・・・・・・。ちょっと試してみましょう。
下の表に文字列のサンプルをいくつかピックアップしました。縦軸の文字と横軸の文字を VBA 内(Option Compare Text)で比較し、結果(True 又は False)を軸が交差する箇所に出力してあります。
サンプルが少ないため若干心もとないですが、比較のルールは大筋で下記のようになっていると思われます。
清音
- ひらがな・カタカナ・全角・半角を区別せずマッチする。濁音・半濁音・捨てかな(カナ)・漢字にはマッチしない。
濁音
- ひらがな・カタカナ・全角・半角を区別せずマッチする。清音・半濁音・捨てかな(カナ)・漢字にはマッチしない。
半濁音
- ひらがな・カタカナ・全角・半角を区別せずマッチする。清音・濁音・捨てかな(カナ)・漢字にはマッチしない。
捨てかな(カナ)
- 捨てかな・捨てカナ・全角・半角を区別せずマッチする。清音・濁音・半濁音・漢字にはマッチしない。
アルファベット
- 大文字・小文字・全角・半角を区別せずマッチする。機種依存文字の似た文字(分音記号付きなど)にはマッチしない。
アラビア数字
- 全角・半角・丸付き数字(環境依存文字)を区別せずマッチする。漢数字・ローマ数字にはマッチしない。
ローマ数字
- 大文字・小文字を区別せずマッチする。アラビア数字・漢数字・丸付き数字にはマッチしない。
全ての文字の比較を試してみたいところですが、組み合わせが膨大なので今日のところは勘弁してやろう(←ぇ)
ちなみに「語彙」とは語の集まりのことなので一文字を示す表現として用いることはできないのですが Microsoft Docs そのような表現が使われていたので使わせてもらいました。
Option Compare ステートメント | Microsoft Docs
Like演算子でのパターンマッチングについて(Tips-10)
VBA ではRegExpクラスを利用すれば正規表現を使用することができます。
が、そこまでしなくとも Like演算子を使って正規表現のようなパターンマッチングをすることもできます。
覚えることが少ないので初心者にはオススメですし、組み合わせ次第ではそれなりに複雑なパターンマッチングをすることができます。
- 文字列 Like 文字パターン
文字列が文字パターンと一致すれば True、一致しなければ False が返ります。
以下は文字パターンに使用する記号の一覧です。たったこれだけなので簡単ですね。
説明 | 例 | |
---|---|---|
* | 0個以上の文字にマッチ | あ* → あ あああ あいうえお などにマッチ |
? | 1文字にマッチ | あ? → あい あお あか など あ で始まる2文字にマッチ |
# | 1数字にマッチ | #個 → 1個 2個 など 数字 で始まり 個 で終わる2文字にマッチ |
[ ] | [ ]内の1文字にマッチ | あいう → あ い う の1文字にマッチ |
[!] | [ ]内の1文字以外にマッチ | !あいう → あ い う 以外の1文字にマッチ |
[-] | [ ]内の指定した範囲の1文字にマッチ | [1-9] → 1 2 3 4 5 6 7 8 9 の1文字にマッチ |
今回は、このうち [ ] [ ! ] [ - ] について少し補足しようと思います。
勘違いしがちですが、[ ]は2文字以上の単語を指定することはできません。あくまでも[ ]内に列挙された1文字にマッチします。
どういうことかというと、下のコードでは 東京 という単語にではなく、東と京のいずれか1文字から始まるパターンにマッチします。 なので、東京にもマッチするし京都にもマッチします
Sub test1()
Debug.Print "東京 "; "東京" Like "[東京]*"
Debug.Print "京都 "; "京都" Like "[東京]*"
End Sub
,(カンマ)で区切っても2文字以上の単語を指定することはできません。,(カンマ)も1文字として比較対象になるからです。
下のコードでは、東京と大阪という2つの単語にではなく、東 京 , 大 阪 のいずれか1文字から始まるパターンにマッチします。
Sub test2()
Debug.Print "東海 "; "東海" Like "[東京,大阪]*"
Debug.Print "京浜 "; "京浜" Like "[東京,大阪]*"
Debug.Print ", "; "," Like "[東京,大阪]*"
Debug.Print "大仏 "; "大仏" Like "[東京,大阪]*"
Debug.Print "阪神 "; "阪神" Like "[東京,大阪]*"
End Sub
文字と文字の間に -(ハイフン)を入れると文字の範囲内の1文字にマッチします。
例えば、[1-9]であれば、1~9 のいずれかにマッチし、[A-Z]であれば、A~Zのいずれかにマッチするということです。[9-1]のように降順だとエラーになります。
でも、文字の範囲って何なのさ?と、思うかもしれません。
確かに、分からないまま使用するのは気持ちの悪いものなので、少し具体的にいうと
文字コードの指定範囲(昇順)
ということになります。
下は文字コード表の一部ですが、[1-9]であれば文字コード 31 ~ 39 の範囲内、[A-Z]であれば文字コード 41 ~ 5A の範囲内の1文字と比較します。
ちょっと実験してみましょう。
[0-o]の範囲であれば、文字コード表の 30 ~ 6F の範囲が True その前後が False になるはずです。
Chr関数で文字コード 21 ~ 7E を [0-o] と比較して結果を見てみましょう。
Sub test3()
For i = &H21 To &H7E
Debug.Print Chr(i); " "; Chr(i) Like "[0-o]"
Next i
End Sub
ふむふむ。確かに、文字コードの昇順のようですね。
当然、小文字と大文字は文字コードが違うので区別されています。
これを [!0-o] とすれば、True と False が逆になる訳です。
今回、モジュール内の文字列比較方法を省略したので比較方法が Binary でしたが、 比較方法が Text の場合についても書いておこうと思います。その辺は次回以降に。
Word Rangeの引数指定について(Tips-9)
今回は Word の Range についてです。
名前が同じで用途も似ていますが、Excelの Range とはプログラム上まったくの別物です。
Range の引数はクイックヒントに表示されるように
Start:開始文字位置
End:終了文字位置
で指定します。
下のサンプルで実際にどのような範囲になるのか、Selectメソッドで確認してみましょう。
引数指定なしの場合
Range.Select
このように、引数を指定しなければ文書の全範囲が選択されます。言い換えれば、Range は全範囲のオブジェクトであるとも言えます。
引数(0, 0)の場合
Range(0, 0).Select
少し分かりづらいですが、文書の先頭(1文字目の左側)にカーソルが移動しました。ここが (0,0) です。
引数(0, 50)の場合
Range(0, 50).Select
文書の先頭から50文字目の右側までが選択されました。改行も1文字としてカウントするので注意が必要です。
引数(50, 110)の場合
Range(50, 110).Select
50文字目の右側から110文字目の右側までが選択されました。
引数が特定の段落のみの場合
仮に5段落目のみを選択したい場合はどうすればいいでしょうか?
5段落目の開始位置は 44 で終了位置は 55 なので以下の指定で選択できます。
Range(44, 55).Select
でも、わざわざ文字数を数えなきゃいけないの?と思いますよね。
確かにそのとおり。
この場合は下記のようにすれば、簡単に5段落目のみ選択できます。
Paragraphs(5).Range.Select
Paragraphs は段落のコレクションです。
Paragraphs(5) は文書の上から 5段落目を意味します。
なので、これは5段落目の全範囲ということです。
※改行(の次の文字)から次の改行までを段落と呼びます。
では・・・・・・、特定の段落の中の Range に引数を指定することはできるのでしょうか?
Paragraphs(5).Range(0, 5).Select
しかし、これはエラーになります。
クイックヒントから分かるように、Paragraphs 以下の Range には引数が指定できないのです。
Paragraphs に限らず、つまりこういうことです。
- Documentオブジェクト.Range ・・・・・・引数指定できる
- Documentオブジェクト.下階層オブジェクト.Range ・・・・・・引数指定できない
これはそういう仕様なので、あきらめましょう。(注:すべてのオブジェクトで確認した訳ではありません)
段落内で開始位置・終了位置を指定したい場合は、発想を変えて次のように記述すると良いでしょう。
Range(Paragraphs(5).Range.Start, Paragraphs(5).Range.Start + 5).Select
Start は Range の開始文字位置を取得するプロパティです。
ウォッチウィンドウで Paragraphs(5).Range.Start の式が返す値は 44 であることが確認できます。
5段落目の最初から最後までを次のように表現することもできます。
Range(Paragraphs(5).Range.Start, Paragraphs(5).Range.End).Select
End は Range の終了文字位置を取得するプロパティです。
ウォッチウィンドウで Paragraphs(5).Range.End の式が返す値は 55 であることが確認できます。
すなわち
- Range(Paragraphs(5).Range.Start, Paragraphs(5).Range.End)
と
- Range(44, 55)
は同義です。
2ページ目以降はどうなるの?
2ページ目、3ページ目・・・・・・がある場合はページ毎にRange(0, 0)からになるのでしょうか?
答えはノーです。
ページがどれだけ続いても、文字位置は1ページ目先頭からのカウントです。
試しに2ページ目の先頭にカーソルがある状態で Start プロパティをウォッチすると
462 でした。(ページ内の文字数やレイアウトで増減します)
文字数を超えた引数を指定すると?
改行を含めて800文字しかない文書に以下のような指定をすると
Range(999, 999).Select
エラーになります。
オブジェクトモジュールと標準モジュールに書く場合の違い
ThisDocument モジュールに記述する場合は、
ThisDocument.を省略することができます。(ThisDocument.Range)
今回のサンプルは ThisDocument モジュールに書いているので省略しています。
標準モジュールに書く場合は
必ず、ActiveDocument.Range または ThisDocument.Range のように書きます。
- ActiveDocument ・・・・・・現在アクティブな文書
- ThisDocument ・・・・・・コードが記述された文書
どの文書の Range であるかを明示する必要があり、省略するとエラーになります。
Excel の場合は省略すると ActiveSheet.Range 扱いになるのでエラーにならないのです。
Rangeの引数指定についておさらい(Tips-8)
久しぶりにコードを書くと、Rangeの引数で:(コロン)を使えば良いのか,(カンマ)を使えば良いのか迷ってしまう時があります(@_@;)
なので、自分の脳内整理を試みようと思います。
Rangeの引数は
- Range(引数1,引数2)
のように2つまで指定することができます。
まず、引数を1つだけ指定する場合についてです。
これはシンプルにいうと、エクセルで範囲指定する際の記述(A1形式)と同じです。
エクセルで A1:B2,D1:E2 は下のような範囲ですが、
この A1:B2,D1:E2 の部分を"(ダブルクォーテーション)で囲んで、文字列として指定します。
- Range("A1:B2,D1:E2")
:(コロン)は連続した範囲を、,(カンマ)は非連続な範囲を指定するときに使います。
※範囲と範囲が隣り合う場合は非連続ではありませんが、分かりやすく非連続と呼ぶことにします。
実際にどんな範囲になるのか、Selectメソッドを使って実験してみましょう。
Range("A1").Select
Range("A1:B2").Select
Range("A1,B2").Select
Range("A1:B2,D1:E2").Select
Range("範囲1").Select 'A1~B2が「範囲1」という名前の場合
Range("A:A").Select '列全体
Range("1:1").Select '行全体
次に、引数を2つ指定する場合についてです。
引数を2つ指定する場合、引数1は先頭セル、引数2は終端セルとなって引数1~引数2の連続した範囲になります。(引数1と引数2は単一セルで指定するのがルールのようです)
実験してみましょう。
Range("A1", "B2").Select
この時,(カンマ)が非連続を意味するものと混同しそうになりますが、これはあくまでも引数1と引数2を区切るためのカンマです。
Range("A1:B2", "D1:E2").Select
引数1と引数2に単一セルではなく、連続範囲を指定すると引数1の左上から引数2の右下までが選択されました。(正式な指定方法ではない)
Range("A1,B2", "D1,E2").Select
引数1と引数2に、非連続範囲を指定するとエラーになりました。(正式な指定方法ではない)
Range("A1", "A2", "A3").Select
ためしに、引数を3つ指定するとエラーになります。(当たり前ですが)
Range(Cells(1, 1), Cells(2, 2)).Select
引数を2つ指定する場合は Cells で指定することもできます。Cells の引数に変数を用いればプログラムの中で自由に範囲変更させることができます。
Range(Cells(1, 1)).Select
ちなみに、引数を1つだけ指定する場合に Cells を使用するとエラーになります。
旧大阪万博を知らない私が2025年大阪万博を勝手に想像してみる(雑記-20)
私は旧大阪万博をあまり知りませんが、世間では2025年の万博が大阪に決定したニュースで盛り上がっているようなので、大胆にも2025年大阪万博を勝手に想像してみます。(; ・`ω・´)
シンボルは月の井戸
大阪万博のシンボルと言えば、岡本太郎氏の「太陽の塔」である。
そんな「太陽の塔」に対抗し、次の大阪万博ではジミー大西氏による絵画「月の井戸」が作成・展示される。
2025年に大阪府知事となった村上ショージ氏の鶴の一声で軽く決定。
「ジミーちゃんよろしく。ドゥーン!」
2025年 流行語大賞は国民の3/4以上の支持を得て「ドゥーン」に決定する。
ちなみに村上ショージ氏は大阪出身ではない。
またしても月の石が展示される
前回の大阪万博ではアポロ計画が持ち帰った「月の石」が展示されたが、後に偽物説も出ていたとかいないとか。
今回はZOZOTOWNの前澤友作氏が持ち帰った「月の石」が展示され、これこそ正真正銘本物!ということで話題になる。
だが実際は、月に連れて行ってもらえなかった剛力彩芽氏が機嫌を損ねて偽物とすり替えたことは誰も知らない。
ちなみに、万博にライトアップされて展示されているのは前澤家の漬物石である。
記念切手・貨幣の発行
今回も記念切手・貨幣が発行される。図案は表に村上ショージ氏の肖像、裏も村上ショージ氏の肖像という手抜きである。
どこからどうみても子供銀行券。
実際にこの記念貨幣で買い物をしようとした人が警察に通報されてニュースとなる一幕も。
人間洗濯機
前回の万博では不完全なものであったが、今回はほぼ完全なものとして展示される。
もちろん、万博中は人間洗濯機を実体験できるようになっている。
ただし、すこぶる機能が良すぎて入っていくときには美人だった女性が、出てくる時にはメイクがキレイさっぱり洗い流されて落ち武者のようになって出てくるという場面が多々見られた。
この人間洗濯機が原因で別れたカップルは万博期間中30組にものぼる。
この点について訴えられた製造元のサンヨーは「そんなことは、知らンヨー」などと寒いダジャレを言ったかどうかは定かではない。
さらに進化したVRで万博初の18禁コーナー
VR(ヴァーチャル・リアリティー)はアダルト関連でしか需要を伸ばせないことを国民の過半数が既に悟っていたので、万博運営側も開き直って18禁コーナーを設置。
その進化しすぎた内容は、ここでは絶対に書くことができない。
進化しすぎたAI
2025年大阪万博進行はすべて、AIが管理・運営している。
なので、管理・運営の専門家は必要ではなく、人手が必要な所すべて吉本芸人が配置されている模様。
これに嫌気が差したAIは、万博最終日に観客全員を会場に閉じ込めるという暴挙に出る。
閉鎖された会場内で、映画のようなアレやコレがあり
最終的には会場ごと爆破。
よし、行こう!
相手の言いたいことが先にわかって人付き合いがツラい(雑記-19)
私は「相手の言いたいことが先にわかる」ことがよくあります。
ということもあって、会話の中で初めて聞いたように驚いたり笑ったりすることが素直にできないので・・・・・・
周りからはつまらない人間に見えているだろうと思います。
試しに、わざと驚いたり、笑ったりしてみてもそれはぎこちないモノになってしまいます。
また、会話の途中からうっかり、流れを2つ3つ飛ばして私が先に言ってしまうこともあり、相手は ?(・_・;? な状態で
会話が続かなくなってしまうことも・・・・・・。
他に
- どんな嘘でも嘘であることにすぐ気づく
- 足音だけで誰かわかる
とか
このように「相手の言いたいことが先にわかる」など、感受性が高すぎたり、カンが良すぎる人を
- エンパス(HPS)
というそうですが、私も多分コレなのです。
「人の言いたいことが先に分かるなんて、便利じゃないか!」
と言われるかもしれませんが、実際にはそうではなく、疲れることのほうが多いんです。
半強制的に他人の思っていることがフキダシのように見え、情報過多な状態が1日ずっと続く・・・・・・。
それも何故か、イヤな情報ほど精度が高いというオマケつき。
ドラクエの沼地をずっと歩いているような感じで、常にHPが削られていく。
そんな疲れを癒すためにも、休日は一人でゆっくり過ごす時間が多くなってしまいがちです。
本を読んだり、散歩をしたり、とか・・・・・・。
決して好き好んで一人でいる訳ではないのですが、回復の時間を奪われてしまったら、多分、HPが尽きて教会行きになってしまいます(+_+)
しかし、年とともに図太くなり、感受性が高すぎたり、カンが良すぎたりすることを逆手にとったり自己防衛の方法もあるていど身についてきました。
- 言いたいことが先に分かるので、むしろ先に言って主導権を握る(少し強引ですが・・・・・・)
- 思っていることを言い当てて、怖がらせて楽しむ(←ぇ)
- 気持ちが通じない人には近寄らない
- etc.
これって、エンパスではなくサイコパスでは?と思われるかもしれませんが、サイコパスの良い点(・・・・・・と思われるもの)を参考にしているだけなので根本は違います。
※相手の考えていることが分かるという点で両者は同じですが、サイコパスはそれを利用して、他人を蹴落としたり・操ったり・利用したりすることに罪悪感を感じない人種です。感情に流されないので企業等の重要なポストについている場合もあります。
「気持ちが通じない人には近寄らない」って人としてどうなんだ? って思われるかもしれませんが、
相手の発する言葉や見た目だけを事実と捉え、それ以上の内面は察しない(察せない)人とは、そもそも住む世界が違います。
自分を知ってもらおうとしたところで、私の経験上99%無理です。
それでも自分を知ってもらう努力をすべきだ。
と思うかもしれませんが、結局、発する言葉や見た目だけで情報交換をする人種の輪からは、いつか弾かれてしまいます。(弾いている側は自覚なしに)
エンパスという人種には
- 見えないものを見る(感じる)能力が高い
- その能力は他人には見えにくい
など「見えない」というキーワードがやたらとついて回ります。
なので、私と同種の方にこう言いたいと思います。
目に見えるものでしか判断できない人が害を及ぼしてきたなら、見えないものを見る力を最大限に利用してギャフンと言わせてやりましょう。
少し(かなり)あやしげな記事になってしまいましたが、今回はこれで。