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

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

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

【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く

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

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

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

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

前回のおさらいと課題
1. ファイルを開いていないと実行できない
Openメソッドで他のワークブックを開く
1. ファイルを開いてデータを取り込むプロシージャ
ワークブックを開いた上でWith
まとめ
連載目次：エクセルVBAで経費データをデータベースに集約する

前回のおさらいと課題

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

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にコピー＆ペーストをしていくというものです。

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

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

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

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

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

答えを言っちゃいますと、このエラーは、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(ファイルのフルパス)」となっていることに注意してください。

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