PowerPoint プレゼンテーション

(1)

エクセルが自動化する

・・・

マクロVBAの

魔法

大量ファイルを自動処理する！

【ブック＆シートの処理入門】

4

(2)

第0章

このコースの進め方、ゴール設定

サンプルマクロの体験

(3)

マクロVBA講座シリーズでの位置づけ

【第１弾】超入門 ショートマクロ編 小さな作業を自動化 【第２弾】脱入門 請求書マクロ編 一覧表 → 帳票作成 【第３弾】実用入門 ユーザーフォーム編 データ入力効率化フォーム 【第４弾】実用入門 大量ブック操作編 大量データ収集マクロエクセル兄さんのシリーズ2,500人以上が受講（収録時点）

(4)

【ゴール設定】どんなスキルを習得する？

①大量ワークシートからデータ収集

②大量ブックからデータ収集

• ブックを開く

• ブックを操作

• 複数ブックを

順番に操作

• シート間での

やり取り

• 複数シートから

順番に収集

(5)

第1章

シートからシートへ転記する！

Worksheetオブジェクト間のやりとり方法

第2章

何度も出てくる記述は省略してラクに！

With構文、Worksheet変数でもっと効率的に

第3章

全てのシートからデータを抽出しよう！

ループ処理で、次々にシートを処理する！

第4章

ブックを開いて操作しよう！

ブックを開く/ Workbook変数に格納して操作！

第5章

複数のブックを次々に開いて操作しよう！

複数ブックを開く /配列の活用

第6章

いよいよ完成へ！50のブックでも一気に収集！

複数ブックから次々に転記する

このコースのカリキュラム全体像

大量

ワーク

シート

大量

ブック

(6)

講義のステップ

①講義

②Tips Card(ワンポイント)

③演習課題

(7)

読者特典

✓全編テキストの配布(100P近く)

✓ページ数を1/6に削減した縮小版

✓文法把握「Tipsカード」×30

✓最後のボーナスレクチャー

（応用ケーススタディ）

(8)

マスターするには

① 動画を見ながら一周する

※飛ばして途中からでもOK

② Tipsカードだけを見ながら

自分でマクロを作ってみる

③ 応用マクロを作ってみる

（最後のボーナスレクチャーに

ヒントあり）

(9)

【ゴール設定】サンプルマクロを使ってみよう

※圧縮ファイルになっています。

ダウンロードしたら、「全て展開」して下さい。

(10)

第1章

シートからシートへ転記する！

(11)

(12)

(1)ワークシートどうしのやりとり

ワークシート「請求書1」から、ワークシート「データ収集」にデータを転記しましょう。

ワークシート「データ収集」ワークシート「請求書1」 ❶ _❶ ❷ ❷ ❸ ❸ ❹ ❹ ❺ ❺

(13)

あるワークシート中のあるセルを指定する

Worksheets("ワークシート名").Range("セル").Value Worksheets("データ収集").Range("A2").Value (例)

あるワークシートのセルから、別のワークシートのセルへ転記する

Worksheets("シートA").Range("セル").Value = Worksheets("シートB").Range("セル").Value Worksheets("データ収集").Range("A2").Value = Worksheets("請求書1").Range("E2").Value (例) Tips Card レッスン(1)

(14)

'(1)ワークシートどうしのやりとり 'ワークシート「請求書1」から、ワークシート「データ収集」にデータを転記しましょう。 Sub example01() '請求書番号 (請求書1のE2からデータ収集のA2へ) Worksheets("データ収集").Range("A2").Value = Worksheets("請求書1").Range("E2").Value '発行日 (請求書1のE1からデータ収集のB2へ) Worksheets("データ収集").Range("B2").Value = Worksheets("請求書1").Range("E1").Value '会社名 (請求書1のB4からデータ収集のC2へ) Worksheets("データ収集").Range("C2").Value = Worksheets("請求書1").Range("B4").Value '担当者名 (請求書1のB5からデータ収集のD2へ) Worksheets("データ収集").Range("D2").Value = Worksheets("請求書1").Range("B5").Value '請求金額 (請求書1のE31からデータ収集のE2へ) Worksheets("データ収集").Range("E2").Value = Worksheets("請求書1").Range("E31").Value End Sub ?? ?? ?? ?? ?? 演習課題 レッスン(1)

(15)

'(1)ワークシートどうしのやりとり 'ワークシート「請求書1」から、ワークシート「データ収集」にデータを転記しましょう。 Sub example01() '請求書番号 (請求書1のE2からデータ収集のA2へ) Worksheets("データ収集").Range("A2").Value = Worksheets("請求書1").Range("E2").Value '発行日 (請求書1のE1からデータ収集のB2へ) Worksheets("データ収集").Range("B2").Value = Worksheets("請求書1").Range("E1").Value '会社名 (請求書1のB4からデータ収集のC2へ) Worksheets("データ収集").Range("C2").Value = Worksheets("請求書1").Range("B4").Value '担当者名 (請求書1のB5からデータ収集のD2へ) Worksheets("データ収集").Range("D2").Value = Worksheets("請求書1").Range("B5").Value '請求金額 (請求書1のE31からデータ収集のE2へ) Worksheets("データ収集").Range("E2").Value = Worksheets("請求書1").Range("E31").Value End Sub 解答

(16)

(2)最終行に転記する

シート「データ収集」へデータを転記する際、必ず最終行へ転記するようにしましょう。

最終行へ転記する

(17)

最終行

(+1)

を取得する

Worksheets("ワークシート名").Cells(Rows.Count, 1).End(xlUp).Row + 1

変数を用意し、最終行を取得する

Dim MaxRow As Integer

MaxRow = Worksheets("ワークシート名").Cells(Rows.Count, 1).End(xlUp).Row + 1 最大行数 Endモード

で上へ行番号

整数型

(18)

'(2)最終行に転記する

'「データ収集」へデータを転記する際、必ず最終行へ転記するようにしましょう。 Sub example02()

'「データ収集」の最終行を変数に取得 Dim MaxRow As Integer

'[請求書番号,発行日,会社名,担当者名,請求金額]をそれぞれ転記する Worksheets("データ収集").Range("A2").Value = Worksheets("請求書1").Range("E2").Value Worksheets("データ収集").Range("B2").Value = Worksheets("請求書1").Range("E1").Value Worksheets("データ収集").Range("C2").Value = Worksheets("請求書1").Range("B4").Value Worksheets("データ収集").Range("D2").Value = Worksheets("請求書1").Range("B5").Value Worksheets("データ収集").Range("E2").Value = Worksheets("請求書1").Range("E31").Value End Sub ?? 演習課題 レッスン(2) 修正

(19)

'(2)最終行に転記する

'「データ収集」へデータを転記する際、必ず最終行へ転記するようにしましょう。 Sub example02()

'「データ収集」の最終行を変数に取得 Dim MaxRow As Integer

MaxRow = Worksheets("データ収集").Cells(Rows.Count, 1).End(xlUp).Row + 1 '[請求書番号,発行日,会社名,担当者名,請求金額]をそれぞれ転記する

Worksheets("データ収集").Range("A" & MaxRow).Value = Worksheets("請求書1").Range("E2").Value Worksheets("データ収集").Range("B" & MaxRow).Value = Worksheets("請求書1").Range("E1").Value Worksheets("データ収集").Range("C" & MaxRow).Value = Worksheets("請求書1").Range("B4").Value Worksheets("データ収集").Range("D" & MaxRow).Value = Worksheets("請求書1").Range("B5").Value Worksheets("データ収集").Range("E" & MaxRow).Value = Worksheets("請求書1").Range("E31").Value End Sub

(20)

(3)データ収集結果を削除する

シート「データ収集」へ転記されたデータを削除するマクロを作っておきましょう。

(21)

セル範囲のデータを削除する

Worksheets("ワークシート名").Range("セル範囲").Value = ""

あるシートの2行目から最終行までを削除する

Worksheets("ワークシート名").Range("2:" & MaxRow).Value = ""

最終行を格納している変数空白を代入

"2:" & MaxRow

結合

(22)

'(3)データ収集結果を削除する

'転記されたデータを削除するマクロを作っておきましょう。 Sub clear03()

'最終行を変数に取得 Dim MaxRow As Integer

MaxRow = Worksheets("データ収集").Cells(Rows.Count, 1).End(xlUp).Row + 1 '2行目から最終行までのデータを削除する

End Sub

??

(23)

'(3)データ収集結果を削除する

'転記されたデータを削除するマクロを作っておきましょう。 Sub clear03()

'最終行を変数に取得 Dim MaxRow As Integer

MaxRow = Worksheets("データ収集").Cells(Rows.Count, 1).End(xlUp).Row + 1 '2行目から最終行までのデータを削除する

Worksheets("データ収集").Range("2:" & MaxRow).Value = "" End Sub

(24)

第2章

何度も出てくる記述は省略してラクに！

(25)

(4)With構文を使って省略する

With構文を使って、ワークシート名を省略して入力しましょう。

Worksheets("データ収集").Range("A" & MaxRow).Value = Worksheets("請求書1").Range("E2").Value Worksheets("データ収集").Range("B" & MaxRow).Value = Worksheets("請求書1").Range("E1").Value Worksheets("データ収集").Range("C" & MaxRow).Value = Worksheets("請求書1").Range("B4").Value Worksheets("データ収集").Range("D" & MaxRow).Value = Worksheets("請求書1").Range("B5").Value Worksheets("データ収集").Range("E" & MaxRow).Value = Worksheets("請求書1").Range("E31").Value

With Worksheets("データ収集")

.Range("A" & MaxRow).Value = Worksheets("請求書1").Range("E2").Value .Range("B" & MaxRow).Value = Worksheets("請求書1").Range("E1").Value .Range("C" & MaxRow).Value = Worksheets("請求書1").Range("B4").Value .Range("D" & MaxRow).Value = Worksheets("請求書1").Range("B5").Value .Range("E" & MaxRow).Value = Worksheets("請求書1").Range("E31").Value End With

(26)

With構文を使って記述を省略する

With Worksheets("ワークシート名") .Range("A1").Value = 10 .Range("A2").Value = 20 .Range("A3").Value = 30 End With Worksheets("ワークシート名")を省略して .Range以降を記述すればよい

With _Worksheets("ワークシート名_{").Range("A1")} .Value = 10 .Font.Bold = True .Font.Italic = True End With (別例) Worksheets("ワークシート名") .Range("A1")を省略してそれ以降の.プロパティ以降を記述すればよい Tips Card レッスン(4)

(27)

'(4)With構文を使って省略する

'With構文を使って、ワークシート名を省略して入力しましょう。 Sub example04()

'最終行を取得しておく Dim MaxRow As Integer

演習課題 レッスン(4)

(28)

'(4)With構文を使って省略する

'With構文を使って、ワークシート名を省略して入力しましょう。 Sub example04()

With Worksheets("データ収集")

.Range("A" & MaxRow).Value = Worksheets("請求書1").Range("E2").Value .Range("B" & MaxRow).Value = Worksheets("請求書1").Range("E1").Value .Range("C" & MaxRow).Value = Worksheets("請求書1").Range("B4").Value .Range("D" & MaxRow).Value = Worksheets("請求書1").Range("B5").Value .Range("E" & MaxRow).Value = Worksheets("請求書1").Range("E31").Value End With

End Sub

(29)

(5)Worksheet変数を利用する

Worksheet変数を宣言して、ワークシートを格納して利用しましょう。

Worksheets("データ収集").Range("A" & MaxRow).Value = Worksheets("請求書1").Range("E2").Value Worksheets("データ収集").Range("B" & MaxRow).Value = Worksheets("請求書1").Range("E1").Value Worksheets("データ収集").Range("C" & MaxRow).Value = Worksheets("請求書1").Range("B4").Value Worksheets("データ収集").Range("D" & MaxRow).Value = Worksheets("請求書1").Range("B5").Value Worksheets("データ収集").Range("E" & MaxRow).Value = Worksheets("請求書1").Range("E31").Value

wsTotal.Range("A" & MaxRow).Value = wsInv.Range("E2").Value wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value

(30)

Worksheet変数を宣言し、ワークシートを格納する

'変数を宣言

Dim wsTotal As Worksheet '変数にワークシートを格納

Set wsTotal = Worksheets("データ収集")

Tips Card

※Worksheet「s」ではないので注意

オブジェクト変数という分類なので、格納するとき「Set」が必要

Worksheet変数に格納したワークシートを利用する

'変数を宣言し、ワークシートを格納 Dim wsTotal As Worksheet

Set wsTotal = Worksheets("データ収集") '数値を代入する

wsTotal.Range("A1").Value = 10

変数に続けて、.Range以降を記述すればよいレッスン(5)

(31)

'(5)Worksheet変数を利用する

'Worksheet変数を宣言して、ワークシートを格納して利用しましょう。 Sub example05()

'Worksheet変数を宣言する Dim wsTotal As Worksheet Dim wsInv As Worksheet

'「データ収集」と「請求書1」を変数に格納する

??

(32)

'(5)Worksheet変数を利用する

'Worksheet変数を宣言して、ワークシートを格納して利用しましょう。 Sub example05()

'Worksheet変数を宣言する Dim wsTotal As Worksheet Dim wsInv As Worksheet

'「データ収集」と「請求書1」を変数に格納する Set wsTotal = Worksheets("データ収集")

Set wsInv = Worksheets("請求書1") '最終行を取得しておく

Dim MaxRow As Integer

wsTotal.Range("A" & MaxRow).Value = wsInv.Range("E2").Value wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value End Sub

(33)

(6)3つのシートを順番に転記 (ループなし)

請求書1、請求書2、請求書3の順にデータを転記しましょう。

ワークシート「請求書1」ワークシート「請求書2」ワークシート「請求書3」 wsInv wsTotal 格納転記

(34)

Worksheet変数を利用して、３つのシートを順番に処理する

'変数を宣言

Dim wsTotal As Worksheet

'変数にワークシート1を格納し、データを代入 Set wsTotal = Worksheets("ワークシート1") wsTotal.Range("A1").Value = 10

'同じ変数にワークシート2を格納し、データを代入 Set wsTotal = Worksheets("ワークシート2")

wsTotal.Range("A1").Value = 10

'同じ変数にワークシート3を格納し、データを代入 Set wsTotal = Worksheets("ワークシート3")

wsTotal.Range("A1").Value = 10 Tips Card 変数を1つだけ宣言する 1つ目のシート 2つ目のシート 3つ目のシートレッスン(6)

(35)

'(6)3つのシートを順番に転記 (ループなし)

'請求書1、請求書2、請求書3の順にデータを転記しましょう。 Sub example06()

'変数にワークシートを格納する Dim wsTotal As Worksheet

Dim wsInv As Worksheet

Set wsTotal = Worksheets("データ収集") Set wsInv = Worksheets("請求書1")

wsTotal.Range("A" & MaxRow).Value = wsInv.Range("E2").Value wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value '次の処理のため、最終行を+1しておく

MaxRow = MaxRow + 1??

(36)

'---請求書2の処理---'請求書2を変数に代入

Set wsInv = Worksheets("請求書2")

'[請求書番号,発行日,会社名,担当者名,請求金額]をそれぞれ転記する wsTotal.Range("A" & MaxRow).Value = wsInv.Range("E2").Value

wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value '次の処理のため、最終行を+1しておく MaxRow = MaxRow + 1 ?? ?? 演習課題 つづき '(次のページへ)

(37)

wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value End Sub

??

(38)

'(6)3つのシートを順番に転記 (ループなし)

'請求書1、請求書2、請求書3の順にデータを転記しましょう。 Sub example06()

'変数にワークシートを格納する Dim wsTotal As Worksheet

Dim wsInv As Worksheet

Set wsTotal = Worksheets("データ収集") Set wsInv = Worksheets("請求書1")

wsTotal.Range("A" & MaxRow).Value = wsInv.Range("E2").Value wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value '次の処理のため、最終行を+1しておく

MaxRow = MaxRow + 1

解答

(39)

wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value '次の処理のため、最終行を+1しておく

MaxRow = MaxRow + 1 つづき

解答

(40)

wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value End Sub

つづき

(41)

第3章

全てのシートからデータを抽出しよう！

(42)

(7) (ループあり)3つのシートを順番に転記

Forループを利用し、請求書1～3の順にデータを転記しましょう。

「請求書1」「請求書2」「請求書3」 iの値 請求書&i 1 請求書1 2 請求書2 3 請求書3 i iが1~3まで繰り返し 変数iを宣言 請求書 & iを格納 wsInv wsTotal wsInv 転記する「i」ループ変数（カウンタ変数） iを+1して繰り返す

(43)

'繰り返し（iが1～3まで） Dim i As Integer

For i = 1 To 3

Worksheets("請求書" & i).Range("A1").Value = 10 Next i

Forループを利用し、3回反復処理する

'ループ変数を宣言 Dim i As Integer '繰り返し（iが1～3まで） For i = 1 To 3 MsgBox i Next i Tips Card

Forループを利用し、請求書1~3を処理する

請求書1,請求書2,請求書3を順番に処理 "請求書" & i 結合レッスン(7)

(44)

'(7)3つのシートを順番に転記（ループあり） 'Forループを利用し、請求書1～3の順にデータを転記しましょう。 Sub example07() 'ループ変数iを宣言 Dim i As Integer 'Forループ（iが1～3まで繰り返す） For i = 1 To 3 '請求書iを変数に格納する

Set wsInv = Worksheets("請求書" & i)

'[請求書番号,発行日,会社名,担当者名,請求金額]をそれぞれ転記する wsTotal.Range("A" & MaxRow).Value = wsInv.Range("E2").Value wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value '次のループのため、最終行を+1しておく MaxRow = MaxRow + 1 Next i End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～ ?? ?? ?? ?? 演習課題 レッスン(7)

(45)

'(7)3つのシートを順番に転記（ループあり） 'Forループを利用し、請求書1～3の順にデータを転記しましょう。 Sub example07() 'ループ変数iを宣言 Dim i As Integer 'Forループ（iが1～3まで繰り返す） For i = 1 To 3 '請求書iを変数に格納する

Set wsInv = Worksheets("請求書" & i)

'[請求書番号,発行日,会社名,担当者名,請求金額]をそれぞれ転記する wsTotal.Range("A" & MaxRow).Value = wsInv.Range("E2").Value wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value '次のループのため、最終行を+1しておく MaxRow = MaxRow + 1 Next i End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～解答

(46)

(8)For Eachを使い、全シートを扱う

For Eachループを使い、全シートのシート名を順番に出力してみましょう。

Worksheetsコレクション ※全てのワークシート wsInv For Eachループ MsgBox wsInv .Name 1つずつ取り出す

(47)

For Eachループを利用し、すべてのワークシートを順番に利用する

'ワークシート変数を宣言 Dim wsInv As Worksheet

'すべてのワークシートから1つずつ取り出し、wsInvに格納 For Each wsInv In Worksheets

'ワークシート名をMsgBoxで出力する MsgBox wsInv.Name Next wsInv Tips Card ※Worksheet「s」なので注意 .Nameプロパティ…ワークシート名レッスン(8)

(48)

'(8)For Eachを使い、全シートを扱う

'For Eachループを使い、全シートのシート名を順番に出力してみましょう。 Sub example08()

'すべてのワークシートから1つずつ取り出し、wsInvに格納 'ワークシート名をMsgBoxで出力する MsgBox wsInv.Name End Sub ?? ?? 演習課題 レッスン(8)

(49)

'(8)For Eachを使い、全シートを扱う

'For Eachループを使い、全シートのシート名を順番に出力してみましょう。 Sub example08()

'ワークシート名をMsgBoxで出力する MsgBox wsInv.Name

Next wsInv End Sub

(50)

(9)For Eachを使い、全シートを順番に転記

For Eachループを使い、全シートから順番にデータを転記しましょう。

Worksheetsコレクション ※全てのワークシート wsInv For Eachループ wsInv wsTotal 転記する wsTotalと同じ シートでないなら No 1つずつ取り出す

(51)

For Eachループの中でIf文を利用する

'もしワークシート名が「データ収集」と一致しなければ If wsInv.Name <> "データ収集" Then 'ワークシート名をMsgBoxで出力 MsgBox wsInv.Name End If Next wsInv Tips Card <> …等しくない（比較演算子）レッスン(9)

(52)

'(9)For Eachを使い、全シートを順番に転記する 'For Eachループを使い、全シートから順番にデータを転記しましょう。 Sub example09() 'すべてのワークシートから1つずつ取り出し、wsInvに格納 'もしワークシート名が「データ収集」と一致しなければ '[請求書番号,発行日,会社名,担当者名,請求金額]をそれぞれ転記する wsTotal.Range("A" & MaxRow).Value = wsInv.Range("E2").Value

wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value '次のループのため、最終行を+1しておく MaxRow = MaxRow + 1 End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～ ?? ?? ?? ?? 演習課題 レッスン(9)

(53)

'(9)For Eachを使い、全シートを順番に転記する

'For Eachループを使い、全シートから順番にデータを転記しましょう。 Sub example09()

'もしワークシート名が「データ収集」と一致しなければ If wsInv.Name <> wsTotal.Name Then

wsTotal.Range("B" & MaxRow).Value = wsInv.Range("E1").Value wsTotal.Range("C" & MaxRow).Value = wsInv.Range("B4").Value wsTotal.Range("D" & MaxRow).Value = wsInv.Range("B5").Value wsTotal.Range("E" & MaxRow).Value = wsInv.Range("E31").Value '次のループのため、最終行を+1しておく MaxRow = MaxRow + 1 End If Next wsInv End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～解答

(54)

第4章

ブックを開いて操作しよう！

(55)

(10)Workbooks.Openでファイルを開く

特定のパスにあるExcelブックを開きましょう。

パス

C:¥Users¥[あなたのユーザー名]¥Downloads¥ … [ファイル名].xlsx

C:

Users

[あなたのユーザー名]

Downloads

[ファイル名].xlsx

Workbooks.Open

… openPath

(56)

Workbooks.Openでファイルを開く

'特定のパスにあるExcelブックを開く

Workbooks.Open ("C:¥Users¥[ユーザー名]¥Downloads¥50Books¥20190603VBA商事様_請求書.xlsx")

(57)

'(10)Workbooks.Openでファイルを開く '特定のパスにあるExcelブックを開きましょう。 Sub example10() '特定のパスにあるExcelブックを開く Workbooks.Open (" ") End Sub ?? 演習課題 レッスン(10) 解答

(58)

(11)ダイアログで選んだブックを開く(単一)

ダイアログ画面で選んだExcelブックを開きましょう。

「Microsoft Excelブック」と表示し、

(

*

.xls?)

で絞り込んだ結果を表示

openPath

Application.GetOpenFilename

パス Workbooks.Open

(59)

Workbooks.Openでブックを選ぶ

'ダイアログからブックを選ぶ Application.GetOpenFilename("Microsoft Excelブック,*.xls?") Tips Card 記号用例 抽出されるファイル名 * *.xlsx A.xlsx , B.xlsx , C.xlsx… ? *.xls? A.xlsx , D.xlsm , E.xlsb ワイルドカードで絞り込む表示させたい任意の文字列 ※ただし、これでは選ぶだけであり、ブックは開かれない

Workbooks.Openで選んだブックを開く

'パスを格納する変数を宣言 Dim openPath As String

'ダイアログからブックを選び、そのパスを格納

openPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?") 'パスにあるブックを開く

Workbooks.Open (openPath)

パスを格納するので、文字列型とするレッスン(11)

(60)

'(11)ダイアログで選んだブックを開く（単一）

'ダイアログ画面で選んだExcelブックを開きましょう。 Sub example11()

'ダイアログからブックを選び、そのパスを格納 openPath = 'パスにあるブックを開く Workbooks.Open (openPath) End Sub ?? 演習課題 レッスン(11)

(61)

'(11)ダイアログで選んだブックを開く（単一）

'ダイアログ画面で選んだExcelブックを開きましょう。 Sub example11()

'ダイアログからブックを選び、そのパスを格納

openPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?") 'パスにあるブックを開く

Workbooks.Open (openPath) End Sub

(62)

(12)ダイアログ

から

ブック

を

開

いて

操作 (単一)

開いたブックをWorkbook型変数に格納し、プロパティやメソッドを利用してみましょう。

openPath Workbooks.Open Application.GetOpenFilename openBook Workbook型 の変数 openBook MsgBox openBook .Name ①どれを開きたい？ _{②パスはこれ} _{③開きます！} _{④受け取ります！} ⑤受け取ってしまえば… プロパティを利用したり openBook .Close メソッドを利用したりブックを操作できる！

(63)

Workbook型の変数を宣言し、ワークブックを格納

'Workbook型の変数を宣言 Dim openBook As Workbook

'現在のワークブックを変数に格納する

Set openBook = Application.ActiveWorkbook

Tips Card 現在開いている（アクティブな）ワークブック ※[Application.は省略可] Workbook「s」ではないので注意

ダイアログで開いたブックを、Workbook型の変数に格納する

'ダイアログからブックを選び、パスを格納する Dim openPath As String

openPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?") 'Workbook型の変数を宣言

Dim openBook As Workbook

'ファイルを開き、変数に格納する

Set openBook = Workbooks.Open(openPath) レッスン(12)

(64)

'(12)ダイアログからブックを開き、操作する（単一）

'開いたブックのプロパティやメソッドを利用してみましょう。 Sub example12()

Set openBook = Workbooks.Open(openPath) 'ブックの名前をMsgboxで出力（.Nameプロパティ） MsgBox openBook.Name 'ブックのパスをMsgBoxで出力（.Pathプロパティ） MsgBox openBook.Path 'ブックを閉じる（.Closeメソッド） openBook.Close End Sub ?? ?? ?? ?? ?? 演習課題 レッスン(12)

(65)

'(12)ダイアログからブックを開き、操作する（単一）

'開いたブックのプロパティやメソッドを利用してみましょう。 Sub example12()

Set openBook = Workbooks.Open(openPath) 'ブックの名前をMsgboxで出力（.Nameプロパティ） MsgBox openBook.Name 'ブックのパスをMsgBoxで出力（.Pathプロパティ） MsgBox openBook.Path 'ブックを閉じる（.Closeメソッド） openBook.Close End Sub 解答

(66)

(13)ダイアログ

の

キャンセル

への

エラー処理

ダイアログで「キャンセル」を押した場合のエラー対策をしましょう。

openPath パスを格納キャンセルしなかった場合 キャンセルした場合 openPath キャンセルした！ファイルを選択

(67)

ダイアログで「キャンセル」を押した場合のエラー対策

openPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?") 'もしパスが「False」でなければ処理を行う（Falseなら何もしない）

If openPath <> "False" Then

MsgBox "キャンセルされませんでした。" End If

(68)

'(13)ダイアログのキャンセルに対するエラー処理

'ダイアログで「キャンセル」を押した場合のエラー対策をしましょう。 Sub example13()

'変数を用意し、ブックを開いて格納 Dim openBook As Workbook

Set openBook = Workbooks.Open(openPath) 'ブックのプロパティやメソッドを利用 MsgBox openBook.Name MsgBox openBook.Path openBook.Close End If End Sub 演習課題 レッスン(13) ?? ??

(69)

'(13)ダイアログのキャンセルに対するエラー処理

Set openBook = Workbooks.Open(openPath) 'ブックのプロパティやメソッドを利用 MsgBox openBook.Name MsgBox openBook.Path openBook.Close End If End Sub 解答

(70)

(14)開いたブックからデータを転記(単一)

開いたブックから請求データを転記しましょう。

Workbooks.Open openBook Workbook型の変数 wsTotal 転記 openBook ...の中の １枚目のシート 開いたブック

(71)

Tips Card

ダイアログで開いたブックのデータを扱う

openPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?") '変数を用意し、ブックを開いて格納

Set openBook = Workbooks.Open(openPath) '開いたブックのデータを出力してみる

MsgBox openBook.Worksheets(1).Range("A1").Value

Worksheets(1)で、一番目のワークシートを指す。

（ワークシート名を指定しなくても済むので、使い勝手のいい方法）レッスン(14)

(72)

'(14)開いたブックからデータを転記(単一)

'開いたブックから請求データを転記しましょう。 Sub example14()

'パスが「False」でなければ処理を行う If openPath <> "False" Then

Set openBook = Workbooks.Open(openPath)

'[請求書番号,発行日,会社名,担当者名,請求金額]をそれぞれ転記する '※openBookの最初のワークシート(1)から

wsTotal.Range("A" & MaxRow).Value = openBook.Worksheets(1).Range("E2").Value wsTotal.Range("B" & MaxRow).Value = openBook.Worksheets(1).Range("E1").Value wsTotal.Range("C" & MaxRow).Value = openBook.Worksheets(1).Range("B4").Value wsTotal.Range("D" & MaxRow).Value = openBook.Worksheets(1).Range("B5").Value wsTotal.Range("E" & MaxRow).Value = openBook.Worksheets(1).Range("E31").Value 'openBookを閉じる openBook.Close End If End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～ ?? 演習課題 レッスン(14)

(73)

'(14)開いたブックからデータを転記(単一)

'開いたブックから請求データを転記しましょう。 Sub example14()

'パスが「False」でなければ処理を行う If openPath <> "False" Then

Set openBook = Workbooks.Open(openPath)

wsTotal.Range("A" & MaxRow).Value = openBook.Worksheets(1).Range("E2").Value wsTotal.Range("B" & MaxRow).Value = openBook.Worksheets(1).Range("E1").Value wsTotal.Range("C" & MaxRow).Value = openBook.Worksheets(1).Range("B4").Value wsTotal.Range("D" & MaxRow).Value = openBook.Worksheets(1).Range("B5").Value wsTotal.Range("E" & MaxRow).Value = openBook.Worksheets(1).Range("E31").Value 'openBookを閉じる openBook.Close End If End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～解答

(74)

第5章

複数のブックを次々に開いて操作しよう！

複数ブックを開く /配列の活用

(75)

(15)複数のブックを次々に開く！

複数のブックのパスを配列に格納し、次々に開きましょう。

openPath 変数にパスを格納 １つのブックの場合ファイルを選択 arrayPath 配列にパスを格納 複数のブックの場合 複数ファイルを選択 (1) (2) (3)

(76)

ダイアログで複数のブックを選ぶ

'配列のための変数を宣言 (Variant型) Dim arrayPath As Variant

'ダイアログから複数のブックを選び、配列にパスを格納する

arrayPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?", MultiSelect:=True)

Tips Card

ダイアログで複数のブックを選び、1～3番目を開く

arrayPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?", MultiSelect:=True) '配列の要素(1)(2)(3)に格納されたパスからブックを順に開く Workbooks.Open (arrayPath(1)) Workbooks.Open (arrayPath(2)) Workbooks.Open (arrayPath(3)) 複数選択を可にする レッスン(15)

(77)

'(15)複数のブックを次々に開く!

'複数のブックのパスを配列に格納し、次々に開きましょう。 Sub example15()

arrayPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?", MultiSelect:=True) '配列の要素(1)(2)(3)に格納されたパスからブックを順に開く Workbooks.Open (arrayPath(1)) Workbooks.Open (arrayPath(2)) Workbooks.Open (arrayPath(3)) End Sub 配列になって受け取る (1) (2) (3) … 最後 Variant型変数 ?? ?? ?? 演習課題 レッスン(15)

(78)

'(15)複数のブックを次々に開く!

'複数のブックのパスを配列に格納し、次々に開きましょう。 Sub example15()

arrayPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?", MultiSelect:=True) '配列の要素(1)(2)(3)に格納されたパスからブックを順に開く Workbooks.Open (arrayPath(1)) Workbooks.Open (arrayPath(2)) Workbooks.Open (arrayPath(3)) End Sub 配列になって受け取る (1) (2) (3) … 最後 Variant型変数解答

(79)

(16)複数のブックを最後まで全て順番に開く

配列の１番目から要素の最大数まで、順番に開きましょう。

arrayPath 配列にパスを格納 複数ファイルを選択 (1) (2) (3) (4) (5) (6) … 最後

UBound(arrayPath)

配列の要素数を返す Workbooks.Open １つずつ開く

(80)

配列の要素数を取得する

UBound(配列名) Tips Card

配列の最後までForループで繰り返す

'Forループ(iが1から配列の要素数まで) For i = 1 To UBound(arrayPath) '配列の要素(i番目)のパスからブックを開く Workbooks.Open (arrayPath(i)) Next i 配列の要素が10あるなら「10」を取得配列名だけを指定する 配列のi番目を指定する レッスン(16)

(81)

'(16)複数のブックを最後まで全て順番に開く

'配列の１番目から要素の最大数まで、順番に開きましょう。 Sub example16()

'ダイアログから複数のブックを選び、配列にパスを格納する Dim arrayPath As Variant

arrayPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?", MultiSelect:=True) 'ループ変数iを宣言 Dim i As Integer 'Forループ(iが1から配列の要素数まで) For i = 1 To UBound(arrayPath) '配列の要素(i番目)のパスからブックを開く Workbooks.Open (arrayPath(i)) Next i End Sub ?? ?? 演習課題 レッスン(16) ??

(82)

'(16)複数のブックを最後まで全て順番に開く

'配列の１番目から要素の最大数まで、順番に開きましょう。 Sub example16()

arrayPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?", MultiSelect:=True) 'ループ変数iを宣言 Dim i As Integer 'Forループ(iが1から配列の要素数まで) For i = 1 To UBound(arrayPath) '配列の要素(i番目)のパスからブックを開く Workbooks.Open (arrayPath(i)) Next i End Sub 解答

(83)

(17)複数のブックを開き、操作する

開いた複数ブックのプロパティやメソッドを利用してみましょう。

arrayPath (1) (2) (3) (4) (5) (6) … 最後 Workbooks.Open openBook Workbook型の変数 １つずつ開く MsgBox openBook .Name プロパティを利用したり openBook .Close メソッドを利用したり

(84)

複数ブックを開き、プロパティやメソッドを利用する

'Forループ(iが1から配列の要素数まで) Dim i As Integer

For i = 1 To UBound(arrayPath)

Set openBook = Workbooks.Open(arrayPath(i)) 'ブックのプロパティやメソッドを利用

MsgBox openBook.Name openBook.Close

Next i

(85)

'(17)複数のブックを開き、操作する

'開いた複数ブックのプロパティやメソッドを利用してみましょう。 Sub example17()

Set openBook = Workbooks.Open(arrayPath(i)) 'ブックのプロパティやメソッドを利用 MsgBox openBook.Name openBook.Close Next i End Sub ?? ?? 演習課題 レッスン(17)

(86)

'(17)複数のブックを開き、操作する

'開いた複数ブックのプロパティやメソッドを利用してみましょう。 Sub example17()

Set openBook = Workbooks.Open(arrayPath(i)) 'ブックのプロパティやメソッドを利用 MsgBox openBook.Name openBook.Close Next i End Sub 解答

(87)

(18)

[複数ブック版]ダイアログのキャンセルへのエラー処理

ダイアログで「キャンセル」を押した場合のエラー対策をしましょう。

配列になって受け取る (1) (2) (3) … 最後キャンセルしなかった場合 Variant型変数 キャンセルした場合 Variant型変数 Boolean型の変数になって受け取る （真偽値[True,False]を受け取る型）つまり、この場合 配列ではない

(88)

配列であるかどうかチェックする

IsArray(配列名)

Tips Card

[複数ブック版]ダイアログのキャンセルに対するエラー処理

arrayPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?", MultiSelect:=True) 'もしopenPathが配列ならば、処理をする（そうでなければ何もしない） If IsArray(arrayPath) Then MsgBox "キャンセルされませんでした。" End If 引数が配列であればTrueを返す レッスン(18)

(89)

'(18)[複数ブック版]ダイアログのキャンセルに対するエラー処理

arrayPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?", MultiSelect:=True) 'もしopenPathが配列ならば、処理をする（そうでなければ何もしない） If IsArray(arrayPath) Then 'Forループ(iが1から配列の要素数まで) Dim i As Integer For i = 1 To UBound(arrayPath) Next i End If End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～ ?? 演習課題 レッスン(18) ??

(90)

'(18)[複数ブック版]ダイアログのキャンセルに対するエラー処理

arrayPath = Application.GetOpenFilename("Microsoft Excelブック,*.xls?", MultiSelect:=True) 'もしopenPathが配列ならば、処理をする（そうでなければ何もしない） If IsArray(arrayPath) Then 'Forループ(iが1から配列の要素数まで) Dim i As Integer For i = 1 To UBound(arrayPath) Next i End If End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～解答

(91)

第6章

いよいよ完成へ！50のブックでも一気に収集！

複数ブックから次々に転記する

(92)

(19)複数のブックを順番に開き、データ収集

次々に開いたブックから請求データを転記しましょう。

arrayPath (1) (2) (3) (4) (5) (6) … 最後 Workbooks.Open openBook Workbook型の変数 開いたブック wsTotal 転記 ...の中の １枚目のシート １つずつ開く

(93)

Tips Card

ダイアログで開いた複数のブックのデータを利用する

'Forループ(iが1から配列の要素数まで) Dim i As Integer For i = 1 To UBound(arrayPath) '変数を用意し、ブックを開いて格納 Dim openBook As Workbook

Set openBook = Workbooks.Open(arrayPath(i)) 'データを出力 MsgBox openBook.Worksheets(1).Range("A1").Value 'openBookを閉じる openBook.Close Next i Worksheets(1)で、一番目のワークシートを指す。 （ワークシート名を指定しなくても済むので、使い勝手のいい方法）レッスン(19)

(94)

'(19)複数のブックを順番に開き、データ収集 '次々に開いたブックから請求データを転記しましょう。 Sub example19() 'Forループ(iが1から配列の要素数まで) Dim i As Integer For i = 1 To UBound(arrayPath) '変数を用意し、ブックを開いて格納 Dim openBook As Workbook

Set openBook = Workbooks.Open(arrayPath(i))

wsTotal.Range("A" & MaxRow).Value = openBook.Worksheets(1).Range("E2").Value wsTotal.Range("B" & MaxRow).Value = openBook.Worksheets(1).Range("E1").Value wsTotal.Range("C" & MaxRow).Value = openBook.Worksheets(1).Range("B4").Value wsTotal.Range("D" & MaxRow).Value = openBook.Worksheets(1).Range("B5").Value wsTotal.Range("E" & MaxRow).Value = openBook.Worksheets(1).Range("E31").Value 'openBookを閉じる openBook.Close '次のループのため、最終行を+1しておく MaxRow = MaxRow + 1 Next i ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～ ?? ?? 演習課題 レッスン(19)

(95)

'(19)複数のブックを順番に開き、データ収集 '次々に開いたブックから請求データを転記しましょう。 Sub example19() 'Forループ(iが1から配列の要素数まで) Dim i As Integer For i = 1 To UBound(arrayPath) '変数を用意し、ブックを開いて格納 Dim openBook As Workbook

Set openBook = Workbooks.Open(arrayPath(i))

wsTotal.Range("A" & MaxRow).Value = openBook.Worksheets(1).Range("E2").Value wsTotal.Range("B" & MaxRow).Value = openBook.Worksheets(1).Range("E1").Value wsTotal.Range("C" & MaxRow).Value = openBook.Worksheets(1).Range("B4").Value wsTotal.Range("D" & MaxRow).Value = openBook.Worksheets(1).Range("B5").Value wsTotal.Range("E" & MaxRow).Value = openBook.Worksheets(1).Range("E31").Value 'openBookを閉じる openBook.Close '次のループのため、最終行を+1しておく MaxRow = MaxRow + 1 Next i ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～解答

(96)

(20)画面のチラつきを防止する

ブックの開閉のたびに画面がチラつくことを防止しましょう。

Application.ScreenUpdating = False

（処理） ブックを開く ブックを閉じる ループ • 画面がチラつく • 処理に時間がかかる

Application.ScreenUpdating = True

画面の描画を停止する画面の描画を再開する

(97)

画面のチラつきを防止する

'画面の描画を停止する Application.ScreenUpdating = False '---ブックの開閉のような、画面がチラつく処理---'画面の描画を再開する Application.ScreenUpdating = True Tips Card ※画面の描画を再開するまで Excel画面に結果が反映されないので注意レッスン(20)

(98)

'(20)画面のチラつきを防止する 'ブックの開閉のたびに画面がチラつくことを防止しましょう。 Sub example20() 'もしopenPathが配列ならば If IsArray(arrayPath) Then '画面の描画を停止する Application.ScreenUpdating = False 'Forループ(iが1から配列の要素数まで) Dim i As Integer For i = 1 To UBound(openPath) Next i '画面の描画を再開する Application.ScreenUpdating = True '処理が終わったことをMsgBoxで出力 MsgBox "全ブックからデータを抽出しました。" End If End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～～～～～～～～(途中省略)～～～～～～～～～～～～～～～～ ?? ?? 演習課題 レッスン(20)

(99)

'(20)画面のチラつきを防止する 'ブックの開閉のたびに画面がチラつくことを防止しましょう。 Sub example20() 'もしopenPathが配列ならば If IsArray(arrayPath) Then '画面の描画を停止する Application.ScreenUpdating = False 'Forループ(iが1から配列の要素数まで) Dim i As Integer For i = 1 To UBound(openPath) Next i '画面の描画を再開する Application.ScreenUpdating = True '処理が終わったことをMsgBoxで出力 MsgBox "全ブックからデータを抽出しました。" End If End Sub ～～～～～～～(途中省略)～～～～～～～～～～～～～～～～～～～～～～～(途中省略)～～～～～～～～～～～～～～～～解答