Error in VBA Excel-SQL coding as “Application-defined or object-defined error”

[cercig]

I am relatively experienced in VBA coding, but I am totally new in MS SQL server 2008.


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

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.


However, I am getting error like "Application-defined or object-defined error" for the code-line

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 diffent questions, and I think it should work as it is. Does anyone have any idea?


Thanks in advance...

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