copying existing table values to new table using vba macro in excel`

See more:

SQL-Server

SQL-Server-2008

, +

Hello I am trying to copy the values which is in excel sheet to new table in same database using Select * INTO query. The data which is extracted to excel is from other table and after editing some columns in excel i need the excel sheet to be sent back to sql database with new table creation.

I got problem with m code which says error : "Run- time error '-2147217900 ' Automation Error"

Please help me out . I am using VBA Macros to import and export the table values to SQL from Excel.

Here is my Code

Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim sSQL As String
Dim lRow As Long, lCol As Long

sConnString = "Provider=sqloledb;Server=;Database=mycon;User Id=;Password="

Set adoCN = CreateObject("ADODB.Connection")

adoCN.Open sConnString

'Assumes that you have Field1, Field2 and Field3 in columns A, B and C
'For this example we can assume that the data exists on Sheet1, with a header on row
'1 and data in rows 2-11
'Also assume that the fields are defined as character (e.g. varchar or char)
'Text values must be enclosed in apostrophes whereas numeric values should not.

'adoCN.Open

For lRow = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

sSQL = "SELECT * INTO TestTable FROM Sheets(1)$"

***adoCN.Execute sSQL***

Next lRow

adoCN.Close

Set adoCN = Nothing


'On Error GoTo 0

error at : adoCN.Execute sSQL