在SQLServer 的服务器端运行如下程序:Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=dsname;" & _
"Initial Catalog=master;User ID=sa;Password=admin"'Import by using OPENDATASOURCE.strSQL = "insert into table1 (column1,column2) " & _
"select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=psFilename;" & _
"Extended Properties=Excel 8.0')...[Sheet1$]"'Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords-------------------------------------------
报错:The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Sheet1$". The table either does not exist or the current user does not have permissions on that table.
cn.Open "Provider=SQLOLEDB;Data Source=dsname;" & _
"Initial Catalog=master;User ID=sa;Password=admin"'Import by using OPENDATASOURCE.strSQL = "insert into table1 (column1,column2) " & _
"select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=psFilename;" & _
"Extended Properties=Excel 8.0')...[Sheet1$]"'Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords-------------------------------------------
报错:The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Sheet1$". The table either does not exist or the current user does not have permissions on that table.
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Sheet1$". 提示是Sheet1不存在,檢查下EXCEL文件是不是有Sheet1,默認是這個名字,但是有可能會被改動。
2.檢查Excel的sheet的name是否正確
3.檢查這個Excel檔是否已被打開
路径我检查过了,完全正确,是全路径形如 C:\Foldername\excelname.xls
Sheet1$也是有的。
to fishmans(金脚指) ( )
这个excel文件在我执行上述代码的时候没有打开。而且也没有excel的进程存在。但是就是报这样奇怪的错,郁闷a ~~之前报一个错是说This module has been ed OFF. Turn on 'Ad Hoc Distributed Queries' in order to be able to access the module.
运行完sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
就抱现在这个错~
錯誤提示比較明顯,就是找不到Sheet1。要麼是這個table不存在,要麼是你沒有權限打開那個table。你可以將Data Source=psFilename中Data Source後面的地址復制到IE地址欄中,檢測下是否有筆誤。
我也知道错误的意思
我也按照你说的把地址copy到地址栏中了,能打开的。
Sheet1$的拼写(包括大小写)也检查过了。现在可能的问题是说 没有权限,我不知道current user指代哪一个user?我觉得是指打开excel文件的权限,我是以本机的administrator的权限登陆的,连SQLServer也是用管理员的账号。
通过http://community.csdn.net/Expert/topic/4926/4926468.xml?temp=.1773645曲线救国式的方法搞定了。谢谢大家,接分~
问题出在这句:
strSQL = "insert into table1 (column1,column2) " & _
"select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=psFilename;" & _
"Extended Properties=Excel 8.0')...[Sheet1$]"这里面的变量psFilename没有拼接,按字符串处理了~~~,应该是:
strSQL = "insert into table1 (column1,column2) " & _
"select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=" & psFilename & ";" & _
"Extended Properties=Excel 8.0')...[Sheet1$]"很多看来很奇怪的问题,一定是有原因的,只是往往当时就是像瞎了眼似的,呵呵