IT PORT

Season 1

© 2024 IT PORT

(第1回)Excelマクロで経費精算を簡単に！

By
約 10 分
(第1回)Excelマクロで経費精算を簡単に！

目次

はじめに
やりたいこと
Excelフォーマットの作成
SQLiteでデータベース作成
ExcelとSQLiteを連携させてみる
次回予告

はじめに

今回は、Excelマクロで日々のルーティン作業を簡単にしてみるという目的で、全2回の記事で皆さんに紹介したいと思います。簡単な作業でも、効率化することにより1秒でも時間の節約が出来るので、今回の紹介した機能が日々の業務もしくはプライベートに活かしていただけたら幸いです。

今回の効率化は交通費計算を簡単にできるツールを作りたいと思っています。PCやスマートフォンで簡単に検索できるとはいえ、Excelのフォーマットなどが決まっている方だと、入力が面倒と感じる時があると思いますし、交通費をいちいち調べるのもかなり面倒だと思います。一度でも行ったことがある場所は事前にデータベースに登録しておき、自動で表示出来たら便利ですよね。そんなツールを作っていきたいと思います。

やりたいこと

今回マクロを組み込んで実行したいことは下記の画像イメージを想定してます。


  1. 　出発地・到着地をインクリメンタルサーチっぽく検索できるようにする
  2. 　出発地、到着地、片道/往復をキーにして、金額を自動表示させる

以上のことを、実装したいと考えています。

動作環境ツールは以下を使用する想定です。（作成する上で追加になった際は改めて記載します）

フォーマットファイルExcel2016 64bit
データベースSQLite
データベース接続ツールSQLite for Excel

今回はマクロを作成するので、Excelファイルを対象としています。データベースはファイル形式で保存できるSQLiteが手軽に扱え、また共有フォルダ等に置くことで、他の方と中身を共有できるので今回はこちらを使います。

また、今回データベースとの接続ツールに SQLite for Excelを使います。こちらはODBCなしでデータベースへの接続が可能となるため、事前にインストール作業等が必要ないため、今回はこちらを採用します。

Excelフォーマットの作成

まずは実際に使うフォーマットファイルを作成していきたいと思います。こちらは自由に作成していただく、もしくは既存のものを使っていただければ大丈夫です、今回はサンプルを用いて、紹介しますが、ご自分の手元のファイルに合わせて、プログラムの内容も少し修正してもらえればと思います。今回使用するフォーマットは下記になります。

  • 日付
  • 摘要　・・・　交通費を使った理由を記載
  • 金額
  • 内訳　・・・　交通費以外も入力できるように選択できるようにしておきます
  • 出発地
  • 到着地
  • 片道/往復　・・・　片道・往復のリスト形式にする
  • 備考

上記の項目を用意しています。この辺りも用途に合わせて、自由に調整してください。

SQLiteでデータベース作成

次に、データ連携元となるデータベースを用意します。SQLiteのダウンロードやインストールに関しては、別記事にて紹介していますので、そちらを参考にしていただければと思います。

SQLiteで簡単にDatabase作成
https://itport.cloud/?p=6463
目次はじめにSQLiteとはデータ型についてGUIでDatabaseを扱う終わりにはじめにアプリケーション開発や、WEBシステム開発などIT技術の発展とともに大容量のデータが必要とされる時代となってきました。ビッグデータなど一般的なデータ管理の処理では扱いが困難なもの...
IT PORT

DB名liquidation.db
テーブル名liqui_tb

DB名、テーブル名は上記の設定で作業したいと思います。DBの作成は、先ほど紹介しました別記事に記載がありますので、今回は割愛して、テーブルの作成から紹介していきます。下記のSQL文でテーブルを作成します。

CREATE TABLE "liqui_tb" (
	"id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"title"	TEXT NOT NULL UNIQUE,
	"amount"	INTEGER,
	"breakdown" 　　TEXT,
	"point_dep"	TEXT,
	"arr_place"	TEXT,
	"date"	REAL
);

特に複雑な制約等は設けておらず、簡易な設定にしております。idとtitleは必須項目としております。交通費以外でも使用する用途がある場合、タイトルをキーにして取り出すといったことも出来るようにしています。
（この部分も紹介できればと思っております）

ExcelとSQLiteを連携させてみる

これで、データ格納の箱は用意できたので、ExcelとSQLiteを連携できるように設定します。まずは、下記のサイトからツールのダウンロードを行います。必要なものはダウンロードしたツールにすべて入っているので、設定を変えたい方以外は、標準のもので対応できると思います。

govert/SQLiteForExcel
https://github.com/govert/SQLiteForExcel
A lightweight wrapper to give access to the SQLite3 library from VBA. - govert/SQLiteForExcel
GitHub
 

ダウンロードページから最新版のツールをダウンロードします。

ダウンロードしたものを解凍すると、下記画像の通り展開されます。

Distributionフォルダの中に入ると、必要ファイルが入っており、まずはSQLiteforExcel_64.xlsmを開きます。

開いたExcelでAlt + F11を押して、マクロの画面を呼び出すと、Sqlite3とSQLiteDemoというモジュールが配置されています。

  • SQLite3　　　 ・・・　SQLIteとの接続する上で必要な定義
  • SQLiteDemo　・・・　接続やDB処理のサンプルコードが載っている

SQLIte3の定義を使って、処理を書いていきますが、書き方等が決まっているので、SQLiteDemoのサンプルコードを確認して、コードを書いていきます。このモジュールを今回作成するファイルにインポートしたいので、まずはモジュールのエクスポートを行います。上記二つのモジュールの上で右クリックし、”ファイルのエクスポート”をクリックします。指定した場所に.basのファイルが配置されます。（複数ファイル同時にエクスポート・インポートはできないみたいです…）

インポートに関しては、今回使用するファイルでマクロを開き、同様の箇所で右クリックし、”ファイルのインポート”をクリックします。ここで先ほどのファイルを読み込むと、モジュールを使用できるようになります。

ただ、これだけでは動作しないので、必要ファイルの配置と、マクロ内の設定を見ていきます。ダウンロードしたツールのDistributionフォルダ配下の下記2ファイルを今回使用するファイルを配置したフォルダと同じディレクトリに配置します。

sqlite3.dllとSQLite3_StdCall.dllをコピーします。ついでにデータベースのファイルも同ディレクトリに配置しておきます（実運用する場合は、共有フォルダ等で使用される場合が多いと思います）。

一旦必要ファイルが設定できたので、今度はマクロ側の設定に移ります。今回は接続できるかのテストを行いたいので、SQLiteDemoのモジュールを使用して、確認してみます。下記画像の赤いラインを引いた箇所を作成したデータベースの名前に書き換えます。

今回は接続テストなので、SQLiteDemoモジュールのAllTests()のTestOpenCloserV2までを実行してみます。
InsertやDeleteの確認は、次回コードを書いて確認したいと思います。TestErrorの箇所でブレイクポイントを打って、実行してみます。

Public Sub AllTests()
    ' Check that this location can be written to
    ' Note that this file will be deleted after the tests complete!
    TestFile = Environ("TEMP") & "\liquidation.db"
        
    Dim InitReturn As Long
    #If Win64 Then
        ' I put the 64-bit version of SQLite.dll under a subdirectory called x64
        InitReturn = SQLite3Initialize(ThisWorkbook.Path + "\x64")
    #Else
        InitReturn = SQLite3Initialize ' Default path is ThisWorkbook.Path but can specify other path where the .dlls reside.
    #End If
    If InitReturn <> SQLITE_INIT_OK Then
        Debug.Print "Error Initializing SQLite. Error: " & Err.LastDllError
        Exit Sub
    End If
    
    TestVersion
    TestOpenClose
    TestOpenCloseV2
    TestError
    TestInsert
    TestSelect
    TestBinding
    TestDates
    TestStrings
    TestBackup
    TestBlob
    TestWriteReadOnly
    SQLite3Free ' Quite optional
        
    Debug.Print "----- All Tests Complete -----"
End Sub

実行した結果は、マクロの イミディエイトウインドウで確認できます（Ctrl + Gキーで呼び出し可能）
実行結果を見てみるとエラーになっていました…

SQLite3Initialize Error Loading C:\Users\Ochi\Desktop\itportフォルダ\x64\SQLite3.dll:              126 
Error Initializing SQLite. Error: 126

表示されたメッセージを確認すると、x64のフォルダ配下にSQLite3.dllがないため、エラーになっています。Excelの64bit版を使用する際は、こちらのフォルダを配置しておく必要があります。このフォルダはダウンロードしたツールに入っているので、フォルダごとコピーします。

このフォルダを、今回使用するフォルダに配置します。最終的に下記のようなフォルダ構成となります。

この設定をして、もう一度接続テストをしてみます。

3.11.1
SQLite3Open returned 0
SQLite3Close returned 0
SQLite3Open returned 0
SQLite3OpenV2 returned 0
SQLite3Close V2 returned

先ほどとは違うメッセージが表示されています。戻り値が0というのが気になりましたが、SQLite3のモジュールで下記の定数が設定されているので、うまく動作していることが確認できました。

Public Const SQLITE_INIT_OK     As Long = 0

データベースのOpenからCloseまで問題なく、動作確認できました。

次回予告

次回は、実際に入力データの追加・更新・削除の機能を実装していきたいと思います。簡単な機能にはなりますが、特に電車の交通費計算などは、データベースに記憶させておくと入力処理がかなり楽になります。次回記事もお楽しみに。