Outlook vbaで、外部Excelの最終行を取得するマクロ

効率化
スポンサーリンク

Excelファイルの最終行の下にデータを追加したいケースは多々あります。

今回は、Outlook vbaから外部のExcelファイルの最終行を取得するマクロです。

できるようになること

Outlook vbaから、外部Excelの最終行を取得できるようになります。

結論

Outlookマクロの場合

Cells(oApp.Rows.count, 1).End(-4162)

↑と書きます。
詳しい解説は後述します。

ちなみに、Excelマクロであれば

Cells(Rows.Count, 1).End(xlUp)

で取得できます。

やりたいこと

以下のような条件のときに役立ちます。

  • Outlookは常に立ち上げている
    • Excelは常に立ち上げているわけではない
  • 立ち上がっているOutlookから、指定のExcelのデータへ1行追記したい
    • この時、Excelを立ち上げないで追記したい

たかだか1行追記するためだけに、Excelを手作業で開きたくないですよね。

具体的に、

  • 指定されたExcelの最終行に
  • 測定した体温データを記録する

というユースケースでスクリプトを書いてみましょう。

コード全体

Sub InputTemperature()
    Dim temperature As String
    temperature = InputBox("体温は?")
    
    Const EXCEL_PATH = "C:\temp\test.xlsx"
    Dim oApp As Object 'Excel アプリを起動しておく箱

    Set oApp = CreateObject("Excel.Application") 'エクセルのアプリケーションを作る
    oApp.Visible = False      '裏側で起動するようにする
    oApp.UserControl = True  'ユーザー操作可能にするおまじない
    
    'xlsxファイルを開く
    oApp.Workbooks.Open (EXCEL_PATH)
       
    Dim LASTROW As Long

    ' 最終行の取得
    LASTROW = oApp.Cells(oApp.Rows.count, 1).End(-4162).Row
    
    oApp.Cells(LASTROW + 1, 1) = Date
    oApp.Cells(LASTROW + 1, 2) = temperature
        
    oApp.Quit
End Sub

これで、最終行の次の行の1列目に日付、2列目に体温を入力することができます。

Appendix

3行目

temperature = InputBox(“体温は?”)

ダイアログボックスを出して体温を入れさせます。
validationとかかけてないです。

5行目

Const EXCEL_PATH = “C:\temp\test.xlsx”

ファイルパスを指定します。
ご自分の環境に合わせてパスを指定してください。

6行目

Dim oApp As Object ‘Excel アプリを起動しておく箱

oAppに、Excelのオブジェクトを入れておきます。
以降、Excel関連のデータにアクセスするときには、必ず頭にoAppをつけます。

9行目

oApp.Visible = False ‘裏側で起動するようにする

実際にはExcelアプリを裏で起動しています。
が、Visible=Falseにして、裏で起動させることで、目に止まらないようにするので自動的に動いているように見えます。

18行目

LASTROW = oApp.Cells(oApp.Rows.count, 1).End(-4162).Row

今回最大のポイント。
End(-4162)という謎の数字を入れます。

XlDirection 列挙 (Excel)
Office VBA リファレンス トピック

ここを参考にしています。

Excel内でマクロを組むと、xlUp=-4162と定義されているのですが……
Outlook内だと定義されていないので、直接定義されている数字を入れて動かします。

これで、Excel系のモジュールを入れなくても動くはず。

ちなみに、一番右端は-4161です。

お好みでSaveを入れてください。
私の環境ではOneDriveで自動保存が働いているので、特にSaveしなくてもOKです。

まとめ

OutlookからExcelファイルへデータを追記するマクロ(最終行取得)を解説しました。

応用すれば、Excelのデータを自由自在にOutlookから操ることができます。

コメント

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