photo credit: GeoMatthis encyclopedia via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
初心者向けエクセルVBAのシリーズとして、請求書を作成するマクロの作り方をお伝えしています。
前回の記事はこちら。
現在マクロを書いているブックのパスを取得する方法をお伝えしました。
これで同じフォルダにあるブックを開くことができるようになるはずですが、開いた後にそのブックまたはシートを取得し操作する必要ありますよね。
ということで、今回はエクセルVBAで開いたブックとそのワークシートを取得する方法についてお伝えしていきます。
なお、こちらの記事は以下のYouTube動画と連動をしております。合わせてご覧くださいませ。
では、行ってみましょう！
今回やりたいこと：ひな形ブックをコピーして取引先ごとのブックを作成したい
前回までのテクニックを使って、以下のようなプログラムを作ってみました。
作ってみましたとは言いつつも、ほとんど未完成ですけどね。
Sub 取引先ごとのブックを作成()
Dim rowsClient As Long
rowsClient = wsClient.Cells(Rows.Count, 1).End(xlUp).Row '取引先マスタの最終行数
Dim n As Long
For n = 2 To rowsClient
Dim client As String
client = wsClient.Cells(n, 1).Value
Dim wb As Workbook, ws As Worksheet
'ワークブックを開く
'開いたワークブックをwbにセット
'wbの一番目のシートをwsにセット
'ファイル名を生成
Dim fileName As String
fileName = ThisWorkbook.Path & "\" & client & ".xlsx"
'ブックをfileNameと名前をつけて保存
'ブックを閉じる
Next n
End Sub
なんとなくやりたいことは伝わってるかと思いますが、説明しますね。
まず、このマクロと同じフォルダ内に「請求書ひな形.xlsx」というファイルが保存されています。
そのファイルを「取引先マスタ」シート（wsClient ）にリストされている取引先の分だけ、コピーして「取引先名.xlsx」として保存するというものです。
以下記事の「Sub 取引先ごとのシートをコピー」の、ブック版という感じですね。
ワークブックを開いて変数にセット
まず、13,14行目のワークブックを開く部分です。
開くだけならWorkbooksコレクションのOpenメソッドを使えば良さそうですが、それをオブジェクト変数wbにセットしたいわけですね。
ActiveWorkbookをセットする
一つの方法としては、開いたブックは直後はActiveWorkbookですから、それをすぐさまオブジェクト変数に格納する方法です。
Workbooks.Open ThisWorkbook.Path & "\請求書ひな形.xlsx"
Set wb = ActiveWorkbook
これはこれでOKです。
Openメソッドで開いたブックをそのままセットする
ActiveWorkbook嫌い（？）の人のために、別の方法もあります。
Openメソッドで開いたブックをそのままセットする方法です。
Openメソッドは「戻り値」としてWorkbookオブジェクトを返しますので、以下のようにすれば、その戻り値をそのままオブジェクト変数にセットできちゃうのです。
これを使うと、以下のように一行で収めることができます。
Set wb = Workbooks.Open(ThisWorkbook.Path & "\請求書ひな形.xlsx")
Openメソッドの引数が括弧になっている理由
ところで、引数が括弧で囲われていますよね。
以前紹介したのは
このように引数の指定には括弧はなしでした。
これはちょっとややこしいポイントなのですが、VBAの場合、メソッドの使用時に戻り値を使用する場合は、引数を括弧内に指定する必要があるのです。
これは、そういうものということで念頭に置いておいてください。
開いたブックのワークシートを取得する
さて、今度は開いたブックのワークシートを取得したいと思います。
プログラムの15行目のところです。
ActiveSheetをセットする
開いた直後のブックのシートが一つだけであれば、そのシートが確実にActiveSheetになりますから、シンプルに
Set ws = ActiveSheet
としても取得できますが、Active~嫌いの方のために、今回は別の方法も覚えてみましょう。
ブックのWorksheetsコレクションを取得する
ブックに含まれるワークシートは複数ありますよね。
したがって、まずはWorkbookオブジェクトに属するワークシートをWorksheetsコレクションとして取得する、というのがそのスタート地点になります。
その場合、Worksheetsプロパティを使って、こう書きます。
Worksheetsコレクションからワークシートを取得する
続いて、Worksheetsコレクションから何らかの方法で、特定のワークシートを取得します。
その方法は二つあります。
インデックスで指定する方法
まず一つ目は、インデックスを使う方法です。
ワークシートはそのリアルな並び順で１番左が「１」それ以降、１ずつ順番に整数が割り振られていて、それをインデックスと言います。
そのインデックス（つまり並び順）がわかれば、以下の書き方でワークシートを取得することができます。
今回の例では、以下のように１番目のワークシートを変数にセットすることができます。
Set ws = wb.Worksheets(1)
シート名で指定する方法
もう一つは、シート名を使う方法です。
こちらは単純に、シート名を文字列として以下のように指定します。
例えば、シート名は「請求書ひな形」であれば、以下のようになります。
Set ws = wb.Worksheets("請求書ひな形")
まとめ
以上、エクセルVBAで開いたワークブックとそのワークシートをオブジェクト変数にセットする方法についてお伝えしました。
ブックの取得、ワークシートの取得ともに非常に重要なので、よく理解しておいてもらえればと思います。
まとめのコードはこちらです。
Sub 取引先ごとのブックを作成()
Dim rowsClient As Long
rowsClient = wsClient.Cells(Rows.Count, 1).End(xlUp).Row '取引先マスタの最終行数
Dim n As Long
For n = 2 To rowsClient
Dim client As String
client = wsClient.Cells(n, 1).Value
Dim wb As Workbook, ws As Worksheet
Set wb = Workbooks.Open(ThisWorkbook.Path & "\請求書ひな形.xlsx")
Debug.Print wb.Name
Set ws = wb.Worksheets(1)
Debug.Print ws.Name
'ファイル名を生成
Dim fileName As String
fileName = ThisWorkbook.Path & "\" & _
Format(Date, "yyyymm") & "請求書_" & client & ".xlsx"
'ブックをfileNameと名前をつけて保存
'ブックを閉じる
Next n
End Sub
デバッグ出力でブックやワークシート名を出力する命令も入れていますので、正しく動いてそうか確認してください。
では、次回ですが、続きでブックを名前をつけて保存して閉じる方法をお伝えします。
どうぞお楽しみに！
連載目次：データ一覧から請求書を自動で作る
お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。- 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
- 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
- 【初心者向けエクセルVBA】For～Next文で簡潔にプログラムを書く
- 【初心者向けエクセルVBA】行の数をカウントする＆不要な行を隠す
- 【初心者向けエクセルVBA】文字列の連結＆Format関数での書式変更
- 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
- 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
- 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
- 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
- 【初心者向けエクセルVBA】If～Thenを使った条件分岐の超入門
- 【初心者向けエクセルVBA】For～Next文でセル範囲を一行ずつ移動させる
- 【初心者向けエクセルVBA】セル範囲のクリア～ClearContentsメソッドとClearメソッド
- 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
- 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
- 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
- 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
- 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
- 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
- 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
- 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
- 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
- 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る
コメント
初めまして。
いつも参考にさせていただいております。
以下について質問なのですが、
fileName = ThisWorkbook.Path & “\” & _
Format(Date, “yyyymm”) & “請求書_” & client & “.xlsx”
これが別名保存したときに、ファイル名として
「201803請求書_株式会社ホゲホゲ.xlsx」になる仕組みがわかりません。
よろしくお願い致します。
マクロ太郎さん
Format関数のところがわからないという感じですかね。
それであれば、以下の記事が参考になると思いますので、ご覧いただければと思います。
https://tonari-it.com/string-format/