给你一个操作excel的代码,是和数据库相连的,希望能给你带来帮助。 Private Sub open_Click() If Ado1.Recordset.RecordCount <> 0 Then MsgBox "已经有一个打开的文件了。", vbInformation Else CommonDialog1.DialogTitle = "Open Microsoft Excel File" CommonDialog1.Filter = "Microsoft Excel File (*.xls)|*.xls" CommonDialog1.ShowOpen If CommonDialog1.CancelError = True Then Exit Sub End If If Len(Trim(CommonDialog1.FileName)) = 0 Then Exit Sub End If Dim i, r, j, k, X As Integer Dim str1 As String Set ex = CreateObject("Excel.Application") Set exwbook = ex.Workbooks.open(CommonDialog1.FileName) ex.Visible = False Set exsheet = exwbook.Worksheets("Sheet1") r = 1 Do Until exsheet.Cells(r, 1) = "" '当条件为真时才终止循环! r = r + 1 Loop Dim rows As Integer rows = 2 'acess表的第一行(非数据行)不导入 For rows = 2 To r - 1 str1 = ex.Cells(rows, 1) If Ado1.Recordset.RecordCount <> 0 Then Ado1.Recordset.MoveFirst End If For j = 0 To Ado1.Recordset.RecordCount - 1 If Ado1.Recordset.Fields("学号") = str1 Then k = 1 j = Ado1.Recordset.RecordCount End If If j < Ado1.Recordset.RecordCount Then Ado1.Recordset.MoveNext End If Next j If k <> 0 Then k = 0 X = 1 '当excel表的“非”最后一个数据与excel表有重复时此时提供给出提示判断 Else Ado1.Recordset.AddNew Ado1.Recordset.Fields("学号") = ex.Cells(rows, 1) Ado1.Recordset.Fields("姓名") = ex.Cells(rows, 2) Ado1.Recordset.Fields("性别") = ex.Cells(rows, 3) Ado1.Recordset.Fields("省份") = ex.Cells(rows, 4) Ado1.Recordset.Fields("学院") = ex.Cells(rows, 5) Ado1.Recordset.Fields("出生年月") = ex.Cells(rows, 6) Ado1.Recordset.Fields("年级") = ex.Cells(rows, 7) Ado1.Recordset.Update End If Next rows End If If X <> 0 Then Ado1.Recordset.AddNew Ado1.Recordset.Fields("学号") = "提示:此EXCEL表有" Ado1.Recordset.Fields("姓名") = "重复数据,但是" Ado1.Recordset.Fields("性别") = "不影响数据导入" Ado1.Recordset.Update End If If Ado1.Recordset.RecordCount <> 0 Then Ado1.Recordset.MoveFirst End If ex.Quit '一定要该语句,不然打开的使用的那个excel文件就双击打不开! End Sub
Private Sub Command1_Click() Set ex_app = CreateObject("Excel.Application") Set ex_book = ex_app.Workbooks.Open(App.Path & "\book1.xls") ex_app.Visible = False Set ex_sheet = ex_book.Worksheets("Sheet1") Dim s_address As String, flg As Integer, s_count As Integer, s_fen As Integer, s_colum As String flg = 0 For Each c In ex_sheet.Range("1:1") If c.Value = Text1.Text Then s_address = c.Address: flg = 1: Exit For Next If flg = 0 Then MsgBox "没有找到符合条件的区域!": GoTo g_exit s_address = Right(s_address, Len(s_address) - 1) s_fen = ex_app.WorksheetFunction.Find("$", s_address, 1) s_colum = Left(s_address, s_fen - 1) Label3.Caption = "当前Excel表中第一行为" & Text1.Text & " 的列中包含 " & Text2.Text & "的单元格的总数为:" & ex_app.WorksheetFunction.CountIf(ex_sheet.Range(s_colum & "2:" & s_colum & "65536"), Text2.Text) g_exit: ex_app.Quit End SubPrivate Sub Form_Load() Label1.Caption = "列定位字符串" Label2.Caption = "要统计的字符串" Label3.Caption = "这里将要显示统计结果!" Text1.Text = "AAA" Text2.Text = "BBB" End Sub
使用后期绑定代码要简单得多: Private Sub Command1_Click() With CreateObject("ADODB.Connection") .OPEN "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Book1.xls;Extended Properties=Excel 8.0;" MsgBox .EXECUTE("SELECT count(AAA) FROM [Sheet1$] where AAA='BBB'")(0) .Close End With End Sub
Private Sub open_Click()
If Ado1.Recordset.RecordCount <> 0 Then
MsgBox "已经有一个打开的文件了。", vbInformation
Else
CommonDialog1.DialogTitle = "Open Microsoft Excel File"
CommonDialog1.Filter = "Microsoft Excel File (*.xls)|*.xls"
CommonDialog1.ShowOpen
If CommonDialog1.CancelError = True Then
Exit Sub
End If
If Len(Trim(CommonDialog1.FileName)) = 0 Then
Exit Sub
End If
Dim i, r, j, k, X As Integer
Dim str1 As String
Set ex = CreateObject("Excel.Application")
Set exwbook = ex.Workbooks.open(CommonDialog1.FileName)
ex.Visible = False
Set exsheet = exwbook.Worksheets("Sheet1")
r = 1
Do Until exsheet.Cells(r, 1) = "" '当条件为真时才终止循环!
r = r + 1
Loop
Dim rows As Integer
rows = 2 'acess表的第一行(非数据行)不导入
For rows = 2 To r - 1
str1 = ex.Cells(rows, 1)
If Ado1.Recordset.RecordCount <> 0 Then
Ado1.Recordset.MoveFirst
End If
For j = 0 To Ado1.Recordset.RecordCount - 1
If Ado1.Recordset.Fields("学号") = str1 Then
k = 1
j = Ado1.Recordset.RecordCount
End If
If j < Ado1.Recordset.RecordCount Then
Ado1.Recordset.MoveNext
End If
Next j
If k <> 0 Then
k = 0
X = 1 '当excel表的“非”最后一个数据与excel表有重复时此时提供给出提示判断
Else
Ado1.Recordset.AddNew
Ado1.Recordset.Fields("学号") = ex.Cells(rows, 1)
Ado1.Recordset.Fields("姓名") = ex.Cells(rows, 2)
Ado1.Recordset.Fields("性别") = ex.Cells(rows, 3)
Ado1.Recordset.Fields("省份") = ex.Cells(rows, 4)
Ado1.Recordset.Fields("学院") = ex.Cells(rows, 5)
Ado1.Recordset.Fields("出生年月") = ex.Cells(rows, 6)
Ado1.Recordset.Fields("年级") = ex.Cells(rows, 7)
Ado1.Recordset.Update
End If
Next rows
End If
If X <> 0 Then
Ado1.Recordset.AddNew
Ado1.Recordset.Fields("学号") = "提示:此EXCEL表有"
Ado1.Recordset.Fields("姓名") = "重复数据,但是"
Ado1.Recordset.Fields("性别") = "不影响数据导入"
Ado1.Recordset.Update
End If
If Ado1.Recordset.RecordCount <> 0 Then
Ado1.Recordset.MoveFirst
End If
ex.Quit '一定要该语句,不然打开的使用的那个excel文件就双击打不开!
End Sub
BBB
BBB
BBB
BBB
我要的是第一行名为AAA下的列的值为BBB的数量
这样的话,我要的统计结果就是4
Set ex_app = CreateObject("Excel.Application")
Set ex_book = ex_app.Workbooks.Open(App.Path & "\book1.xls")
ex_app.Visible = False
Set ex_sheet = ex_book.Worksheets("Sheet1")
Dim s_address As String, flg As Integer, s_count As Integer, s_fen As Integer, s_colum As String
flg = 0
For Each c In ex_sheet.Range("1:1")
If c.Value = Text1.Text Then s_address = c.Address: flg = 1: Exit For
Next
If flg = 0 Then MsgBox "没有找到符合条件的区域!": GoTo g_exit
s_address = Right(s_address, Len(s_address) - 1)
s_fen = ex_app.WorksheetFunction.Find("$", s_address, 1)
s_colum = Left(s_address, s_fen - 1)
Label3.Caption = "当前Excel表中第一行为" & Text1.Text & " 的列中包含 " & Text2.Text & "的单元格的总数为:" & ex_app.WorksheetFunction.CountIf(ex_sheet.Range(s_colum & "2:" & s_colum & "65536"), Text2.Text)
g_exit:
ex_app.Quit
End SubPrivate Sub Form_Load()
Label1.Caption = "列定位字符串"
Label2.Caption = "要统计的字符串"
Label3.Caption = "这里将要显示统计结果!"
Text1.Text = "AAA"
Text2.Text = "BBB"
End Sub
Private Sub Command1_Click()
With CreateObject("ADODB.Connection")
.OPEN "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Book1.xls;Extended Properties=Excel 8.0;"
MsgBox .EXECUTE("SELECT count(AAA) FROM [Sheet1$] where AAA='BBB'")(0)
.Close
End With
End Sub