【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる

オープンとクローズ

エクセルVBAを使ってバラバラの経費精算書のデータをデータベースに集約するシリーズです。

前回はこちらの記事でした。

【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
エクセルVBAを使ってバラバラの経費精算書のデータをデータベースに集約するマクロを作っています。今回はWithというステートメントを使って、オブジェクト名を省略してプログラムをスッキリ書く方法をお伝えします。

With文を使ってオブジェクトを省略する書き方について解説をしました。

さて、今回はエクセルVBAでWith文と組み合わせて他のワークブックを開く書き方についてお伝えしていきます。

では、行ってみましょう!

スポンサーリンク

前回のおさらいと課題

前回のプログラムはこちらです。

Sub 経費精算データ取り込み()

Dim ws As Worksheet
Set ws = Workbooks("201807経費精算書_経費_3001.xlsx").Worksheets(1)

Dim month As Date: month = ws.Range("G4").Value
Dim departmentName As String: departmentName = ws.Range("G6").Value
Dim staffId As Long: staffId = ws.Range("G8").Value
Dim staffName As String: staffName = ws.Range("G7").Value

With ws
    Dim i As Long: i = 12
    Dim di As Long: di = 2
    Do While ws.Cells(i, 1).Value <> ""
        wsData.Cells(di, 1).Value = month '1 対象月
        wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
        wsData.Cells(di, 3).Value = ""  '3 部署No
        wsData.Cells(di, 4).Value = departmentName '4 部署
        wsData.Cells(di, 5).Value = staffId '5 社員No
        wsData.Cells(di, 6).Value = staffName '6 氏名
        wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
        wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
        wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
        wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
        i = i + 1: di = di + 1
    Loop
End With

End Sub

太郎さんの経費精算書ファイル「201807経費精算書_経費_3001.xlsx」の経費データを「経費データ」シートwsDataにコピー&ペーストをしていくというものです。

ファイルを開いていないと実行できない

ただ、このプログラム…太郎さんのファイルを開いた状態からでないと、以下のようなエラーが出てしまいます。

VBAの実行時エラーエラー「インデックスが有効範囲にありません」

実行時エラー ‘9’: 「インデックスが有効範囲にありません」

…ちょっとよくわからないメッセージですね。

「デバッグ」ボタンをクリックすると、以下の部分でエラーが発生していることがわかります。

VBAの実行時エラーが発生している位置

答えを言っちゃいますと、このエラーは、Workbooksコレクションの中に「201807経費精算書_経費_3001.xlsx」が存在していないことに起因しています。

そりゃそうです。ファイルを開いてないんですから。

今回はこの点を解消すべく、ファイルが閉じていてもプロシージャを実行できるように修正をしていきます。

Openメソッドで他のワークブックを開く

ファイルが閉じているのであれば、開いちゃえばいいんですね。

WorkbooksコレクションのOpenメソッドを使えばOKです。

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

つまり、冒頭に以下のような処理を入れてあげましょう。

Dim wb As Workbook
Set wb = Workbooks.Open(ThisWorkbook.Path & "\data\" & "201807経費精算書_経費_3001.xlsx")
Dim ws As Worksheet
Set ws = wb.Worksheets(1)

Openメソッドの使い方についてはこちらの記事をご参考ください。

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

ちなみに、太郎さんの経費精算書は、現在のマクロブックと同じ階層の「data」というフォルダの中にあるとします。

その際の、開くブックのフルパスの指定の仕方などは、以下の記事が参考になります。

【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
初心者向けエクセルVBAのシリーズとして請求書を作成するマクロの作り方をお伝えしています。今回はエクセルVBAで現在マクロを書いているブックのフォルダのパスを取得する方法についてお伝えします。

ファイルを開いてデータを取り込むプロシージャ

以上をまとめたプロシージャがコチラです。

Sub 経費精算データ取り込み()

Dim wb As Workbook
Set wb = Workbooks.Open(ThisWorkbook.Path & "\data\" & "201807経費精算書_経費_3001.xlsx")
Dim ws As Worksheet
Set ws = wb.Worksheets(1)

Dim month As Date: month = ws.Range("G4").Value
Dim departmentName As String: departmentName = ws.Range("G6").Value
Dim staffId As Long: staffId = ws.Range("G8").Value
Dim staffName As String: staffName = ws.Range("G7").Value

With ws
    Dim i As Long: i = 12
    Dim di As Long: di = 2
    Do While ws.Cells(i, 1).Value <> ""
        wsData.Cells(di, 1).Value = month '1 対象月
        wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
        wsData.Cells(di, 3).Value = ""  '3 部署No
        wsData.Cells(di, 4).Value = departmentName '4 部署
        wsData.Cells(di, 5).Value = staffId '5 社員No
        wsData.Cells(di, 6).Value = staffName '6 氏名
        wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
        wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
        wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
        wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
        i = i + 1: di = di + 1
    Loop
End With

wb.Close

End Sub

開いたら閉じるべきなので、最後にCloseメソッドも入れています。

ワークブックを開いた上でWith

さて、以上で特に問題はないのですが、今回は一歩進んだOpenの仕方をしていきたいと思います。

以下のように、OpenメソッドとWith文と組み合わせるのです。

With Workbooks.Open(ThisWorkbook.Path & "\data\" & "201807経費精算書_経費_3001.xlsx")
    With .Worksheets(1)
        '処理
    End With
    .Close
End With

まず、1つ目のWith文により、開いたWorkbookオブジェクトを省略することができます。

少し難しい話ですが、Openメソッドの戻り値として開いたWorkbookオブジェクトを取得できますので、それをWith文のオブジェクトとして指定するわけですね。

そして、2つ目の「With .Worksheets(1)」で、その開いたワークブックの1枚目のWorksheetオブジェクトを省略できます。

つまり、2つ目以降のWithブロックでオブジェクトを省略した場合は、開いたワークブックの1枚目のシートを対象にした処理ということになります。

百聞は一見にしかず、今回のプログラムをこの書き方で記述してみますと、以下のようになります。

Sub 経費精算データ取り込み()

With Workbooks.Open(ThisWorkbook.Path & "\data\" & "201807経費精算書_経費_3001.xlsx")
    With .Worksheets(1)
    
        Dim month As Date: month = .Range("G4").Value
        Dim departmentName As String: departmentName = .Range("G6").Value
        Dim staffId As Long: staffId = .Range("G8").Value
        Dim staffName As String: staffName = .Range("G7").Value
        
        Dim i As Long: i = 12
        Dim di As Long: di = 2
        Do While .Cells(i, 1).Value <> ""
            wsData.Cells(di, 1).Value = month '1 対象月
            wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
            wsData.Cells(di, 3).Value = ""  '3 部署No
            wsData.Cells(di, 4).Value = departmentName '4 部署
            wsData.Cells(di, 5).Value = staffId '5 社員No
            wsData.Cells(di, 6).Value = staffName '6 氏名
            wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
            wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
            wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
            wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
            i = i + 1: di = di + 1
        Loop
    End With
    .Close
End With

End Sub

かなりスッキリしていますよね。

開いたワークブックのシートをすぐに使いたいときにはWithとOpenを組み合わせると非常に簡潔にプログラムを書けるようになります。

ちなみに、お気づきかも知れませんが、ワークブックをオープンする書き方が「Workbooks.Open ファイル名」ではなくて「Workbooks.Open(ファイルのフルパス)」となっていることに注意してください。

メソッドの戻り値を使う場合はカッコが必要なのです。

まとめ

以上、エクセルVBAでWithをしながら他のワークブックを開く方法についてお伝えしました。

ワークブックを開くときはWithで開くことにより以降のオブジェクト名の表記を省略しスッキリ記述することができますし、開いたワークブック用のオブジェクト変数などを用意する必要もなくなります。

では、次回はVBAでフォルダを操作するためにFileSystemオブジェクトを使います。

【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
エクセルVBAでバラバラの経費精算書をデータベースに集めるマクロの作り方をお伝えしています。今回はエクセルVBAでフォルダやファイルを操作するFileSystemオブジェクトとその使い方についてです。

どうぞお楽しみに!

連載目次:エクセルVBAで経費データをデータベースに集約する

請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。
  1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
  2. 【エクセルVBA入門】Do While~Loop文で条件を満たす間繰り返し
  3. 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの2つのポイント
  4. 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
  5. 【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる
  6. 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
  7. 【エクセルVBA入門】For Each~Next文でフォルダ内のブック全てを開く方法
  8. 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
  9. 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
  10. 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
  11. 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
  12. 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
  13. 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
  14. 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
  15. 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する
タイトルとURLをコピーしました