可以用SQL Server的OPENROWSET、OPENDATASOURCE或OPENQUERY将Excel表作为SQL的表直接打开 然后用Select Into 或 Inset Into Select 语句直接导入SQL中
'On Error GoTo er Dim XL As Excel.Application Dim xlb As Excel.Workbook Set XL = New Excel.Application XL.Workbooks.Open "c:\dt.xls" Set xlb = XL.Workbooks(1) XL.Visible = True dim ret as adodb.recordset set ret=new adodb.recordset ret.open "select * from tableName" if ret.eof then msgbox "open table failed!" exit sub endif ret.addnew ret.field(1)=XL.Cells( 2, 2) .... ret.updateADO要先在工程中引用
'引用ADO(Microsoft ActiveX Data Objects 2.X Library) Private Sub Command1_Click() Dim cn As New ADODB.Connection cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=123;Initial Catalog=dataBase001;Data Source=mySERVICE" cn.CursorLocation = adUseClient cn.Open cn.Execute "select * into table1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=c:\Test.xls;','select * from [Sheet1$]')" End Sub
免费的学习交流网站,欢迎大家访问!
http://www.j2soft.cn/
然后用Select Into 或 Inset Into Select 语句直接导入SQL中
Dim XL As Excel.Application
Dim xlb As Excel.Workbook
Set XL = New Excel.Application
XL.Workbooks.Open "c:\dt.xls"
Set xlb = XL.Workbooks(1)
XL.Visible = True
dim ret as adodb.recordset
set ret=new adodb.recordset
ret.open "select * from tableName"
if ret.eof then
msgbox "open table failed!"
exit sub
endif
ret.addnew
ret.field(1)=XL.Cells( 2, 2)
....
ret.updateADO要先在工程中引用
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=123;Initial Catalog=dataBase001;Data Source=mySERVICE"
cn.CursorLocation = adUseClient
cn.Open
cn.Execute "select * into table1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=c:\Test.xls;','select * from [Sheet1$]')"
End Sub