如何用VB制作简单的查询程序，查询EXCEL表格中的内容并显示出结果

Question

如题所述

Answer 1

n Explicit
Private objSWbemServices As SWbemServices '
Private WithEvents DeleteProcessEvent As SWbemSink '进程结束监控
Dim xlApp As Excel.Application '定义EXCEL类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类

Private Sub Initial(ss As String)
Dim i, j As Integer
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
'xlApp.Visible = True '设置EXCEL可见
'Set xlBook = xlApp.Workbooks.Open(App.Path + "\" & "106-33-LCDTest-20130202.csv") '打开EXCEL工作簿
Set xlBook = xlApp.Workbooks.Open(App.Path + "\" & ss) '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表
'-----正式统计数量
Text1.Text = xlsheet.UsedRange.Cells.Rows.Count
sun = xlsheet.UsedRange.Cells.Rows.Count
For i = 2 To xlsheet.UsedRange.Cells.Rows.Count - 1
If Format(xlsheet.Cells(i, 3), "mm/dd") = Format(Now, "mm/dd") Then '统计今天的数据
Select Case Format(xlsheet.Cells(i, 3), "hh")
Case 0, 1 '0-1点钟数据统计
If xlsheet.Cells(i, 2) = "PASS" Then
Select Case xlsheet.Cells(i, 6)
Case 1
u(1, 1) = u(1, 1) + 1
Case 2
u(3, 1) = u(3, 1) + 1
Case 3
u(5, 1) = u(5, 1) + 1
Case 4
u(7, 1) = u(7, 1) + 1
Case Else
MsgBox "Nest异常", vbCritical, "消息提示:"
End Select
Else

。。。。。。。。。。。。。。。。。。。。。。。。
Else
Select Case xlsheet.Cells(i, 6)
Case 1
u(2, 12) = u(2, 12) + 1
Case 2
u(4, 12) = u(4, 12) + 1
Case 3
u(6, 12) = u(6, 12) + 1
Case 4
u(8, 12) = u(8, 12) + 1
Case Else
MsgBox "Nest异常", vbCritical, "消息提示:"
End Select
j = j + 1
End If
Case Else
'MsgBox "时间格式异常", vbCritical, "消息提示:"
End Select
End If
Next i
'-----------------------------------------------------------
'xlBook.Save '保存文件
xlBook.Close (True) '关闭工作簿
xlApp.Quit '结束Excel对象
Set xlApp = Nothing '释放xlApp对象
'MsgBox "初始化完成", vbCritical, "消息提示:OK"
Text2.Text = Format(j / Text1.Text, "0.00%")
End Sub