Libre Office の Base をマクロで使う-Base データの読込み- より
関数は、下記。
Function GetDBRecCount(ByVal tblname as String, ByVal filterstr as String) As Long
Dim Doc as Object
Dim Form as Object
Dim Connection as Object
Dim Statement as Object
Dim ResultSet as Object
Dim sqlstr as String
Dim dstr as String
Dim recs as Long
filterstr = Trim(filterstr)
If filterstr = "" Then
sqlstr = "Select Count(*) from "+tblname
Else
sqlstr = "Select Count(*) from "+tblname+" where "+filterstr
End If
Doc = ThisComponent
Form = Doc.getDrawPage().getForms().getByName("MainForm")
REM ***** SQL接続 *****
Connection = Form.ActiveConnection
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery(sqlstr)
If IsNull(ResultSet) Then
recs = 0
Else
ResultSet.next()
dstr = ResultSet.getString(1)
recs = CLng(dstr)
End If
ResultSet.Close()
Statement.Close()
GetDBRecCount = recs
End Function
関数使用時の例。
「T_member」というテーブルの「name」フィールドに”鈴木”さんが何人いるかをカウントする場合。
フォームのボタンに割り当てて使ってください。
Sub NameCount
Dim recCount as Integer
ofilter = """name"" like '%鈴木%'"
recCount = GetDBRecCount("T_member",ofilter)
msgbox recCount & "件"
End Sub
件数取得のマクロは、別のタイプのものもあり。
( LibreOffice 6.2.4.2 / Linux Mint Debian Edition3(LMDE3)”Cindy” )