Excelマクロでkintone見積書アプリからExcel方眼紙の帳票を作成してみた

はじめに

本記事では、Excelマクロでkintoneのデータを使った帳票を作成する方法をご紹介します。「見積番号」をセットして「作成」ボタンをポチっと押すだけで、kintoneの見積書アプリからデータを取得してExcelの見積書を作成できます。

使用した製品

今回、kintoneのREST APIをExcelのマクロのVBAオブジェクトとして呼び出すために筆者が所属するCData Software のExcel Add-In for kintoneを使用しました。

※30日間の無償評価版がございます

作成手順

CData kintone Excel Add-In のインストール、および、接続確認

本製品は、いわゆるkintoneのプラグインとは異なり、ExcelのAdd-Inとして動作する製品です。ExcelがインストールされているWindwosマシンにCData kintone Excel Add-Inをインストールします。こちらのページから30日間ご利用いただける評価版をダウンロードできます。

ダウンロードしたserup.exeを実行してインストールします、なお、この時、Excelアプリケーションが開いているとインストールできませんのでExcelは閉じてから実行ください。

インストーラが完了してExcelを起動すると、CDATAというリボンが追加されて「取得元Kintone」というボタンが追加されていればインストールは成功です。

このボタンをクリックすると、初回であれば、接続ウィザードが起動します。User, Password, Urlに、kintoneへ接続する際のユーザ名、パスワード、サブドメインを含むUrlをセットしてください。「接続テスト」ボタンをクリックして「サーバーに接続できました。」というダイアログが出ればExcelからkintoneへの接続は成功です。
※ エラーとなる場合は、設定項目を見直すか、セキュアアクセスやプロキシサーバなどの設定が必要となります。こちらの記事をご参考ください。

接続ウィザードのOKボタンをクリックすると「テーブル：」のドロップダウンリストにkintoneのアプリ一覧が表示されます。今回は「見積書 テーブル」を選択して「OK」ボタンをクリックします。

見積書アプリのデータがExcelに読み込まれました。今回の記事では詳細をご説明しませんが、Excel上で値を編集して「行の挿入/更新/削除」ボタンをクリックすることで、Excelのデータでkintoneのデータを更新することもできます。

同様に「見積書_見積明細 テーブル」のデータも別シートに読み込んでみます。

これで、ExcelからCDataExcel Add-In経由でkintoneの見積書アプリのデータにアクセスできる準備が整いました。

Excelテンプレートの準備

マクロ入りのExcelファイル(.xlsm)を作成します。本テンプレートファイルはこちらのGithubからもダウンロードできます。

シート名

テンプレート_Macro

検索項目、および、作成ボタン

項目	セル	備考
見積番号	F2	検索用
作成	O2:03	マクロ実行用ボタン（マクロ登録名は「作成_Click」）

マクロ(VBA)で見積書アプリからデータを自動で取得する項目

以下（黄色）のセルに見積書アプリからVBAでデータを取得して自動セットします。

項目	セル	備考
見積番号	M4
見積日	M5
宛名	A8
備考	B30
RecordId (非表示)	M2

※有効期限は見積日の1ヶ月後を取得する数式 [=EDATE(M5,1)]でセット

見積書の明細（テーブル部分）のデータは、セルの１８行目から27行目まで繰り返しでセットします。

項目	セル	備考
型番	B18 - B２７
商品名	D18 - D２７
単価	J18 - J２７
数量	L18 - L２７

※小計と合計金額は数式でセット

マクロ（VBA）の実装

「作成」ボタンをクリックした時のマクロの処理です。

Sub 作成_Click()
  On Error GoTo Error
  Dim module As New ExcelComModule
  module.SetProviderName ("Kintone")
  module.SetConnectionString ("User=*****;Password=*****;Url=https://*****.cybozu.com")
  Cursor = Application.Cursor
  Application.Cursor = xlWait
  Dim nameArray, valueArray
  With Worksheets("テンプレート_Macro")
    '必須項目チェック
    If Cells(2, "F").Value = Empty Then
      Err.Description = "見積り番号をセットしてください"
      GoTo Error
    End If
    'セル初期化
    Cells(4, "M").MergeArea.ClearContents '見積番号
    Cells(5, "M").MergeArea.ClearContents '見積日
    Cells(8, "A").MergeArea.ClearContents '宛名
    Cells(30, "B").MergeArea.ClearContents '備考
    Cells(2, "M").MergeArea.ClearContents 'RecordId (非表示)
    For i = 18 To 27
      Cells(i, "B").MergeArea.ClearContents '見積明細(型番)
      Cells(i, "D").MergeArea.ClearContents '見積明細(商品名)
      Cells(i, "J").MergeArea.ClearContents '見積明細(単価)
      Cells(i, "L").MergeArea.ClearContents '見積明細(数量)
    Next i
    '見積書 取得
    Query = "SELECT * FROM 見積書 WHERE 見積番号 = '" & Range("F2").Value & "'"
    result = module.Select(Query, nameArray, valueArray)
    If Not module.EOF Then
      Cells(4, "M").Value = module.GetValue(3) '見積番号
      Cells(5, "M").Value = module.GetValue(13) '見積日
      Cells(8, "A").Value = module.GetValue(8) '宛名
      Cells(30, "B").Value = module.GetValue(11) '備考
      Cells(2, "M").Value = module.GetValue(0) 'RecordId (非表示)
    Else
      Err.Description = "見積り番号が見つかりませんでした"
      GoTo Error
    End If
    '見積明細 取得
    Query = "SELECT * FROM 見積書_見積明細 WHERE 見積書Id = '" & Range("O2").Value & "' ORDER BY Id"
    result = module.Select(Query, nameArray, valueArray)
    i = 18
    While (Not module.EOF)
      Cells(i, "B").Value = module.GetValue(8) '見積明細(型番)
      Cells(i, "D").Value = module.GetValue(7) '見積明細(商品名)
      Cells(i, "J").Value = module.GetValue(6) '見積明細(単価)
      Cells(i, "L").Value = module.GetValue(5) '見積明細(数量)
      module.MoveNext
      i = i + 1
    Wend
    MsgBox "完成"
  End With
  Application.Cursor = Cursor
  module.Close
  Exit Sub
Error:
  MsgBox "ERROR: " & Err.Description
  Application.Cursor = Cursor
  module.Close
End Sub

いくつかのパートを解説してきます。

kintoneへの接続

  Dim module As New ExcelComModule
  module.SetProviderName ("Kintone")
  module.SetConnectionString ("User=*****;Password=*****;Url=https://*****.cybozu.com")

※User, Password, Urlには、kintoneへ接続する際のユーザ名、パスワード、サブドメインを含むUrlをセットしてください

CData kintone Excel Add-In製品マニュアル - VBA からの接続

見積書データの取得

「F2」セルにユーザがセットした見積番号を検索条件としてSQLで見積書アプリからデータを取得します。「M2」には次の「見積明細データの取得」時の検索キーとして必要な「RecordId」をセットします。

    Query = "SELECT * FROM 見積書 WHERE 見積番号 = '" & Range("F2").Value & "'"
    result = module.Select(Query, nameArray, valueArray)
    If Not module.EOF Then
      Cells(4, "M").Value = module.GetValue(3) '見積番号
      Cells(5, "M").Value = module.GetValue(13) '見積日
      Cells(8, "A").Value = module.GetValue(8) '宛名
      Cells(30, "B").Value = module.GetValue(11) '備考
      Cells(2, "M").Value = module.GetValue(0) 'RecordId (非表示)
    Else
      Err.Description = "見積り番号が見つかりませんでした"
      GoTo Error
    End If

CData kintone Excel Add-In製品マニュアル - データのクエリ

見積明細データの取得

続いて見積書アプリの（サブ）テーブルに登録されている見積明細を取得します。kintoneの（サブ）テーブルデータは「アプリ名_（サブ）テーブル名」、今回であれば「見積書_見積明細」というテーブル名称で取得できます。見積書Idで絞り込みます。

    '見積明細 取得
    Query = "SELECT * FROM 見積書_見積明細 WHERE 見積書Id = '" & Range("O2").Value & "' ORDER BY Id"
    result = module.Select(Query, nameArray, valueArray)
    i = 18
    While (Not module.EOF)
      Cells(i, "B").Value = module.GetValue(8) '見積明細(型番)
      Cells(i, "D").Value = module.GetValue(7) '見積明細(商品名)
      Cells(i, "J").Value = module.GetValue(6) '見積明細(単価)
      Cells(i, "L").Value = module.GetValue(5) '見積明細(数量)
      module.MoveNext
      i = i + 1
    Wend

まとめ

いかがでしたでしょうか？ 今回は見積書の帳票出力を例に作成しましたが、同様の手順で既存のExcelテンプレート資産を使ってkitoneのデータで帳票出力することできます。また、帳票の出力だけではなく、Excelシートから「登録」ボタンをクリックすることでSQLのInsert/Update/Delete文でkintoneにExcelのデータを登録することも可能です。詳細はこちらのマニュアルに記載がありますので是非お試しください。