Question

vb读取EXCEL数据

Answer 1

放置一个CommonDialog1控件用于选择Excel文件
Private Sub Command1_Click()
Dim i As Long
Dim Sum1, Sum2 As Long
Dim VBExcel As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlssheet As Excel.Worksheet
Set VBExcel = CreateObject("Excel.Application")
CommonDialog1.FileName = ""
CommonDialog1.Filter = "EXCEL文件(*.xlsx)|*.xlsx"
CommonDialog1.ShowOpen
If CommonDialog1.FileName = "" Then
Exit Sub
Else
Set xlbook = VBExcel.Workbooks.Open(CommonDialog1.FileName)
Set xlssheet = xlbook.Worksheets(1)
VBExcel.Visible = True
For i = 2 To 35535
If xlssheet.Cells(i, 1) = "" Then
Exit For
Else
If xlssheet.Cells(i, 1) = "张三" Then '检查第i行，第一列数据，自己可以定义
Sum1 = Sum1 + xlssheet.Cells(i, 2)
End If
If xlssheet.Cells(i, 1) = "李四" Then
Sum2 = Sum2 + xlssheet.Cells(i, 2)
End If

End If
Next
End If
Text1.Text = Sum1
Text2.Text = Sum2
xlbook.Close (True)
Set xlssheet = Nothing
Set xlbook = Nothing
Set VBExcel = Nothing
End Sub