错误 - SQL编码为“应用程序定义或对象定义的错误” [英] Error - SQL coding as "Application-defined or object-defined error"
问题描述
我在VBA编码方面相对有经验,但我在MS SQL Server 2008中是全新的。
I am relatively experienced in VBA coding, but I am totally new in MS SQL server 2008.
我试图将如下所示的Excel表导出到SQL服务器:
I am trying to export an Excel table like below to a SQL server:
A B C D E
1 Name Year ID
2 Jill 2015 17
3 Jack 2012 13
4 Mike 1999 25
5
打开一个连接,然后从Excel表(Sheet2)创建一个表,然后它将此表复制到我的SQL服务器。
My code below, first creates and opens a connection, then creates a table from the Excel-sheet (Sheet2), then it copies this table to my SQL-server.
但是,我收到错误,如应用程序定义或对象定义错误代码行 MyWorkBook.CurSheet.ListObjects.Add (xlSrcRange,Range(A1:B4),,xlYes).Name =Table1
其中我试图定义 Table1
从范围(A1:B4)读取
However, I am getting error like "Application-defined or object-defined error" for the code-line MyWorkBook.CurSheet.ListObjects.Add(xlSrcRange, Range("A1:B4"), , xlYes).Name = "Table1"
where I am trying to define Table1
which is read from the Range("A1:B4")
有趣的是,我发现了相同的代码行几个不同的问题,我认为它应该工作,因为它是。
The interesting point I found the same code-line from several different questions, and I think it should work as it is. Does anyone have any idea?
Private Sub Transtable()
Dim connOk As Boolean
Dim MyWorkBook As Workbook
Dim CurSheet As Worksheet
Dim listObj As ListObject
Dim rs As New ADODB.Recordset
Dim dbclass As New ADODB.Connection
Dim ServerName As String, DataBaseName As String, strSQL As String
Set dbclass = New ADODB.Connection
ServerName = "E43b0784"
DataBaseName = "Tables"
' Specify the OLE DB provider.
dbclass.Provider = "sqloledb"
' Set SQLOLEDB connection properties.
dbclass.Properties("Data Source").Value = ServerName
dbclass.Properties("Initial Catalog").Value = DataBaseName
' Windows NT authentication.
dbclass.Properties("Integrated Security").Value = "SSPI"
dbclass.Open
Set MyWorkBook = ActiveWorkbook
Set CurSheet = MyWorkBook.Sheets("Sheet2")
'Create Table in Excel VBA
CurSheet.ListObjects.Add(xlSrcRange, Range("A1:B4"), , xlYes).Name = "Table1"
Set listObj = CurSheet.ListObjects("Table1") 'Table Name
'get range of Table
HeaderRange = listObj.HeaderRowRange.Address
DataRange = listObj.DataBodyRange.Address
dbclass.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
strSQL = "SELECT * FROM [" & CurSheet.Name & "$" & Replace(DataRange, "$", "") & "];"
rs.Open strSQL, dbclass, adOpenStatic, adLockReadOnly
arrData = rs.GetRows
rs.Close
dbclass.Close
Set rs = Nothing
Set dbclass = Nothing
Set listObj = Nothing
Set CurSheet = Nothing
End Sub
推荐答案
CurSheet
不是 MyWorkBook
。 CurSheet
已经是一个完全限定的表单对象。它不需要使用工作簿对象限定。
CurSheet
is not a member of MyWorkBook
. CurSheet
is already a fully-qualified sheet object on its own. It doesn't need to be qualified with a workbook object.
Set MyWorkBook = ActiveWorkbook
Set CurSheet = Sheet2
'Create Table in Excel VBA
MyWorkBook.CurSheet.ListObjects... ' Error
也许这就是你的意思:
Set MyWorkBook = ActiveWorkbook
Set CurSheet = MyWorkbook.Sheets("Sheet2")
'Create Table in Excel VBA
CurSheet.ListObjects... ' Correct
这篇关于错误 - SQL编码为“应用程序定义或对象定义的错误”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!