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

# はじめに

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

![Dec-07-2019 08-13-01.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/895e43b0-ef37-dc98-3565-9db91586d355.gif)

# 使用した製品

今回、kintoneのREST APIをExcelのマクロのVBAオブジェクトとして呼び出すために筆者が所属するCData Software の[Excel Add-In for kintone](https://www.cdata.com/jp/drivers/kintone/excel/)を使用しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/aee6d436-3048-d739-5a3f-9bd53c2e12db.png)
※30日間の無償評価版がございます

# 作成手順

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

本製品は、いわゆるkintoneのプラグインとは異なり、ExcelのAdd-Inとして動作する製品です。ExcelがインストールされているWindwosマシンにCData kintone Excel Add-Inをインストールします。[こちらのページ](https://www.cdata.com/jp/drivers/kintone/download/excel/)から30日間ご利用いただける評価版をダウンロードできます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/3acb04a6-81f1-e82a-9b15-7f80c14722ca.png)
ダウンロードしたserup.exeを実行してインストールします、なお、この時、Excelアプリケーションが開いているとインストールできませんのでExcelは閉じてから実行ください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/d69e79b8-19c7-0a3d-04f8-b804ab784bac.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/089f75df-b83f-036e-7b06-35d292944b88.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/225ea008-6dd0-9f03-bede-90bb96bf8c85.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/e26b1cb6-e02e-7178-6c78-67719b8ebd71.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/865f9f46-95fb-8498-5e54-06c9582aee15.png)

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

## Excelテンプレートの準備

マクロ入りのExcelファイル(.xlsm)を作成します。本テンプレートファイルはこちらの[Github](https://github.com/kuwazzy/KintoneCdataVba/blob/master/%E8%A6%8B%E7%A9%8D%E6%9B%B8.xlsm)からもダウンロードできます。

### シート名

テンプレート_Macro

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/f5a6f329-a16f-fe56-92d2-ffa2ba086941.png)

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

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


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/543b2987-5104-7e2f-f646-b5cc03de9364.png)

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

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/333e0d8c-52de-e109-4c47-0ad7f58efc84.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/c2ada804-c8a2-18e5-8c77-43d4e0259067.png)

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/57f19ac7-e736-442e-91bf-0ba8fe4ff9e3.png)

## マクロ（VBA）の実装

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

```vb
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への接続

```vb
  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 からの接続](http://cdn.cdata.com/help/EKE/jp/xls/pg_xlsvbaconnection.htm)

### 見積書データの取得

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

```vb
    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製品マニュアル - データのクエリ](http://cdn.cdata.com/help/EKE/jp/xls/pg_excelvbaquery.htm)

### 見積明細データの取得

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

```vb
    '見積明細 取得
    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
```

# まとめ

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123181/59312aee-4968-2213-ae5c-6d739cf26dc8.png)

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