|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Dim arr
- Sub 按钮2_Click()
- Dim Sql_str As String
- If IsEmpty(Range("D14").Value) Then
- MsgBox ("Please input your employee number!")
- End
- Else
- Sql_str = "select 工号,姓名,Name,工作起始,上年剩余年假,当年年假天数,当年已用年假,剩余可用年假,年假使用情况2016 from [2016$] where 工号=" & Range("D14").Value & ""
- Call 查询(Sql_str)
- End If
- With UserForm1
- .TextBox1 = arr(1, 0)
- .TextBox2 = arr(2, 0)
- .TextBox3 = arr(0, 0)
- .TextBox4 = arr(3, 0)
- .TextBox5 = arr(4, 0)
- .TextBox6 = arr(5, 0)
- .TextBox7 = arr(6, 0)
- .TextBox8 = arr(7, 0)
- .TextBox9 = arr(8, 0)
- .Show
- End With
- End Sub
- Sub 查询(sql As String)
- Dim cnn As Object, rs As Object, i%, j%
- Set cnn = CreateObject("ADODB.Connection")
- Set rs = CreateObject("ADODB.Recordset")
-
- cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\年假查询2016基础数据.xlsx"
- rs.Open sql, cnn
-
- arr = rs.getrows
-
- rs.Close
- cnn.Close
- Set rs = Nothing
- Set cnn = Nothing
- End Sub
复制代码 |
|