「ExcelVBA」を使って「LotusNotes」 から 効率的にデータを取得する方法
今回はノーツから通勤手当を取得する方法について紹介します。
この方法を使うと、Excel VBAを使用してLotus Notesと連携し、Notesから通勤手当のデータを取得することができます。通勤手当のデータはLotus Notesの特定のビューから取得し、Excelのワークシートに書き込まれます。この手法を用いることで、データの自動取得と処理を行うことができます。
サンプルアプリの紹介
下記に示す「ノーツから通勤手当を取得」ボタンをクリックすると、Lotus Notesから通勤手当データを取得し、Excelワークシートに結果を表示できます。
このサンプルアプリケーションは、実際の業務シナリオで使用できる基本的な機能を提供します。
次項にサンプルコードを紹介しますので、業務の効率化に役立ててください。
ノーツとエクセルが連携する箇所の解説
Lotus NotesとExcelが連携する主要な部分について詳細に解説します。
１．NotesSession、NotesDatabase、NotesView、NotesDocumentなどのLotus Notesのオブジェクトを格納するための変数を宣言します。
Dim Session As Object
Dim db1 As Object
Dim view1 As Object
Dim doc1 As Object２．Lotus Notesのセッションオブジェクトを作成し、データベースへのアクセスを確立します。
'Lotus Notesのセッションオブジェクトを作成し、それを「Session」変数に代入
Set Session = CreateObject("Notes.Notessession")
'サーバ名を設定、ローカルにあるアプリケーションのためnullを指定
Server = ""
'ノーツアプリケーションが存在するパスを設定
Directory = "VBATEST.nsf"３．データベースオブジェクトを作成し、データベースの場所を指定します。この部分では、サーバ名やディレクトリ名が設定されます。
'Lotus Notesのデータベースオブジェクトを作成し、指定されたサーバとディレクトリを使用して「db1」変数に代入
Set db1 = Session.GetDatabase(Server, Directory)
'Lotus Notesのビューオブジェクトをデータベースから取得し、指定されたビュー名を「view1」変数に代入
'（「通勤手当テーブル」は検索を行うビュー、1列目(No列)はソートされている必要があります）
Set view1 = db1.GetView("通勤手当テーブル")４．Excelワークシート内のデータをループ処理し、Lotus Notesデータベースから対応するデータを取得します。この部分で、通勤手当を取得し、Excelのワークシートに書き込むプロセスが行われます。
'ループを開始し、「waG1」ワークシートのセル(i, 1)に値が存在する限り繰り返す
Do While waG1.Cells(i, 1).Value <> ""
'「waG1」ワークシートのセル(i, 1)の値を取得し、3桁の数字にフォーマットするためにゼロ埋めを行って「Key1」変数に代入
Key1 = Right("000" + Trim(Str(waG1.Cells(i, 1).Value)), 3)
'「view1」ビューオブジェクトから指定されたキーに一致するドキュメント（レコード）を取得し、「doc1」変数に代入
Set doc1 = view1.GetDocumentByKey(Key1, True)
'「doc1」がNothingでない場合、つまり該当するドキュメントが存在する場合に以下を実行
If Not (doc1 Is Nothing) Then
'ノーツから取得した通勤手当を一旦「Teate」変数に格納
Dim Teate As Variant
Teate = doc1.tuukinteate
'ワークシートのセル(i, 6)に、数値に変換した通勤手当を代入
waG1.Cells(i, 6) = Val(Teate(0))
End If
'カウンタを1増やし、アプリケーションのステータスバーに進捗状況を表示
i = i + 1
Application.StatusBar = Int(i - 4 / 15) & " % 処理をしています..."
Loopサンプルアプリのコードの紹介 アプリの作成手順はこちら
以下がサンプルアプリのVBAコードです。
'変数の宣言や未定義の変数を使用することを禁止するために、コード内で「未定義の変数を使用しない」というオプションを有効にします。
Option Explicit
'マクロ（サブルーチン）の開始
Sub ノーツからデータ取得()
'ループカウンタとして使用される整数型（Long）の変数を定義
Dim i As Long
'ワークブック（Excelファイル）を指すWorkbookオブジェクトの変数を定義
Dim wa As Workbook
'ワークシートを指すWorksheetオブジェクトの変数を定義
Dim waG1 As Worksheet
'ファイル名を格納する文字列型の変数を定義
Dim fname As String
'Excelのイベントを無効にするために、自動マクロ（イベントハンドラ）を停止
Application.EnableEvents = False
'「fname」に現在のワークブック（このVBAコードが実行されているファイル）の名前を格納
fname = ThisWorkbook.Name
'「wa」にそのワークブックを指すWorkbookオブジェクトを代入
Set wa = Application.Workbooks(fname)
'「waG1」変数に「wa」ワークブック内の「Sheet1」ワークシートオブジェクトを代入
Set waG1 = wa.Worksheets("Sheet1")
'「Sheet1」ワークシートを選択
Worksheets("Sheet1").Select
'NotesSession、NotesDatabase、NotesView、NotesDocumentなどのLotus Notesのオブジェクトを格納するための変数を宣言
Dim Session As Object
Dim db1 As Object
Dim view1 As Object
Dim doc1 As Object
'サーバ名やディレクトリ名、キー（検索キー）を格納するための変数を宣言
Dim Server As String
Dim Directory As String
Dim Key1 As Variant
'Lotus Notesのセッションオブジェクトを作成し、それを「Session」変数に代入
Set Session = CreateObject("Notes.Notessession")
'サーバ名を設定、ローカルにあるアプリケーションのためnullを指定
Server = ""
'ノーツアプリケーションが存在するパスを設定
Directory = "VBATEST.nsf"
'Lotus Notesのデータベースオブジェクトを作成し、指定されたサーバとディレクトリを使用して「db1」変数に代入
Set db1 = Session.GetDatabase(Server, Directory)
'Lotus Notesのビューオブジェクトをデータベースから取得し、指定されたビュー名を「view1」変数に代入
'（「通勤手当テーブル」は検索を行うビュー、1列目(No列)はソートされている必要があります）
Set view1 = db1.GetView("通勤手当テーブル")
'「i」変数に初期値として5を代入
i = 5
'ループを開始し、「waG1」ワークシートのセル(i, 1)に値が存在する限り繰り返す
Do While waG1.Cells(i, 1).Value <> ""
'「waG1」ワークシートのセル(i, 1)の値を取得し、3桁の数字にフォーマットするためにゼロ埋めを行って「Key1」変数に代入
Key1 = Right("000" + Trim(Str(waG1.Cells(i, 1).Value)), 3)
'「view1」ビューオブジェクトから指定されたキーに一致するドキュメント（レコード）を取得し、「doc1」変数に代入
Set doc1 = view1.GetDocumentByKey(Key1, True)
'「doc1」がNothingでない場合、つまり該当するドキュメントが存在する場合に以下を実行
If Not (doc1 Is Nothing) Then
'ノーツから取得した通勤手当を一旦「Teate」変数に格納
Dim Teate As Variant
Teate = doc1.tuukinteate
'ワークシートのセル(i, 6)に、数値に変換した通勤手当を代入
waG1.Cells(i, 6) = Val(Teate(0))
End If
'カウンタを1増やし、アプリケーションのステータスバーに進捗状況を表示
i = i + 1
Loop
'自動マクロを有効に戻す
Application.EnableEvents = True
'読込み完了メッセージの表示
MsgBox "読込みが完了しました。", vbOKOnly
End Sub