I built an Excel macro for working with a DB.

Since this macro covers a lot of ground, I'll explain it across 4 parts.

Part 1: [Handling the Config sheet's settings in VBA](/plants/excel-vba-db-1-config-sheet)  
Part 2: [Storing SELECT results in Excel](/plants/excel-vba-db-2-select)  
Part 3: [Running INSERT, UPDATE, DELETE from Excel](/plants/excel-vba-db-3-insert-update-delete)  
Part 4: [Running MERGE from Excel](/plants/excel-vba-db-4-merge)    

[【Download the Excel file here】](https://github.com/atman-33/template-excel-vba/tree/main/Template_OracleDB%E6%93%8D%E4%BD%9C)

※Revised 2021/2/5  
　・Switched from ODBC to OraOLEDB.Oracle for the connection  
　・Fixed how INSERT, UPDATE, and MERGE statements are generated  

___
## Overview
Part 1 explains how to work with the settings used for DB operations in VBA (macro development).

![image](/plants/excel-vba-db-1-config-sheet/01.png)

The three settings needed for the Oracle connection — "service name," "username," and "password" — are configured in the Config sheet.

With the Configurator class explained here, you can easily add configuration values.  
（Think of it as bringing an ini file into Excel.）

___
### Package Structure
The structure inside the Excel macro is as follows.
（Only the modules used are listed below.）

```text
Template_ver1.x.x.xlsm
├標準モジュール
|   ├modCmnGlbConst
|
|
クラスモジュール
    ├Configurator
```

___
## Source Code Explanation

___
### ①modCmnGlbConst
To extract configuration values from the Excel sheet, this defines the "sheet, row, and column" where the Config information is written.

```vb
Option Explicit

' Config 設定シート情報
Public Const GLB_CONFIG_SHEET = "Config"
Public Const GLB_CONFIG_KEY_COL = 1
Public Const GLB_CONFIG_ITEM_COL = 2
Public Const GLB_CONFIG_START_ROW = 2
```

For example, if you want to use a sheet name other than "Config," change the constant `GLB_CONFIG_SHEET` above.

___
### ②Configurator
This is the class responsible for extracting the Config information.
The explanation is written as comments.

```vb
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
' Configurator クラス
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----

Option Explicit

' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
' 【使用手順】
' ① インスタンス生成
' ② setData    ：設定値を格納
' ③ getItem    ：設定値を取得
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----

' メンバ変数（Me.で参照可能とするためpublic）
Public dic As Object

' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
' brief : コンストラクタ
' note  :
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
Private Sub Class_Initialize()

    Set Me.dic = CreateObject("Scripting.Dictionary")

End Sub

' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
' brief : Config 情報である設定項目と設定値を格納
' note  :
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
Public Sub setData(sheet As Worksheet, keyCol As Long, itemCol As Long, startRow As Long)

    Dim i As Long
    Dim row As Long

    Dim key As String, item As String

    i = 0
    row = startRow

    Do While sheet.Cells(row, keyCol) <> ""

        key = sheet.Cells(row, keyCol)
        item = sheet.Cells(row, itemCol)

        Me.dic.add key, item

        row = row + 1
    Loop

End Sub

' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
' brief : setData 関数で取得した Config 情報の設定値を取得
' note  :
' ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
Public Function getItem(key As String) As String

    getItem = dic.item(key)

End Function
```

___
### ③Example usage of Configurator
Here's an example of using the Configurator class.

Running the code below displays a message box with the value of `SERVICE_NAME` as set in the Config sheet.

```vb
Option Explicit

    Dim config As Configurator

Public Sub main()

    Dim servicename As String, username As String, password As String, accessPath As String

    ' Config 設定の読み込み
    Set config = New Configurator
    config.setData ThisWorkbook.Worksheets(GLB_CONFIG_SHEET), GLB_CONFIG_KEY_COL, GLB_CONFIG_ITEM_COL, GLB_CONFIG_START_ROW

    servicename = config.getItem("SERVICE_NAME")
    Msgbox servicename

End Sub
```

That's it for this part.

Next time, I'll explain how to use the item values configured in the Config sheet to fetch data from the DB.