VBA Recordsetの利用について

 VBAでDataTableに似たものを利用したいと思っていろいろと調べているとRecordsetがいい感じとのことだったので調べて必要そうなところを抜粋してメモしている。

Private Sub Sample()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
'▽Recordse定義
     Dim myRS As ADODB.Recordset
     Set myRS = New ADODB.Recordset
     With myRS.Fields
          .Append "商品番号", adVarWChar, -1
          .Append "番号", adVarWChar, -1
     End With
     myRS.Open

'▽データ取り込み-----------------------------------------

    Dim i As Long
    Dim FieldList As Variant: FieldList = Array("商品番号", "番号")
    i = 2
    Do
        myRS.AddNew FieldList, CreateArrayData(Sheet2.Range("A" & i & ":B" & i).Resize(1, 2).Value)
        i = i + 1
    Loop Until IsEmpty(Sheet2.Cells(i, 1))
     
     myRS.MoveFirst
    
'Recordsetデータ検索-----------------------------------------
    i = 2
    Do
        myRS.Filter = "商品番号 = '" & Sheet1.Cells(i, 1).Value & "'"
        Sheet1.Cells(i, 2) = myRS!番号
        i = i + 1
        If i Mod 1000 = 0 Then
        End If
    Loop Until IsEmpty(Sheet1.Cells(i, 1))
'閉じる

     myRS.Close
     Set myRS = Nothing

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Function CreateArrayData(SecDimArray As Variant) As Variant

     Dim myVar As Variant

     Dim i As Long

     ReDim myVar(0 To UBound(SecDimArray, 2) - 1)

     For i = 0 To UBound(myVar)

          myVar(i) = SecDimArray(1, i + 1)

     Next

     CreateArrayData= myVar

End Function

備忘録 Having句で集計関数を利用する

　HAVING句をうまく利用する
　
　意外と思いつかなかったけど、HAVING句でCASE句を利用することができる
　※例は適当です。グループの中で条件を満たしていないものがあるかどうかのチェックができます。
　
　　・会社ごとに振込先口座を持っている
　　・グループ会社の中で異なる振込先があるグループがあるか
　
   (例）
    
   
    会社CD | 　　会社名　　 | グループ会社CD | 請求会社CD | 親会社CD | 銀行CD | 支店CD | 口座番号 | 口座名義人
    00001  |テスト１　　　　| 001            | 00001      |  00001   |  0001  |  002   | 1234567  | ｶ)ﾃｽﾄ1
    00002  |テスト１-１ 　　| 001            | 00002      |  00001   |  0001  |  002   | 0004567  | ｶ)ﾃｽﾄ2
    00003  |テスト１-２ 　　| 001            | 00001      |  00001   |  0001  |  002   | 1234567  | ｶ)ﾃｽﾄ1
    10001  |テスト２　　　　| 002            | 10001      |  10001   |  0001  |  002   | 1234567  | ｶ)ﾃｽﾄ2
    10002  |テスト２-１ 　　| 002            | 10001      |  10001   |  0001  |  002   | 1234567  | ｶ)ﾃｽﾄ2
    
    
    -- 「請求先」が「親会社」になっていないデータが混じっているグループ会社を抽出
　　-- グループCDで集約を行い、グループCDでまとめた件数＝レコードの中で「請求会社CD」
　　SELECT グループ会社CD
　　FROM 契約会社テーブル
　　GROUP BY グループ会社CD
　　HAVING COUNT(*) <> SUM(CASE 
　　　　　　　　                WHEN 請求会社CD = 親会社CD THEN 1 
　　　　　　　　                ELSE 0 
　　　　　　　　            END) AS 抽出対象
　　上記のデータ例で行くと「グループ会社CD：001」が抽出される
　　
　集計関数の中にもCASE式が書けるって初めて知ったときに結構衝撃だったけど、月ごとの集計結果を列で持ちたいときとかにも使えるので素晴らしい。
　
　　SELECT 
　　       商品品種名
　　      ,SUM(CASE WHEN SUBSTR(売上日,5,2) = '01' THEN 売上金額 ELSE 0 END) AS 1月売上
　　      ,SUM(CASE WHEN SUBSTR(売上日,5,2) = '02' THEN 売上金額 ELSE 0 END) AS 2月売上
　　      ,SUM(CASE WHEN SUBSTR(売上日,5,2) = '03' THEN 売上金額 ELSE 0 END) AS 3月売上
　　      ,SUM(CASE WHEN SUBSTR(売上日,5,2) = '04' THEN 売上金額 ELSE 0 END) AS 4月売上
　　      　　　　　・
　　      　　　　　・
　　      　　　　　・
　　FROM 売上テーブル
　　WHERE 売上日 BETWEEN '20210101' AND '20211231'
　　GROUP BY 商品品種名