SQLでデータを抽出するユーザー定義関数を作ってみたその２

先日SQLでデータを抽出するユーザー定義関数SQLSearchを作成した。

www.excellovers.com

その後テストした際に追加したいと思った機能を追加してみた。

その際に風柳さん@furyutei には貴重なアドバイスを頂きました。
ありがとうございます。

例えば

ADODB.ConnectionをOpenする際に、Extended Propertiesで HDR=No を指定してやれば、フィールド名が F1, F2, ... みたいに指定できますが、そういうことではなく…？
— 風柳 (@furyutei) 2020年9月24日

改修後のコード

Public Function SQLSearch(SQL文 As String, Optional Header有 As Boolean = True, Optional 元データヘッダー有り As Boolean = True) As Variant
'###Excel2007以降に接続出来るように設定。いじれば別のデータベースに接続も可能。

Application.Volatile True
     Dim myCON As ADODB.Connection
     Set myCON = New ADODB.Connection
     
     Dim ADODBExtendeProperties As String
     ADODBExtendeProperties = "Excel 12.0;IMEX=1"
     If Not 元データヘッダー有り Then ADODBExtendeProperties = ADODBExtendeProperties & ";HDR=No"

     myCON.Provider = "Microsoft.ACE.OLEDB.12.0"
     myCON.Properties("Extended Properties") = ADODBExtendeProperties
     
     Dim DBFullPath As String
     DBFullPath = Application.Caller.Parent.Parent.FullName
     
     myCON.Open DBFullPath
     
     Dim myRS As ADODB.Recordset
     Set myRS = New ADODB.Recordset
     
     myRS.Open SQL文, myCON, adOpenStatic
     

     Dim tmp As Variant
     If myRS.RecordCount = 0 Then
          tmp = "解無し"
          GoTo Ans
     End If
     
     Dim vvレコードセット件数 As Long
     Dim vvレコードセット項目数 As Long
     vvレコードセット件数 = myRS.RecordCount - 1
     vvレコードセット項目数 = myRS.Fields.Count - 1
     
     'Recordsetから配列に置き換え
     Dim RSを配列化 As Variant
     ReDim RSを配列化(vvレコードセット件数 - Header有, vvレコードセット項目数)
     
     Dim i As Long
     Dim c As Long
     If Header有 Then
          For i = 0 To vvレコードセット項目数
               RSを配列化(0, i) = myRS.Fields(i).Name
          Next i
     End If
     
     For i = 0 To vvレコードセット件数
          For c = 0 To vvレコードセット項目数
               RSを配列化(i - Header有, c) = myRS.Fields(c).Value
          Next
          myRS.MoveNext
     Next
     tmp = RSを配列化

Ans:
     SQLSearch = tmp
End Function
  

Public Function Table範囲アドレス取得(テーブル名 As String) As String
     Dim tmp As Variant
     tmp = Range(テーブル名).ListObject.Range.Address(False, False)
     Table範囲アドレス取得 = tmp
End Function

改修点

引数の追加

前回Verは引数はSQL文だけ受け取るものでしたが、以下の2つを追加した。

Header有
元データヘッダー有り

Header有

前回Verでは必ずタイトル行(例では1行目のキャリア・合計)が返るようになっていた。
f:id:ExcelLover:20200926202306j:plain

改修後は引数：Header有　にFALSEを指定することで、タイトル行を返さないようにした。
f:id:ExcelLover:20200926202329j:plain

こうすることで返り値のみが必要な場合に対応出来るようになった。

元データヘッダー有り

前回VerではFrom句のデータには必ずタイトル行が存在する想定になっていた。
しかし扱うデータがデータベースならともかく、扱うデータはExcelである。
タイトルが無くいきなりデータが始まる場合も想定される。

この引数にFALSEを指定することで1行目のデータをタイトル行ではなくデータとして扱うことが出来る。

f:id:ExcelLover:20200926202419j:plain

タイトル行がないのにどうやって抽出する列を指定するのか？
上記画像の数式内の「F1」がそれです。
元データ内の1列目を指定するときはF1とすれば良い。

配列変換の省力化

前回Verでは下記の手順でRecordsetのデータを最終形の配列に整形していた。

RecordsetからGetRowsで配列へ
GetRowsで取得したデータは元データと行列が逆転しているので正しい行列方向に変換

これをRecordsetから抽出する際にGetRowsで一括抽出はなく、Recordsetのデータを1行ずつ抽出する方法に変更した。

元々配列から配列に行列変換する際に1行ずつデータを見ているので作業工程的には大きい変動はない。
使う配列が１つ減る分、メモリ使用量が減る。(のではないか？よくわかっていない）

改修しようかどうしようか

集計する元データの列内に空白があるとエラーになる

エラーが出ることで元データに空白があるというアラートになる。
空白への処理はSQL文で行ったほうが良いのでは。

という点でそのままにしようどうしようかというところです。

255文字・255列制限

元データのセル範囲が256列以上、1つあたりの文字数が256文字以上の場合、はみ出したデータはRecordsetに取り込まれません。
これはそもそも仕様なのでどうしようもない気はするのですが、対処法があるのか。

もうちょっと迷ってみようと思います。

Excelが大好きだ！

Excel大好き経理マンがExcelの事を書き綴っていきます。