"-")
'加入上傳日期時間
str_Date=FormatDateTime(Date(),2)& " " & Time()
myConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strName& ";Extended PropertIEs=Excel 8.0"
'打開連接
myconnection.open myConn_Xsl
'打開表
str_Xsl="select * from ["& strSheetName &"$]"
rsXsl.open str_Xsl,myconnection,1,1
'//姓名,SFZ號碼,證書號碼,簽發日期,有效日期
j=1
Do While not rsXsl.eof
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'取出最大值
str_Sql="select Max(id) as maxId from ceritificate"
rsSql.open str_Sql,myConn,1,3
If Not rsSql.Eof Then
If not isNull(rsSql("maxId")) Then
maxId=Clng(rsSql("maxId"))+1
Else
maxId=1
End if
else
maxId=1
End if
rsSql.close'//關閉對象
'加入成績單
str_Sql=" insert into ceritificate values("&maxId&",'"&rsXsl(0)&"','"&rsXsl(1)&"','"&rsXsl(2)&"','"& str_Kind(0) & "','" & rsXsl(3)& "','"&rsXsl(4) &"','" & str_Date &"')"
cmd.CommandText=str_Sql
cmd.Execute()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
set rsSql=nothing
set myconnection=nothing
set cmd=nothing
end sub
代碼說明:
1)上列代碼是將Excel中的數據信息導入至SQLSERVER中,strKind參數是指證書的種類;
2) 鏈接Excel字符串:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strName& ";Extended PropertIEs=Excel 8.0"
3) str_Xsl="select * from ["& strSheetName &"$]"這條語句是確定是Excel哪一個表簽,即表