我想将OPEN对话框中选中的EXCEL文件导入到SQL SERVER中 程序如下:
On Error GoTo err
With CommonDialog1
.FileName = "*.xls"
.Filter = "(Excel)*.xls|*.xls"
.CancelError = True
.ShowOpen
End WithexcelPath = CommonDialog1.FileName Dim cn As New ADODB.Connection
Dim rs As New Recordset
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=sales"
cn.Open
cn.Execute "select * into invest1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=CommonDialog1.FileName;','select * from [Sheet1$]')"
'此处如果使用具体的路径则导入成功,若不是则报错“OLE DB提供程序,想请教!‘'microsoft.jet.oledb.4.0'’报错”
cn.Close
Set cn = Nothing
Adodc1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=sales"
Exit Sub
err:
MsgBox err.Description'另外,我还想请教一个问题,在导入成功的情况下,第二次导入的时候肯定会报错提示表已经存在,我想在第二次导入的时候先判断是否已经存在该表,如果存在则删除再导入,请问用VB怎么实现
On Error GoTo err
With CommonDialog1
.FileName = "*.xls"
.Filter = "(Excel)*.xls|*.xls"
.CancelError = True
.ShowOpen
End WithexcelPath = CommonDialog1.FileName Dim cn As New ADODB.Connection
Dim rs As New Recordset
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=sales"
cn.Open
cn.Execute "select * into invest1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=CommonDialog1.FileName;','select * from [Sheet1$]')"
'此处如果使用具体的路径则导入成功,若不是则报错“OLE DB提供程序,想请教!‘'microsoft.jet.oledb.4.0'’报错”
cn.Close
Set cn = Nothing
Adodc1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=sales"
Exit Sub
err:
MsgBox err.Description'另外,我还想请教一个问题,在导入成功的情况下,第二次导入的时候肯定会报错提示表已经存在,我想在第二次导入的时候先判断是否已经存在该表,如果存在则删除再导入,请问用VB怎么实现
Dim rs As New Recordset
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=sales"
cn.Open
strSql = "select * from sysobjects where [name]='[Sheet1$]'"
'查询SQL库里是否有这个表
Rst.Open strSql, Cn, adOpenKeyset, adLockPessimistic
if rst.recordcout<>0 then
'有表时
cn.execute "DROP TABLE [Sheet1$]"
'删除他
endif
rst.close cn.Execute "select * into invest1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=CommonDialog1.FileName;','select * from [Sheet1$]')"