Excel VBAでブックやシートを指定する方法|ファイルを開くコードも解説

Level5 VBA・マクロ

Level5 VBA|クエスト8

勇者たんたん
勇者たんたん

別のブックにあるセルを操作したい!

Range("A1")だけでは、どのブックにある、どのワークシートのA1セルを操作するのかが明確ではありません。

複数のブックやワークシートを扱う場合は、操作対象をコード内で指定する必要があります。

ブック名、ワークシート名、セル番地を順番に記述すると、操作するセルを明確にできます。

Workbooks("売上管理.xlsm").Worksheets("売上表").Range("A1").Value = "完了"

また、Excelで開かれていないファイルは、Workbooks.Openを使って開けます。

この記事では、ブックとワークシートを指定する方法、ThisWorkbookとActiveWorkbookの違い、フルパスを使ってファイルを開閉する方法を解説します。

今回のゴール

操作するブックとワークシートを明確に指定し、保存されているExcelファイルを安全に開閉できるようになりましょう。

ブックやワークシートを指定する理由

次のコードでは、現在操作対象になっているワークシートのA1セルへ「完了」と入力します。

Range("A1").Value = "完了"

1つのブックとワークシートだけを扱う場合は、このコードでも意図した結果になることがあります。

しかし、複数のブックを開いている場合や、ブック内に複数のワークシートがある場合は、意図しないセルを操作する可能性があります。

操作対象を明確にするには、ブック名とワークシート名を含めて指定します。

Workbooks("売上管理.xlsm").Worksheets("売上表").Range("A1").Value = "完了"

このコードは、左から次のように読めます。

  1. Workbooks("売上管理.xlsm"):「売上管理.xlsm」というブックの
  2. Worksheets("売上表"):「売上表」というワークシートにある
  3. Range("A1"):A1セルの
  4. Value = "完了":値を「完了」にする

VBAでは、ブック、ワークシート、セルの順番で、大きな対象から小さな対象へ絞り込めます。

ブック名やシート名を指定しよう

ブック名やシート名を指定していなくても、意図した通りのセルを扱えるかどうかはアクティブシートによります。

たまたま意図したブック・シートがアクティブになっていれば、目的通りに指示できます。

Workbooksで開いているブックを指定する

現在Excelで開いているブックは、Workbooksの後ろにブック名を記述して指定します。

Workbooks("売上管理.xlsm")

このコードでは、開いているブックの中から「売上管理.xlsm」を指定しています。

ブック名を指定するときは、ファイルの拡張子も含めます。

Excelブックでは、主に次のような拡張子が使用されます。

  • .xlsx:通常のExcelブック
  • .xlsm:マクロを保存できるExcelブック

拡張子についてはLevel1の記事で解説していますので、ご覧ください。

このコードで指定できるのは、基本的に現在Excelで開かれているブックです。

開いているブックだけが対象

「転記先.xlsx」が保存されていても、まだExcelで開かれていなければ、このコードだけでは操作できません。

閉じているファイルを操作する場合は、Workbooks.Openで開きます。

Worksheetsでワークシートを指定する

ワークシートは、Worksheetsの後ろにシート名を記述して指定します。

Worksheets("売上表")

このコードでは、「売上表」という名前のワークシートを指定しています。

ワークシートが所属するブックまで指定する場合は、次のように記述します。

Workbooks("売上管理.xlsm").Worksheets("売上表")

複数のブックに「売上表」という同じ名前のワークシートがあっても、どのブックのワークシートを操作するのか判断できます。

セルまで指定する場合は、後ろにRangeを続けます。

Workbooks("売上管理.xlsm").Worksheets("売上表").Range("A1")
ブック名やシート名は正確に

ブック名やシート名が実際の名前と異なると、対象を見つけられずエラーになります。全角・半角、スペース、拡張子まで確認しましょう。

ThisWorkbookとActiveWorkbookの違い

VBAでは、ブック名を直接記述するほかに、ThisWorkbookやActiveWorkbookを使ってブックを指定できます。

指定方法表すブック
ThisWorkbookVBAコードが保存されているブック
ActiveWorkbook現在操作対象になっているブック

ただし、この2つが表すブックは同じとは限りません。

ThisWorkbookはVBAコードが保存されているブック

ThisWorkbookは、現在実行しているVBAコードが保存されているブックを表します。

たとえば、マクロが保存されているブック内の「設定」シートを指定する場合は、次のように記述します。

ThisWorkbook.Worksheets("設定")

A1セルの値を取得する場合は、次のとおりです。

ThisWorkbook.Worksheets("設定").Range("A1").Value

別のブックが画面に表示されていても、ThisWorkbookが表すのはVBAコードが保存されているブックです。

ActiveWorkbookは現在操作対象になっているブック

ActiveWorkbookは、その時点で画面上の操作対象になっているブックを表します。

現在操作対象になっているブックの「売上表」シートを指定する場合は、次のように記述します。

ActiveWorkbook.Worksheets("売上表")

別のブックを開いたり、画面上でブックを切り替えたりすると、ActiveWorkbookが表すブックも変わる場合があります。

ブックとワークシートを変数へ代入する

ブック名やワークシート名を繰り返し記述すると、コードが長くなります。

そこで、ブックやワークシートを変数へ代入して使用します。

ブックを変数へ代入する

Workbook型の変数を用意し、開いているブックを代入します。

Dim 対象ブック As Workbook

Set 対象ブック = Workbooks("売上管理.xlsm")

このコードでは、「売上管理.xlsm」を対象ブックという変数へ代入しています。

代入後は、ブック名の代わりに変数名を使用できます。

対象ブック.Worksheets("売上表").Range("A1").Value = "完了"

ワークシートを変数へ代入する

Worksheet型の変数を用意し、ワークシートを代入します。

Dim 対象シート As Worksheet

Set 対象シート = Worksheets("売上表")

代入後は、次のようにセルを指定できます。

対象シート.Range("A1").Value = "完了"

ブックとワークシートの両方を変数へ代入する

より安全に対象を指定するには、ブックとワークシートの両方を変数へ代入します。

Dim 対象ブック As Workbook
Dim 対象シート As Worksheet

Set 対象ブック = Workbooks("売上管理.xlsm")
Set 対象シート = 対象ブック.Worksheets("売上表")

その後は、次のように短く記述できます。

対象シート.Range("A1").Value = "完了"

対象シートがどのブックに所属しているのかは、Setで代入している部分から確認できます。

Workbooks.Openでファイルを開く

Excelで開かれていないファイルは、Workbooks.Openで開けます。

Workbooks.Open "C:\売上管理\転記先.xlsx"

このコードは、次のように読めます。

  • Workbooks.Open:Excelファイルを開く
  • "C:\売上管理\転記先.xlsx":開くファイルの場所

Workbooks.Openの後ろには、開くファイルのフルパスを記述します。

フルパスとは、ドライブ、フォルダー、ファイル名を組み合わせたものです。
フルパスの詳細はこちらをご覧ください。

Workbooks.Openで開いたブックは、Workbook型の変数へ代入できます。

Dim 対象ブック As Workbook

Set 対象ブック = Workbooks.Open("C:\売上管理\転記先.xlsx")

このコードでは、次の2つの処理を行っています。

  1. 指定したフルパスにあるブックを開く
  2. 開いたブックを対象ブックへ代入する

開いた後は、変数名を使ってブック内のワークシートを指定できます。

対象ブック.Worksheets("売上表").Range("A1").Value = "完了"

Saveでブックを上書き保存する

ブックを上書き保存するときは、Saveメソッドを使用します。

対象ブック.Save

このコードでは、対象ブックへ加えた変更を、現在のファイルへ上書き保存します。

マクロが保存されているブックを上書き保存する場合は、次のように記述できます。

ThisWorkbook.Save

Closeでブックを閉じる

ブックを閉じるときは、Closeメソッドを使用します。

対象ブック.Close

変更内容を確実に保存してから閉じる場合は、SaveとCloseを分けて記述すると処理を読み取りやすくなります。

対象ブック.Save
対象ブック.Close
  • 1行目:対象ブックを保存する
  • 2行目:対象ブックを閉じる
保存せずにブックを閉じる

次のコードでは、変更内容を保存せずに閉じます。

対象ブック.Close SaveChanges:=False

変更を意図的に破棄する場合を除き、安易に使用しないでください。

ブックを開いて保存し、閉じる完成例

ここまでに学んだ内容を組み合わせてみましょう。

Sub 対象ブックを操作する()

    Dim 対象ブック As Workbook
    Dim 対象シート As Worksheet

    Set 対象ブック = Workbooks.Open("C:\売上管理\転記先.xlsx")
    Set 対象シート = 対象ブック.Worksheets("売上表")

    対象シート.Range("A1").Value = "完了"

    対象ブック.Save
    対象ブック.Close

End Sub

このマクロでは、次の順番で処理します。

  1. ブック用とワークシート用の変数を用意する
  2. 指定したフルパスのブックを開く
  3. 開いたブックを対象ブックへ代入する
  4. ブック内の「売上表」を対象シートへ代入する
  5. 「売上表」のA1セルへ「完了」と入力する
  6. 対象ブックを上書き保存する
  7. 対象ブックを閉じる

対象ブック.Closeを実行した後は、対象ブックが閉じられています。

そのため、ブック内のセルを操作する処理は、Closeより前に記述してください。

ブックを閉じた後は操作しない

必要なセル操作と保存をすべて終えてから、最後にブックを閉じましょう。

まとめ|操作するブックとワークシートを明確にしよう

複数のブックやワークシートを扱う場合は、操作対象をコード内で明確にしましょう。

このクエストで覚えること
  • Workbooks("ブック名")は、開いているブックを指定する
  • Worksheets("シート名")でワークシートを指定する
  • ThisWorkbookはVBAコードが保存されているブック
  • ActiveWorkbookは現在操作対象になっているブック
  • 閉じているファイルはWorkbooks.Openで開く
  • 開いたブックはWorkbook型の変数へ代入できる
  • Saveで上書き保存する
  • Closeでブックを閉じる
  • フルパスではドライブ、フォルダー、ファイル名、拡張子を確認する
  • 保存とセル操作を終えてからブックを閉じる

次のクエストへ

今回は、操作するブックとワークシートを変数へ代入し、保存されているExcelファイルを開閉する方法を学びました。

次のクエストでは、転記元と転記先のブックをそれぞれ指定し、別のブックへ値を転記する方法を確認します。

ブック名、ワークシート名、セル番地を区別し、目的の場所へ安全にデータを転記できるようになりましょう。

Level5の学習順序を確認したい方は、Level5攻略フローをご覧ください。

コメント

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