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