|
代码分享:
'2020-02-03 池盛龙
Public Conn As Object, rs As Object
Public strConn As String, strSQL As String
Public i As Integer, PathStr As String
Public Sub LJSJK()
Set Conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.FullName '设置工作簿的完整路径和名称
Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
'设置SQL查询语句
Conn.Open strConn '打开数据库链接
End Sub
Public Sub GBSJK()
rs.Close '关闭数据库连接
Conn.Close
End Sub
Public Sub GBLJ()
Set Conn = Nothing
Set rs = Nothing
End Sub
Private Sub CommandButton1_Click() '搜索
Sql = "SELECT * FROM [sheet2$] where 标题 like '%" & Me.TextBox1 & "%'" '将【标题】改成你实际的列标题
Call LJSJK '连接符丢失重新连接
rs.Open Sql, Conn, 1, 1
If rs.RecordCount > 0 Then
ListBox1.Column = rs.GetRows
Else
ListBox1.Clear
End If
Call GBSJK
Call GBLJ
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) '双击事件
Call 选入
End Sub
Private Sub TextBox1_Change()
Call CommandButton1_Click
End Sub
Private Sub UserForm_Initialize() '初始化
Sql = "SELECT * FROM [sheet2$] " '工作表sheet2改成你实际的工作表名称
Call LJSJK '连接符丢失重新连接
rs.Open Sql, Conn, 1, 1
If rs.RecordCount > 0 Then
ListBox1.Column = rs.GetRows
Else
ListBox1.Clear
End If
Call GBSJK
Call GBLJ
End Sub
Sub 选入()
HS = Selection.Row '鼠标选择单元格行数
LS = Selection(Selection.Count).Column '鼠标选择单元格列数
Cells(HS, LS) = ListBox1.List(ListBox1.ListIndex, 0)
Me.Hide '隐藏窗体
End Sub
|
|