Error - SQL coding as "Application-defined or object-defined error"

Question

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 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

I think the issue is with Set listObj = CurSheet.ListObjects(Table1). You are passing in what looks to be a variable named Table1 . Wrap that in quotes like this: Set listObj = CurSheet.ListObjects("Table1"). I also noticed that HeaderRange and DataRange are not declared as variables anywhere (not necessary, just good coding practice) — Soulfire
– Soulfire, Commented Aug 24, 2015 at 12:50
Thanks @Soulfire but it gives the same error for the same line even if I wrote the Table1 in wrapped mode as Set listObj = CurSheet.ListObjects("Table1") I also tried to add the WorkBook at the beginning like MyWorkBook.CurSheet.ListObjects.Add(xlSrcRange, Range("A1:B4"), , xlYes).Name = "Table1", but in that case, I got error like "Object doesn't support this propert or method" — user3714330
– user3714330, Commented Aug 24, 2015 at 12:57
Do you already have a "Table1" defined in your workbook? I can successfully execute the offending line of code without error in a workbook with no Table1 defined. If I then re-run the same line of code (on a different range) I get the "Application-defined ... " error — xidgel
– xidgel, Commented Aug 24, 2015 at 16:13

Bond · Accepted Answer · 2015-08-24 13:14:33Z

1

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

Maybe this is what you meant:

Set MyWorkBook = ActiveWorkbook
Set CurSheet = MyWorkbook.Sheets("Sheet2")

'Create Table in Excel VBA
CurSheet.ListObjects...            ' Correct

answered Aug 24, 2015 at 13:14

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3714330 Over a year ago

Thanks @Bond for the answer, but I tried that alternative too (I even double-checked now), and it gives the same error. I will update the line for the "Sheet2" in the question.

Collectives™ on Stack Overflow

Error - SQL coding as "Application-defined or object-defined error"

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related