VBAで別のブックを開く／閉じる
VBAから別のExcelブックを開いたり閉じたりする方法について書きます。
１．Excelブックを開く(Workbooks.Open)
２．Excelブックを閉じる(Workbook.Close)
３．テストしてみる
１．Excelブックを開く(Workbooks.Open)
VBAからブックを開く為には、Workbooks.Openメソッドを使用します。[サンプルソース]
Workbooks.Open fileName:="C:\work\Book1.xlsx" |
単純にファイル名のみ指定して開くことができます。
任意のファイル名を指定します。
Function化してみたものが、以下になります。
[サンプルソース]
Public Function bookOpen(ByVal filePass As String, ByVal bookName As String, Optional ByVal readOnly = 1, Optional ByVal updateLinks = 0) As Workbook '------------------------------------------------------------------------------------------------------ ' bookOpen ' 指定したExcelBookを開く ' ' 引数１：filePass ･･･ ファイルパス ' 引数２：bookName ･･･ ExcelBook名 ' 引数３：updateLinks ･･･ Book内リンクの更新有無(デフォルト：0(しない)) ' 引数４：readOnly ･･･ 読み取り専用か否か(デフォルト：1(読み取り専用)) ' ' 戻り：Workbookオブジェクト ' ' 使用注：引数に指定するパスの整合性確認等は参照元で行ってください。 '------------------------------------------------------------------------------------------------------ Const cnsYEN = "\" Set bookOpen = Workbooks.Open(fileName:=filePass + cnsYEN + bookName, updateLinks:=updateLinks, readOnly:=readOnly) End Function |
オプションで、リンクの更新有無と、読み取り専用か否かを指定できます。
標準モジュールに記述して、呼び出して使用してください。
呼び出し例はこちら
２．Excelブックを閉じる(Workbook.Close)
VBAからブックを閉じる為には、Workbook.Closeメソッドを使用します。[サンプルソース]
Workbooks("Book1.xlsx").Close saveChanges:=0 |
「saveChanges」は保存して閉じるか否かの指定です。
開いた状態のWorkbookオブジェクト（サンプルではBook1)の、Closeメソッドを呼び出します。
Book1を開いていなければ、エラーになります。
Function化してみたものが、以下になります。
[サンプルソース]
Public Sub bookClose(ByVal objWBK As Workbook, Optional ByVal saveChanges = 0, Optional ByVal fileName = "") '------------------------------------------------------------------------------------------------------ ' bookClose ' 指定したExcelBookを閉じる ' ' 引数１：objWBK ･･･ WorkBookオブジェクト ' 引数２：saveChanges ･･･ 保存して閉じるか否か(デフォルト：0(保存しない)) ' 引数３：fileName ･･･ 保存するファイル名（保存する場合に指定) ' ' 使用注：開いた状態のWorkBookオブジェクトを指定してください。 '------------------------------------------------------------------------------------------------------ '保存して閉じる If saveChanges & fileName <> "" Then objWBK.Close saveChanges:=saveChanges, fileName:=fileName '保存しないで閉じる Else objWBK.Close saveChanges:=saveChanges End If End Sub |
保存して閉じる場合は、ファイル名を指定するようにしています。
標準モジュールに記述して、呼び出して使用してください。
呼び出し例はこちら
ちなみに、開いている全てのブックを閉じる場合は、Workbooks.Closeメソッドを使用します。
[サンプルソース]
Workbooks.Close |
３．テストしてみる
項番１と項番２の呼び出しサンプルです。[サンプルソース]
Sub testBookOpenClose() Dim objWBK As Workbook Const filePass = "C:\work\VBA" Const bookName = "Book1.xlsx" 'ブックを開く Set objWBK = bookOpen(filePass, bookName) 'ブックを閉じる Call bookClose(objWBK) End Sub |
以上です。
VBAでの範囲指定（Rangeオブジェクトの利用)
VBAでの範囲指定について書きます。
VBAで
指定したセルの値を取得・設定する
指定した範囲のセルを繰り返し操作する
といった場合に範囲指定が必要になります。
VBAでセル範囲を指定するためには、Rangeオブジェクトを利用します。
VBAでセル範囲を指定する（Rangeオブジェクト）
１．A1形式での指定(単一セル)
２．A1形式での指定(複数セル)
３．R1C1形式での指定(複数セル)
４．現在選択しているセル範囲の指定
１．A1形式での指定(単一セル)
A1形式で単一のセルを指定する場合は、以下の通りにします。[サンプルソース]
Sub rangeTest1() MsgBox Range("A1").Value End Sub |
（rangeTest1()実行結果）
２．A1形式での指定(複数セル)
A1形式で複数のセル範囲を指定する場合は、以下の通りにします。[サンプルソース]
Sub rangeTest2() Dim rObj As Range Set rObj = Range("B2:C3") MsgBox rObj.Cells(1, 1).Value End Sub |
（rangeTest2()実行結果）
３．R1C1形式での指定(複数セル)
R1C1形式で複数のセル範囲を指定する場合は、以下の通りにします。マクロを組む場合は、この方法で指定することが最も多いのではないでしょうか。
[サンプルソース]
Sub rangeTest3() Dim rObj As Range Set rObj = Range(Cells(2, 2), Cells(3, 3)) MsgBox rObj.Cells(1, 1).Value End Sub |
（rangeTest3()実行結果）
４．現在選択しているセル範囲の指定
現在選択しているセル範囲を取得する場合は、以下の通りに記述します。[サンプルソース]
Sub rangeTest4() MsgBox ActiveCell.Cells(1, 1).Value End Sub |
同じ範囲を選択した状態で実行してみます。
（rangeTest4()実行結果）
以上です。
Excelで指定行へジャンプ（スクロール）する
Excelで指定行へジャンプ（スクロール）する方法について書きます。
（完成イメージ）
上記の様なイメージで希望したセル（のある行）までジャンプしたいのですが、
ということで、VBAで実現します。
(2014.7.26 訂正)
※Excel標準機能でジャンプする方法もありました。 こちら
VBAでワークシート内の指定行へジャンプする（Window.ScrollRow）
VBAのWindow.ScrollRowプロパティにより、指定行へ画面をスクロールさせることができます。
これを利用して、マクロを作成します。
以下、サンプルソースです。
標準モジュールに記述してください。
[サンプルソース]
Public Sub jumpToTargetRow() '------------------------------------------------------------------------------ ' jumpToTargetRow ' 対象行までジャンプする ' '------------------------------------------------------------------------------ '対象行番号 Dim tRow As Long 'エラー時は処理終了 On Error GoTo endSub tRow = CLng(Application.InputBox("指定行へジャンプします")) If tRow > 0 Then '画面スクロール ActiveWindow.ScrollRow = tRow 'スクロール先のセルを選択（列番号は最初に選択していた範囲の1列目とする） Cells(tRow, Selection(1).Column).Select End If endSub: End Sub |
InputBoxで指定行を入力させ、スクロールします。
スクロール後、スクロール先のセルを選択します。
実行すると、最初のイメージ通りの結果になります。
私は、Ctrl+jキーに上記のマクロを登録し、使用しています。
ショートカットの登録方法は、以下の記事をご参照ください。
Excel起動時にVBAマクロへショートカットを自動設定する - Sumihirablog.
2014.7.26 訂正
Excel標準機能で指定のセルへジャンプする方法
標準機能でもできました。[方法]
Excelを開いてF5キー押下。
以上です。
Excel起動時にVBAマクロへショートカットを自動設定する
VBAマクロへショートカットを割り当てる方法について書きます。
タイトル以外にも、いくつかの方法について、ご紹介します。
[目次]
１．マクロの記録時にショートカットを割り当てる
２．Excelのマクロメニューからショートカットを割り当てる
３．VBAのOnKeyメソッドでショートカットを割り当てる
４．Excel起動時にマクロへ自動でショートカットを割り当てる(OnKey)
１．マクロの記録時にショートカットを割り当てる
「マクロの記録」から、マクロを作成する場合は、そのままショートカットを設定できます。
一番簡単な方法だと思います。
[方法]
以下から様にマクロの記録メニューから、ショートカットを登録できます。
Excel「開発」タブ＞「マクロの記録」メニュー＞ショートカットキー
（「マクロの記録」メニュー）
「開発」タブの表示方法(参考)
[開発] タブを表示する - Excel
＞１．[ファイル] タブをクリックします。 ＞２．[オプション] をクリックします。 ＞３．[リボンのユーザー設定] をクリックします。 ＞４．[リボンのユーザー設定] および [メイン タブ] で、[ 開発] チェック ボックスをオンにします。 |
２．Excelのマクロメニューからショートカットを割り当てる
2つ目は、Excelのマクロメニューからショートカットを割り当てる方法です。
[方法]
以下から作成済みマクロへショートカットを登録できます。
Excel「開発」タブ＞「マクロ」メニュー＞オプション（または、Alt+F8）
（「マクロ」メニュー）
３．VBAのOnKeyメソッドでショートカットを割り当てる
3つ目は、VBAのOnKeyメソッドで、ショートカットを割り当てる方法です。
[サンプルソース]
Application.OnKey "^j", "test1" |
サンプルでは、Ctrl+jキーに、「test1」というプロシージャのショートカットを割り当てています。
その他のキーの指定方法については、以下サイトを参考にしてください。
Application.OnKey メソッド (Excel)
注意として、コードを実行するまでは、ショートカットは登録されません。
何らかのタイミングでコードを実行する必要があります。
Excel起動時に、上記のコードを実行する方法は、次項で説明します。
４．Excel起動時にマクロへ自動でショートカットを割り当てる(OnKey)
VBAのOnKeyメソッドをExcel起動時に呼び出し、マクロへショートカットを登録する方法です。
Excel起動時にマクロを自動実行する方法については、前回説明しました。
ブックを開いた時やExcel起動時にマクロを自動実行する - Sumihirablog.
以下の通り、OnKeyメソッドを記述すれば、実現できます。
[コードの記述箇所]
(1) PERSONAL.XLSB＞Microsoft Excel Objects＞ThisWorkBook
(2) PERSONAL.XLSB＞標準モジュール＞任意のモジュール
(図：コードの記述箇所)
'PERSONAL.XLSB ThisWorkBook 'WithEventsにてApplicationオブジェクト(Excel全体を示すオブジェクト)に発生するイベントを拾える様にする 'PERSONAL.XLSBに記述することで、全てのExcelファイルに適用される。 Public WithEvents xlAPP As Application 'Excelブックが開いた時に自動実行する。 Private Sub Workbook_Open() 'アプリケーションオブジェクトを取得 Set xlAPP = Application '標準モジュールの呼出し '直接呼び出すとエラーになるため、実行時間をずらして呼び出す。 Application.OnTime Now + TimeSerial(0, 0, 1), "appFirst" End Sub |
[サンプルソース(2)]※標準モジュールに記述
'PERSONAL.XLSB 標準モジュール firstModule 'Excel起動時に自動実行するマクロを記述する。 'マクロの実行メニューにPublicプロシージャが表示されないようにする。 Option Private Module 'Excel起動時に自動実行するプロシージャ Public Sub appFirst() 'ファイル読込みに時間がかかるとエラーになるためスキップする |
上記の方法で、Excel起動時にショートカットを登録させることができます。
コードの説明については、前回の記事をご参照ください。
以上です。
ブックを開いた時やExcel起動時にマクロを自動実行する
通常Excelマクロは、マクロの実行メニューや(登録済みの)ショートカットから呼び出さなければ実行できませんが、
「ブックを開いた時やExcel起動時にマクロを自動実行したい」
と思う時がしばしばあります。
１．ブックを開いた時にマクロを自動実行する（workbook_open）
「workbook_open」 イベントプロシージャを利用して、実現することができます。
任意のExcelブックに以下のサンプルソースを記述してください。
[サンプルソース]
Sub workbook_open() MsgBox ("ブックが開かれました") End Sub |
[コード記述箇所]
任意のExcelブック＞VBAProject＞Microsoft Excel Objects＞ThisWorkBook
（図：コード記述箇所）
Book1が開かれた時に、メッセージを表示します。
（図：サンプルの実行結果）
応用すれば、色々なことができそうですね。
「workbook_open」は、ブック自体に指定できるイベントプロシージャです。openイベントに反応して実行されます。
ブックへの指定となるため、標準モジュールではなく「ThisWorkbook」に記述しなければなりません。
また、ファイル保存時はマクロありの「.xlsm」形式で保存が必要になるため、注意が必要です。
参考
エクセルExcel大事典 VBAマクロ イベントプロシージャ Open Target Cancel EnableEvents Volatile
２．Excel起動時にマクロを自動実行する
項番１の方法では、ブックにあらかじめコードを記述しておかなければなりません。そこで、ブックを指定せず、Excel全般にコードを適用します。
これにより、Excel起動時にマクロを自動実行することができます。
（正確には、任意のブックを開くたびに実行します）
[方法]
⇒PERSONAL.XLSBにコードを記述する。
[コードの記述箇所]
(1) PERSONAL.XLSB＞Microsoft Excel Objects＞ThisWorkBook
(2) PERSONAL.XLSB＞標準モジュール＞任意のモジュール
(図：コードの記述箇所)
'PERSONAL.XLSB ThisWorkBook 'WithEventsにてApplicationオブジェクト(Excel全体を示すオブジェクト)に発生するイベントを拾える様にする 'PERSONAL.XLSBに記述することで、全てのExcelファイルに適用される。 Public WithEvents xlAPP As Application 'Excelブックが開いた時に自動実行する。 Private Sub Workbook_Open() 'アプリケーションオブジェクトを取得 Set xlAPP = Application '標準モジュールの呼出し '直接呼び出すとエラーになるため、実行時間をずらして呼び出す。 Application.OnTime Now + TimeSerial(0, 0, 1), "appFirst" End Sub |
[サンプルソース(2)]※標準モジュールに記述
'PERSONAL.XLSB 標準モジュール firstModule 'Excel起動時に自動実行するマクロを記述する。 'マクロの実行メニューにPublicプロシージャが表示されないようにする。 Option Private Module 'Excel起動時に自動実行するプロシージャ Public Sub appFirst() 'ファイル読込みに時間がかかるとエラーになるためスキップする |
「ThisWorkbook」と「標準モジュール」の2か所にコードを記述しています。
「ThisWorkbook」の方は、項番１と同じく「workbook_open」のプロシージャを記述し、
その中で標準モジュールの「appFirst」プロシージャを呼び出す様にしています。
実行結果のイメージは省略しますが、どのExcelブックを開いても、項番１と同様の結果が得られます。
これにより、「appFirst」に記述した処理を、Excel起動時に自動実行することができます。
以上です。
バッチ実行時にプロンプト(DOS画面)を非表示にする
バッチ実行時にプロンプト(DOS画面)を非表示にする方法について書きます。
コマンドプロンプトの黒い画面を表示したくない場合に有効です。
(図：例の黒い画面)
バッチ実行時にプロンプト(DOS画面)を非表示にする
VBScriptを使用して実現します。
<参考>
Run メソッド
任意のバッチファイルを作成後、以下内容のファイルを作成し、拡張子を「.vbs」としてください。
[サンプルソース]
Dim oShell Set oShell = WScript.CreateObject ("WSCript.shell") oShell.run "C:\wk\firefox起動.bat",0 Set oShell = Nothing |
バッチ名(.bat)は任意のファイルを指定してください。
あとは作成した「.vbs」ファイルを実行するだけです。
私は、Firefoxの起動バッチを呼出す際に使用しています。
(利用環境でプロファイルを分けているので)
FireFoxの起動バッチについては、以前記事にしました。
Firefoxプロファイルまとめ
以上です。
