CommonDialog1.Filter = "Excel文件|*.xls|所有文件|*.*"
CommonDialog1.FilterIndex = 1
CommonDialog1.Action = 1
thefilename = CommonDialog1.FileName
If thefilename <> "" Then
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim x As Integer
Set oExcel = New Excel.Application
oExcel.Visible = False '设置EXCEL可见
'Set oBook = oExcel.Workbooks.Open(App.Path & "\test.xls") '打开EXCEL工作簿
Set oBook = oExcel.Workbooks.Open(thefilename)
Set oSheet = oBook.Worksheets(1) '打开EXCEL工作表
oSheet.Activate '激活工作表
x = oSheet.UsedRange.Rows.Count
' oExcel.Visible = True
' oExcel.UserControl = True
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Integer
Dim str, estr, sql, sstr As String
On Error Resume Next
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=MSDAORA.1;User ID=kaf;password=kaf;Data Source=kaf;Persist Security Info=False"
conn.CursorLocation = adUseClient
conn.Open
rs.CursorLocation = adUseClient
rs.ActiveConnection = conn
sql = "select cardno,student_name, sex,birthday from ke_school_student where company_id='" + txt_school.Text + "' and class_name ='" + txt_class.Text + "' "
rs.Open sql, conn, 2, 3
Set DataGrid2.DataSource = rs
DataGrid2.Refresh
Debug.Print rs.RecordCount
If x - 1 = rs.RecordCount Then
For i = 0 To rs.RecordCount - 1
listPrint ("学生姓名: " & oSheet.Cells(i + 2, 2) & " 学生卡号:" & oSheet.Cells(i + 2, 9) & " 处于第" & i + 1 & "个位置")
tmp(i) = oSheet.Cells(i + 2, 9)
tmp(UBound(tmp)) = tmp(i)
ReDim Preserve tmp(UBound(tmp) + 1)
Next
Else
MsgBox "数据表中的数据总数: " & rs.RecordCount & " 不等于excel表中的数据: " & x - 1 & " 请检查校对!!!"
End If
Debug.Print rs.RecordCount & UBound(tmp)
If UBound(tmp) = rs.RecordCount Then '此数组中己包含要替换的新卡号
rs.MoveFirst
For i = 0 To rs.RecordCount - 1
rs.Fields(0) = tmp(i)
Debug.Print "记录集中的" & rs.Fields(0) & "tmpid数组中的值:" & tmp(i)
rs.Update
rs.MoveNext
Next i
End If
' rs.Close
sql = "select cardno from ke_school_student where company_id='" + txt_school.Text + "' and class_name ='" + txt_class.Text + "' "
rs.Open sql, conn, 1, 1
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
oExcel.DisplayAlerts = False '强行退出excel进程
oExcel.Quit
Else
Exit Sub
End If
End Sub
CommonDialog1.Filter = "Excel文件|*.xls|所有文件|*.*"
CommonDialog1.FilterIndex = 1
CommonDialog1.Action = 1
thefilename = CommonDialog1.FileName
If thefilename <> "" Then
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set oExcel = New Excel.Application
oExcel.Visible = True '设置EXCEL可见
'Set oBook = oExcel.Workbooks.Open(App.Path & "\test.xls") '打开EXCEL工作簿
Set oBook = oExcel.Workbooks.Open(thefilename)
Set oSheet = oBook.Worksheets(1) '打开EXCEL工作表
oSheet.Activate '激活工作表
For i = 0 To UBound(tmpid) - 1
oSheet.Cells(i + 2, 9) = tmpid(i)
Next i
oExcel.Visible = True
oExcel.UserControl = True
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Integer
Dim str, estr, sql, sstr As String
On Error Resume Next
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=MSDAORA.1;User ID=kaf;password=kaf;Data Source=kaf;Persist Security Info=False"
conn.CursorLocation = adUseClient
conn.Open
rs.CursorLocation = adUseClient
rs.ActiveConnection = conn
sql = "select cardno from ke_school_student where company_id='0011' and class_name ='高一(1)班' "
Set rs = conn.Execute(sql)
MsgBox rs.RecordCount & " and " & UBound(tmpid)
If UBound(tmpid) = rs.RecordCount Then
Do While rs.EOF <> True
estr = oSheet.Cells(r + 2, 2)
sstr = oSheet.Cells(r + 2, 3)
str = tmpid(r)
Debug.Print estr & "cardno is: " & str; " sex is: " & sstr
sql = "update ke_school_student set cardno = str where student_name = '" + estr + "' and sex = '" + sstr + "' "
Set rs = conn.Execute(sql)
rs.MoveNext
r = r + 1
Loop
End If
rs.Close
sql = "select cardno from ke_school_student where company_id='0011' and class_name ='高一(1)班' "
Set rs = conn.Execute(sql)
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
Else
Exit Sub
End If
rstOra.Open "select cardno from ke_school_student where company_id='0011' and class_name ='高一(1)班' " '查询出这个班级的所有卡号
If UBound(tmpid) = rstOra.RecordCount Then '此数组中己包含要替换的新卡号
Do While rstOra.EOF <> True
rstOra.Open "update ke_school_student set cardno = '数组????' where company_id='0011' and class_name ='高一(1)班'"
rstOra.MoveNext
Loop
End If