非表示にできる< 目次 >
学習パート
本記事のパートでは『Chapter 5 -実例/紹介』を説明します。
本パートでは、Chapter1～
これまでの学習パート
これまでの学習パートを未学習の方向けに 以下にリンクを貼っておきます。
Chapter 5 - VBA活用 実例の紹介
朝一ルーティンファイルを開く
Chapter-4深堀の最後の方で紹介したファイルオープン方法。
毎朝ルーティンで利用するファイル・Webサイトがあれば、
簡単に自動化が可能。
オープンしたいファイルのフルパスを変更してご利用ください。
＜ソースコード＞
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
'不要なコードは削除し、 'フルパスの修正を忘れないようにしてください。 Sub 朝イチファイルオープン() '指定のEXCELを開く Workbooks.Open "フルパス\▲▲.xlsx" '指定のEXCELマクロを開く Workbooks.Open "フルパス\▲▲.xlsx" '指定のメモ帳を開く CreateObject("Shell.Application").ShellExecute "フルパス\▲▲.txt" '指定のOfficeWordを開く CreateObject("Shell.Application").ShellExecute "フルパス\■■.docx" '指定のPDFを開く CreateObject("Shell.Application").ShellExecute "フルパス\〇〇.pdf" '指定のURLを開く CreateObject("Shell.Application").ShellExecute "https://nkmrdai.com/" '対象をすべて開いたら Msgboxで完了連絡 MsgBox "完了しました。" End Sub |
＜併せて読みたい＞
└理由：EXCELの開き方を忘れた人向け。
└理由：フルパスの意味が分からない人向け。
└理由：ボタン1つでマクロが実行され便利になる。
└理由：EXCEL以外の開き方の紹介
EXCEL↔EXCEL間の自動転記
継ぎ足しデータ転記方法の一例を紹介します。
◎やりたい事
Book2.xlsx の「2月実績」シートからBook1.xlsx の「月次累計」シートに
継ぎ足しでデータを追加したい。
継ぎ足しでデータを追加したい。
◎条件
Book1・Book2のEXCELファイルが未オープンの状態からスタート。
＜ソースコード＞
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Sub Book2データをBook1へ継ぎ足し() Dim wb1 As Object Dim wb2 As Object Dim wb1_Lastgyou As Long Dim wb2_Lastgyou As Long 'Book1.xlsxを開く Workbooks.Open "W:\Desktop\Book1.xlsx" 'Book1を 変数：wb1にセットする Set wb1 = ActiveWorkbook 'Book1の最終行を取得しておく wb1_Lastgyou = wb1.Worksheets("月次累計").Cells(Rows.Count, 1).End(xlUp).Row 'Book2.xlsxを開く Workbooks.Open "W:\Desktop\Book2.xlsx" 'Book2を 変数：wb2にセットする Set wb2 = ActiveWorkbook 'Book2の最終行を取得しておく wb2_Lastgyou = wb2.Worksheets("2月実績").Cells(Rows.Count, 1).End(xlUp).Row 'Book2の 範囲 A3：C列最終行[Range(Cells(3, 1), Cells(wb2_Lastgyou, 3))] をコピーし 'Book1のAセル最終行[Cells(wb1_Lastgyou + 1, 1)]に貼付。※最終行＋1しないと、最終行が上書きされ消えてしまうから注意 wb2.Worksheets("2月実績").Range(Cells(3, 1), Cells(wb2_Lastgyou, 3)).Copy wb1.Worksheets("月次累計").Cells(wb1_Lastgyou + 1, 1) End Sub |
◎実行結果の確認
Book2.xlsx のデータが Book1.xlsxの最後尾に継ぎ足しで登録されていることを確認できる。
＜併せて読みたい＞
└理由：セル・レンジの考え方を忘れた人向け
└理由： Copyメソッド忘れた人向け
└理由：変数の概念を忘れた人向け。
└理由：最終行の取得コードが納得できない人向け。
EXCELのデータ成形
データ成形の一例をコードと共に紹介します。
また、For Next と 最終行取得の併用時の注意点も説明します。
◎やりたい事
Book1.xlsx のB列-商品名で、価格変更があったものをD列に記載したい。
◎条件
Book1.xlsx は既に開いている状態からスタート
＜ソースコード＞
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Sub データを成形() Dim wb1 As Object Dim Lastgyou As Long Dim i As Long 'Book1.xlsxをシートと一緒に 変数：wb1にセットする Set wb1 = Workbooks("Book1.xlsx").Worksheets("月次累計") '最終行を取得しておく Lastgyou = wb1.Cells(Rows.Count, 1).End(xlUp).Row i = 0 For i = 0 To Lastgyou -3 '変数i は最大値10になるため、3を引いておく（※解説有） If wb1.Cells(3 + i, 2) = "コーラ" Then wb1.Cells(3 + i, 4) = 300 ElseIf wb1.Cells(3 + i, 2) = "ピザ" Then wb1.Cells(3 + i, 4) = 5000 ElseIf wb1.Cells(3 + i, 2) = "プロテイン" Then wb1.Cells(3 + i, 4) = 100 Else wb1.Cells(3 + i, 4) = "変更無" End If Next End Sub |
＜解説-For Nextと最終行取得の併用時の注意点-＞
For Nextの特性上 1ループするごとに i は 1ずつ増えるので
Cells(行 ＋ i , 列）にすることで、行を1つずつズラすことが出来る。
そして、For i = 0 to Lastgyou にすることで
Cells(行 ＋ i , 列）にすることで、行を1つずつズラすことが出来る。
そして、For i = 0 to Lastgyou にすることで
確実に最終行までのアプローチすることができる。
しかし、For Next 利用時の注意点でもあるが、
今回、最終行が 10行目だったので、
For i = 0 to Lastgyou は、実質 0 to 10となる。
つまり、範囲としては
i =0の時は Cells(3 + 0, 列） →3行目へアプローチ
i=10の時は Cells(3 + 10,列）→13行目へアプローチ
本来の最終行は10行目なのに対し、13行目までアプローチしてしまう。
つまり、Cells(3+i,列）の3の部分をLastgyouから減算させる必要がある。
∴ For i = 0 to Lastgyou -3 （実質 0 to 7となり、Cells(3+7,列）= 10行目までのアプローチとなる）
開始時点の行を Lastgyouから減算する必要があることを覚えておきましょう。
＜併せて読みたい＞
└理由：セル・レンジの考え方を忘れた人向け
└理由：変数の概念を忘れた人向け
└理由：最終行の取得コードが納得できない人向け
└理由： IF文を忘れた人向け
└理由： For Next を忘れた人向け
Chapter 5 - 参考書で必要なものは逆引きコード本＋利用しているもの紹介
逆引きコード本がおすすめする理由
逆引きコード本を使用すれば、
VBAでやりたい事を逆引き＝検索できるため、VBA作成時間が短縮できます。
もちろん、Webサイトで都度 調べても 全然問題ないのですが、
逆引きコード本の良いところは、
全コードが統一した形で表現されているため非常に分かりやすいということ。
そして、連動性のあるコードが前後に記載してあるということです。
Webサイトを横断しての調べものは、
また、コードの情報は各ページごとにショットでの記載が多いため、
別のコードで解決できるかもしれない･･･といった閃きが起こりにくい です。
VBAを早く上達する一番の近道は
逆引きコードを活用し たくさん実践することだと
今だからこそ深く思います。
＜逆引きコード本 がおすすめな理由＞
①VBA作成時間を短縮できる
②統一されたコード表現のため 自身のコーディングにも統一性がでる
③連動性のあるコードが前後に記載されているため 閃きを与えてくれる
おすすめする逆引きコード本
「Chapter 2-基本/動作」と「Chapter 3-基本/応用」を理解すれば、
逆引きコード本を理解できる土壌はそろっているので
初めから逆引きコード本を手に入れて問題ありません。
とは言っても、逆引きコード本は多数あり、当たり外れがありますので、
私自身が現在お世話になっている VBA逆引きコード本を紹介します。
とりあえず、これを持っておけば間違いありません。
※紹介するもの以外がハズレということではありません。
おすすめな理由は、とにかく見やすいです。
情報量もちょうど良く、読み味が非常に良いです。
ハッキリ言ってコスパが良いです。
定価の値段は、時間単価 2,000円/hの業務をたった1時間効率化すれば
すぐに元値は回収できますので 迷ったらこちらを手に取っていただけばと思います。
Chapter 5 - Next Stageの紹介
DOM（IEブラウザ連携）
DOMとは「Document Object Model」の略となり、
html・head・body・td・tr・p・
直接アプローチできる仕組みとなります。
ちまたでは、Webスクレイピングと呼ばれたりしていますが、
EXCELでも DOMを使用することができ、
InternetExploerブラウザ(IEブラウザ)
Webサイトからデータ抽出・データ入力・ボタン押下 などを
EXCEL側から自動で操作できます。
EXCELでWebスクレイピングができれば、
EXCEL記載の情報をWebサイトに自動投入できますので、
自動化の範囲が一段と広がります。
また、DOMの需要を調べてみると、
クラウドソーシング最大手「Crowd Work」では定常的にDOMによるWebスクレイピングの依頼があることから
一定の需要があるといえ 副業として稼ぐことも可能です。
私もEXCEL-
将来 本サイトでも 「1週間で分かるEXCEL DOM 」 という形で
ご説明が出来たらと考えています。
Chapter 5 - 終わりに
VBAを独学で学ぼうとすると、困る場面が多いです。
参考書では ボリュームが多すぎて最後まで読み切れないし、
それ使うの？っていうコードばかり紹介されてますし、
全てが重要のように扱われていて 辞書みたいな分厚い量を暗記しないと
VBAを組んではいけないような強迫観念に駆られたり。
一方、Webサイトを元に勉強しようとすると、
Tips形式(VBAの小ワザ)なものが多く、
既にVBAが出来る前提での紹介のされかたが殆どでした。
運よく『1から始めるVBA』的なサイトに巡り合っても
学習全体スケジュールが見えないゆえ、
ひたすら長いトンネルを走り続けさせられている感が出て挫折して
そういったことから、
VBAを 広く・浅く・
過去の自分のために本記事を作成するに至りました。
VBAで 実現したい事は、調べれば大体 何でも出てきます。
しかし調べたものを吸収できる土台がなければ 調べたところで実行できません。
本記事が そんな土台＝