Excel导入SQLServer奇怪的报错~
在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.
[解决办法]
1.
Data Source=psFilename
檢查這個路徑是不是有問題,應該為EXCEL文件的完整路徑。
2.
The OLE DB provider "Microsoft.Jet.OLEDB.4.0 " for linked server "(null) " does not contain the table "Sheet1$ ".
提示是Sheet1不存在,檢查下EXCEL文件是不是有Sheet1,默認是這個名字,但是有可能會被改動。
[解决办法]
你的Excel文件里面有没有包含Sheet1这1个worksheet?
[解决办法]
1.檢查文件路徑是否正確
2.檢查Excel的sheet的name是否正確
3.檢查這個Excel檔是否已被打開