Dim cnExcel As New ADODB.Connection
Dim recExcel As New ADODB.Recordset
cnExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.xls;" & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
cnExcel.Open
cnExcel.Execute ("select A.* into [Sheet1$] FROM OPENROWSET('SQLOLEDB','myServer';'sa';' ','SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') as A") '''''''错误
cnExcel.Close
Set cnExcel = Nothing
Set recExcel = Nothing
提示错误from 子句有错误
Dim recExcel As New ADODB.Recordset
cnExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.xls;" & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
cnExcel.Open
cnExcel.Execute ("select A.* into [Sheet1$] FROM OPENROWSET('SQLOLEDB','myServer';'sa';' ','SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') as A") '''''''错误
cnExcel.Close
Set cnExcel = Nothing
Set recExcel = Nothing
提示错误from 子句有错误
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=jtaf;Password=登陆密码;Initial Catalog=sql里的数据库;Data Source=sql服务器别名或IP"
cn.CursorLocation = adUseClient
cn.Open cn.Execute("select * into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\Test.xls;','select * from [Sheet1$]') from table1")
---------------------------------
用1楼的代码可以的
关键字OpenRowSet附近有错误
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\test.xls;Extended Properties='Excel 8.0;HDR=Yes'" cn.Execute ("insert INTO [odbc;Driver={SQL Server};" & _
"Server=ljx;Database=test;" & _
"UID=sa;PWD=].temp " & _
"select * FROM [Sheet1$]")
cn.Close
Set cn = Nothing
End Sub
------------------------------------
还是写错了,后面没有from employee ,from应该写到OpenRowSet前面来,这样:cnExcel.Execute "Select * Into sqlNewTable From OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\Test.xls;','Select * From [Sheet1$]')
保存在了c盘,验证通过(SQL SERVER 2000)
谢谢faysky2().....,明天揭帖.....