在VB中用OPENDATASOURCE方法导入EXCEL表到SQL数据库中已经建好的表中,使用以下语句:
Dim Adocon As ADODB.Connection
Set Adocon = New ADODB.Connection
Adocon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test;Data Source=(local)"
Adocon.ConnectionTimeout = 120
Adocon.Open
Adocon.Execute ("delete from wvmis_t_chiinfo")
Adocon.Execute ("insert into wvmis_t_chiinfo(chinumber,sex) SELECT chinumber,sex FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=d:\download\cs.xls;Extended properties=Excel4.0')...[sheet1$]")
Adocon.Close
Set Adocon = Nothing
运行,系统提示:OLE/DB provider returned message:找不到可安装的ISAM,但是我如果使用SQL的数据导入导出工具先导入一个相应的EXCLE表,再用上面的语句又能执行,希望高手能给个解释。我用的是VB6SP6版本,操作系统是XPSP2。
Dim Adocon As ADODB.Connection
Set Adocon = New ADODB.Connection
Adocon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test;Data Source=(local)"
Adocon.ConnectionTimeout = 120
Adocon.Open
Adocon.Execute ("delete from wvmis_t_chiinfo")
Adocon.Execute ("insert into wvmis_t_chiinfo(chinumber,sex) SELECT chinumber,sex FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=d:\download\cs.xls;Extended properties=Excel4.0')...[sheet1$]")
Adocon.Close
Set Adocon = Nothing
运行,系统提示:OLE/DB provider returned message:找不到可安装的ISAM,但是我如果使用SQL的数据导入导出工具先导入一个相应的EXCLE表,再用上面的语句又能执行,希望高手能给个解释。我用的是VB6SP6版本,操作系统是XPSP2。
希望高手们能提供帮助啊,谢谢了!
1.Microsoft ActiveX Data Objects 2.8 Library '版本根據你的實際情況選擇
2.Microsoft Exce 10.0 Object LibraryOption Explicit
Dim Cn As New ADODB.Connection
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim SheetName() As StringPrivate Sub Command1_Click()
Dim i As Integer, nSheet As Integer
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Open("D:\IP.xls")
objExcel.Visible = False
nSheet = objBook.Sheets.Count
ReDim SheetName(nSheet)
For i = 1 To nSheet
SheetName(i) = objBook.Sheets.Item(i).Name
Next
objBook.Close
objExcel.Quit
Set objExcel = Nothing
Set objBook = Nothing
Set objSheet = Nothing
Dim Cm As New ADODB.Command
Dim Sql As String
For i = 1 To nSheet
Sql = "SELECT * into " & SheetName(i) & " FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source = D:\IP.xls;User ID=;Password=;Extended properties=Excel 5.0')..." & SheetName(i) & "$"
Cm.ActiveConnection = Cn
Cm.CommandText = Sql
Cm.Execute
Next
MsgBox "OK"
End SubPrivate Sub Form_Load()
Cn.ConnectionString = "Driver=SQL Server;Server=192.168.2.68;uid=sa;pwd=password;database=pubs"
Cn.Open
End Sub
以上代碼在
Windows 2000 Server+SQL Server 2000+Office XP中測試通過
得到的Table列與excel中第一行相同,只是類型與長度要再做修改
---------------------------------------
这个错误一般是在你打开了Excel时运行上面代码后给的提示,你把Excel关闭后,再运行