概述
适量的使用 vba，可以打破 ES 本身的限制，实现许多强大的功能。 本文将结合数据库技术，展示一种基于 myGrid 控件的模糊查询技术。 阅读本文需要一定的 vba 及 sql 知识。
过程演示
- 首先，在数据库中创建如下存储过程，其中红色字样请换成自己的库存表及相关字段。
CREATE PROCEDURE p_arrSearch
@Str varchar(100)--可多条件如："螺丝 M6 90"
AS
BEGIN
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @SqlStr varchar(200)
SET @PointerPrev = 1
SET @SqlStr = ''
WHILE(@PointerPrev <LEN(@Str))
BEGIN
SET @PointerCurr=CharIndex(' ',@Str,@PointerPrev)
IF(@PointerCurr>0)
BEGIN
SET @SqlStr = @SqlStr +' and charindex('''+SUBSTRING(@Str,@PointerPrev,@PointerCurr -
@PointerPrev)+''',描述)>0'
SET @PointerPrev = @PointerCurr+1
END
ELSE
BREAK
END
--最后一段在跳出循环后获取（）
SET @SqlStr = 'SELECT 0,物料编号,描述,批次编号,库存数,单位,0 FROM 批次库存视图 WHERE 库存数>0' + @SqlStr
+ ' and charindex(''' + SUBSTRING(@Str,@PointerPrev,LEN(@Str)-@PointerPrev+1) + ''',描述)>0'
--执行构造的 sql 语句
EXEC (@SqlStr)
END
GO
————————创建后，执行一下看看是否正常输出———————–
- 接下来，画出 ES 表样，选中描述字段，定义名称为“_wlms”
- 然后，添加两个控件:
extbox1和myGrid1。
说明：myGrid 控件也可以是 combobox 或者 listbox 或 listview 等，但用法稍有不同。
- ALT+F11 打开 VBE 在 sheet1 中加入以下代码:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet1.myGrid1.Visible = False
Sheet1.TextBox1.Visible = False
If Application.Intersect(Target, [_wlms]) Is Nothing Then Exit Sub
With Sheet1.TextBox1 '初始化 textbox
.Value = Target.Value
.Top = Target(1).Top
.Left = Target(1).Left
.Visible = True
.Activate
End With
With Sheet1.myGrid1 '初始化 grid
.Top = Target(1).Offset(1, 0).Top
.Left = Target(1).Left
End With
End Sub
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim sErr As String
Dim rs As New ADODB.Recordset
'非空时调用存储过程搜索物料
If TextBox1.Text <> "" Then
If Application.COMAddIns("ESClient10.Connect").Object.execQryproc("p_arrSearch", _
rs, sErr, Trim(TextBox1.Text)) = False Then Exit Sub
With Sheet1.myGrid1
.SetDatasource rs '绑定数据集
.ColWidth(2) = 3000 '描述列宽
.Visible = True '显示 grid
End With
rs.Close
End If
Set rs = Nothing
End Sub
Private Sub myGrid1_DblClick()
Dim selectRow As Integer
selectRow = Selection(1).Row
With myGrid1
Cells(selectRow, 4) = .Text(.Row, 2) '描述
Cells(selectRow, 5) = .Text(.Row, 1) '编号
Cells(selectRow, 6) = .Text(.Row, 3) '批号
Cells(selectRow, 7) = .Text(.Row, 4) '数量
Cells(selectRow, 8) = .Text(.Row, 5) '单位
Cells(selectRow, 11) = .Text(.Row, 4) '库存
.Visible = False
End With
TextBox1.Visible = False
End Sub
- Ok,大功告成，实际效果如下。 1.填入“m6 螺丝 90”
2．双击选择后
小结
基于控件的模糊搜索技术的主要工作原理是：使用 VBA 调用存储过程进行查询，通过 Grid 控件显示查询结果，最后由用户双击选择。
注：由于 myGrid 控件为非微软官方控件，某些 EXCEL 版本打开时会有 ActiveX 控件启用提示，消除提示的方法请自行百度