错误 - 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屋！