macro cannot find the table??

[viva0521]

Hey there, I have following code like this:
Private Sub CommandButton5_Click()
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const WORKSHEETNAME As String = "MIP_solution"
Const TABLENAME As String = "Table1"

Dim conn As Object, rs As Object
Dim tbl As ListObject
Dim Destination As Range
Set Destination = Worksheets.Add.Range("A1")

Set tbl = Worksheets(WORKSHEETNAME ).ListObjects(TABLENAME )


Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"


conn.Open
' On Error GoTo CloseConnection
Set rs = CreateObject("ADODB.Recordset")
With rs
.ActiveConnection = conn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = getSQL(tbl)
.Open


With Destination
tbl.HeaderRowRange.Copy .Range("A1")
.Range("A2").CopyFromRecordset rs
.Parent.ListObjects.Add SourceType:=xlSrcRange, Source:=.Range("A1").CurrentRegion, XlListObjectHasHeaders:=xlYes, TableStyleName:=tbl.TableStyle


End With
End With
CloseRecordset:
rs.Close
Set rs = Nothing
CloseConnection:
conn.Close
Set conn = Nothing
End Sub

I am encountering the subscript out of range error as highlighted in red. I have a sheet named "MIP_solution" and I do name a certain range within this sheet as "Table1", why would the error happen? please advise!

Thanks a lot!!

 

[Fluff]

Is it a proper table, or just a named range?

 

[viva0521]

Is it a proper table, or just a named range?

I selected the range in Excel and name it as "Table1".

 

[Fluff]

In that case it's not a proper table.
You will need to set it up as a proper table. https://www.contextures.com/xlExcelTable01.html

 

[viva0521]

In that case it's not a proper table.
You will need to set it up as a proper table. https://www.contextures.com/xlExcelTable01.html

Hey Fluff

Thanks very much! now I solved the reference problem. But comes up with the Run time error automation error Unspecified error for the ado connection.
Any idea?

Thanks again!

 

[Fluff]

I have never used ADODB connections, so cannot help.
Hopefully someone with knowledge in that area will step in.