【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする


book-sheet

photo credit: GeoMatthis encyclopedia via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

初心者向けエクセルVBAのシリーズとして、請求書を作成するマクロの作り方をお伝えしています。

前回の記事はこちら。

【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
初心者向けエクセルVBAのシリーズとして請求書を作成するマクロの作り方をお伝えしています。今回はエクセル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 取引先ごとのシートをコピー」の、ブック版という感じですね。

【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
初心者向けエクセルVBAのシリーズとして請求データから請求書を作成するマクロの作り方をお伝えしています。 今回はWorksheetオブジェクトを変数にセットして取り扱う方法についてお伝えします。

ワークブックを開いて変数にセット

まず、13,14行目のワークブックを開く部分です。

開くだけならWorkbooksコレクションのOpenメソッドを使えば良さそうですが、それをオブジェクト変数wbにセットしたいわけですね。

ActiveWorkbookをセットする

一つの方法としては、開いたブックは直後はActiveWorkbookですから、それをすぐさまオブジェクト変数に格納する方法です。

Workbooks.Open ThisWorkbook.Path & "\請求書ひな形.xlsx"
Set wb = ActiveWorkbook

これはこれでOKです。

Openメソッドで開いたブックをそのままセットする

ActiveWorkbook嫌い(?)の人のために、別の方法もあります。

Openメソッドで開いたブックをそのままセットする方法です。

Openメソッドは「戻り値」としてWorkbookオブジェクトを返しますので、以下のようにすれば、その戻り値をそのままオブジェクト変数にセットできちゃうのです。

Set オブジェクト変数 = Workbooks.Open(ファイルのフルパス)

これを使うと、以下のように一行で収めることができます。

Set wb = Workbooks.Open(ThisWorkbook.Path & "\請求書ひな形.xlsx")

Openメソッドの引数が括弧になっている理由

ところで、引数が括弧で囲われていますよね。

以前紹介したのは

Workbooks.Open 開くブックのフルパス

このように引数の指定には括弧はなしでした。

【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
初心者向けエクセルVBAのシリーズとして請求書を作成するマクロの作り方をお伝えしています。今回は、エクセルVBAで新たなブックを開く方法です。コレクションやWorkbooksプロパティについても解説しますよ。

これはちょっとややこしいポイントなのですが、VBAの場合、メソッドの使用時に戻り値を使用する場合は、引数を括弧内に指定する必要があるのです。

これは、そういうものということで念頭に置いておいてください。

開いたブックのワークシートを取得する

さて、今度は開いたブックのワークシートを取得したいと思います。

プログラムの15行目のところです。

ActiveSheetをセットする

開いた直後のブックのシートが一つだけであれば、そのシートが確実にActiveSheetになりますから、シンプルに

Set ws = ActiveSheet

としても取得できますが、Active~嫌いの方のために、今回は別の方法も覚えてみましょう。

ブックのWorksheetsコレクションを取得する

ブックに含まれるワークシートは複数ありますよね。

したがって、まずはWorkbookオブジェクトに属するワークシートをWorksheetsコレクションとして取得する、というのがそのスタート地点になります。

その場合、Worksheetsプロパティを使って、こう書きます。

Workbookオブジェクト.Worksheets

Worksheetsコレクションからワークシートを取得する

続いて、Worksheetsコレクションから何らかの方法で、特定のワークシートを取得します。

その方法は二つあります。

インデックスで指定する方法

まず一つ目は、インデックスを使う方法です。

ワークシートはそのリアルな並び順で1番左が「1」それ以降、1ずつ順番に整数が割り振られていて、それをインデックスと言います。

そのインデックス(つまり並び順)がわかれば、以下の書き方でワークシートを取得することができます。

Worksheetsコレクション(インデックス)

今回の例では、以下のように1番目のワークシートを変数にセットすることができます。

Set ws = wb.Worksheets(1)

シート名で指定する方法

もう一つは、シート名を使う方法です。

こちらは単純に、シート名を文字列として以下のように指定します。

Worksheetsコレクション(シート名)

例えば、シート名は「請求書ひな形」であれば、以下のようになります。

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のシリーズとして、請求書を作成するマクロの作り方についてお伝えしています。今回は、ワークブックをSaveAsメソッドで別名で保存し、Closeメソッドで閉じる方法をお伝えします。

どうぞお楽しみに!

連載目次:データ一覧から請求書を自動で作る

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
  3. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  4. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  5. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  6. 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
  7. 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
  8. 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
  9. 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
  10. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  11. 【初心者向けエクセルVBA】For~Next文でセル範囲を一行ずつ移動させる
  12. 【初心者向けエクセルVBA】セル範囲のクリア~ClearContentsメソッドとClearメソッド
  13. 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
  14. 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
  15. 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
  16. 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
  17. 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
  18. 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
  19. 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
  20. 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
  21. 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
  22. 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る

コメント

  1. マクロ太郎 より:

    初めまして。
    いつも参考にさせていただいております。

    以下について質問なのですが、
    fileName = ThisWorkbook.Path & “\” & _
    Format(Date, “yyyymm”) & “請求書_” & client & “.xlsx”

    これが別名保存したときに、ファイル名として
    「201803請求書_株式会社ホゲホゲ.xlsx」になる仕組みがわかりません。

    よろしくお願い致します。

タイトルとURLをコピーしました