ExcelでSQLを使う-023: ソース公開-07 ELookup関数（Excel To Excel）
生後14日目。移行期を迎えて2日目。
産室を全体で2畳の広さに拡張。
仔犬たちは、産箱を出て動き回り始めました。
排泄場と産箱との間にある段差も越えて行き来しています。
◇◇◇◇◇
７、行データを検索・参照するELookup関数
ELookup関数は、次の次の次ぐらいに紹介するDSelect関数とDLookup関数の中間に位置するもので、一行全体のデータを”；”等の区切り子で連結して戻します。
例えば、DLookup関数は、英語最高得点者である林悟君の＜名前＞＜読み＞＜成績＞のデータを一度に取得することはできません。その難点をカバーしたのが ELookup関数です。
＜成績表＞と＜生徒名簿＞の二つの表から検索する
これまでは一つの表からだけ検索していましたが、SQLは複数の表から一度に検索することもできます。N5では、＜成績表＞と＜生徒名簿＞から検索しています。
SELECT
[生徒名簿$A1:C100].№, ←不要！
[生徒名簿$A1:C100].名前,
[生徒名簿$A1:C100].読み,
[成績表$D3:I100].成績
FROM [生徒名簿$A1:C100], [成績表$D3:I100]
WHERE
[成績表$D3:I100].生徒_№=[生徒名簿$A1:C100].№ AND
[成績表$D3:I100].種類='期末試験' AND
[成績表$D3:I100].科目_№=1
ORDER BY [成績表$D3:I100].成績 DESC
このレベルになるとエクセル初心者がエクセルの式を書いて複数の表から区切り子で連結して検索結果を取得するってのは相当に難しいと思われます。それを、いとも簡単に実現してくれるのがELookup関数です。
なお、ELookup関数は、予定にはなかったそれです。複数の表から・・・という課題をクリアする上で必要に迫られて昨日に書いたものです。次は、そのソースコードです。まあ、昨日に書いたと言っても、DLookup関数と違うのは朱記している3行程度です。
ELookup関数
Public Function ELookup(ByVal strSQL As String, _
Optional ByVal intSearch As Integer = 1, _
Optional ByVal xlFileName As String = "", _
Optional ByVal isHeader As Boolean = True, _
Optional ByVal returnValue As Variant = "") As Variant
On Error GoTo Err_ELookup
'
' 【要参照設定】
'
' Micrsoft ActiveX Data Objects 2.8 Library
'
Dim R As Integer ' 行インデックス
Dim N As Integer ' 行総数 - 1
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim strHDR As String
Dim strList As String
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
'
' ThisWorkbook.FullName の指定
'
If Len(xlFileName) = 0 Then
xlFileName = ThisWorkbook.FullName
End If
'
' 接続設定
'
With cnn
strHDR = IIf(isHeader, "HDR=YES", "HDR=NO")
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 12.0;" & strHDR & "IMEX=1;"
.Open xlFileName
'
' 列を読み込み
'
With rst
.Open strSQL, cnn, adOpenKeyset, adLockReadOnly
If intSearch < 0 Then
intSearch = rst.RecordCount + intSearch + 1
End If
If Not .BOF Then
N = CInt(.RecordCount) - 1
intSearch = intSearch - 1
.MoveFirst
For R = 0 To N
If intSearch = R Then
For Each fld In .Fields
strList = strList & fld.Value & ";"
Next fld
Exit For
End If
.MoveNext
Next R
End If
End With
'
' 末尾の";"を消す
'
strList = Replace(strList & "[END]", ";[END]", "")
End With
Exit_ELookup:
On Error Resume Next
rst.Close
Set rst = Nothing
ELookup = IIf(Len(strList & "") > 0, strList, returnValue)
Exit Function
Err_ELookup:
If isEcho Then
MsgBox "SELECT 文の実行時にエラーが発生しました。(ELookup)" & Chr(13) & Chr(13) & _
"・Err.Description=" & Err.Description & Chr(13) & _
"・SQL Text=" & strSQL, _
vbExclamation, " 関数エラーメッセージ"
Resume Exit_ELookup
End If
End Function
Public Function CutStr(ByVal Text As String, _
ByVal Separator As String, _
ByVal N As Integer) As String
Dim strDatas() As String
If N > 0 Then
strDatas = Split("" & Separator & Text, Separator, , 0)
CutStr = strDatas(N * Abs(N <= UBound(strDatas)))
End If
End Function
次は、エクセルの表情報を取得するGetFieldInfo関数を紹介します。表情報とは、ADOが表を読み込んだ場合の＜列名＞と＜データの型＞に関する情報を指します。これを知ることで、DSelect関数が検索したデータをエクセルのシートに書き出すことができます。
PS、エラー制御
DLookup関数、Elookup関数、DSelect関数を式に組み込んだ時は、広域変数 isEcho を真にしておくと、関連データを削除した際にエラーが表示されます。それが煩わしいので既定では、その値を偽にしていまっす。以下は、その真と偽とを切り替えるマクロです。
