|
- Option Explicit
- Dim cnn As Object, aa As String
- Sub 报表整理()
- Dim Sh As Worksheet, Falg As Boolean
- aa = InputBox("请输入报表日期:", "编制报表", "1日")
- If StrPtr(aa) = 0 Then Exit Sub
- For Each Sh In Worksheets
- If Sh.Name = aa Then Falg = True: Exit For
- Next
- If Falg = False Then MsgBox "没有" & aa & "的工作表!", vbCritical: End
- Set cnn = CreateObject("adodb.connection")
- If Application.Version < 12 Then
- cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
- Else
- cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
- End If
- 输出结果 "业务一部", "部门"
- 输出结果 "业务二部", "部门"
- 输出结果 "业务三部", "部门"
- 输出结果 "第一级别", "级别"
- 输出结果 "第二级别", "级别"
- 输出结果 "第三级别", "级别"
- 输出结果 "优秀", "级别"
- cnn.Close
- End Sub
- Private Sub 输出结果(ByVal myName As String, ByVal Colum As String)
- Dim Sh As Worksheet, SQL As String
- Set Sh = Sheets(myName)
- SQL = "Select 录音时间,姓名,年龄,地址,部门,级别 from [" & aa & "$] where " & Colum & "='" & myName & "'"
- Sh.Range("A2:F65536").Clear
- Sh.Range("A2").CopyFromRecordset cnn.Execute(SQL)
- Sh.Columns("A:A").NumberFormatLocal = "yyyy""年""m""月""d""日"";@"
- End Sub
复制代码 |
|