Export data from Excel to MS SQL

[Forestq]

Hi,

I have data as:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]id[/TD]
[TD]name[/TD]
[TD]value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A2[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A3[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]....[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]


How to export the data (Range:A1:C&i) to MS SQL server, to table called "test"?


I know how to get/select the data from MS SQL: TAB DATA/Connections/Workbook Connections/Add... then just add connection string & into command line put: "select * from..."
Then into TAB DATA, from Existing Connection menu, connect to the data, and put range/table/pivot_table, where data should be save.

 

[Forestq]

if you have Vba code, please share...

 

[Forestq]

if someone is interested:

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim conn As ADODB.Connection
Dim rcrds As ADODB.Recordset
Dim iRowNo, i As Long
Dim id_insert, name_insert, value_insert As String

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

   Dim login, passwd, host, dbname As String
   
   login = ""
   passwd = ""
   host = ""
   dbname = ""
  
   Set conn = New ADODB.Connection
    
    With conn
        .ConnectionString = "driver={sql server};Server=" + host + _
                            ";Database=" + dbname + _
                            ";User ID=" + login + _
                            ";Password=" + passwd + _
                            ";Trusted_Connection=False;"
        .Open
    End With 

    iRowNo = 2    
    
        Do Until ws.Cells(iRowNo, 1) = ""
        
                id_insert = ws.Cells(iRowNo, 1)
                name_insert = ws.Cells(iRowNo, 2)
                value_insert = ws.Cells(iRowNo, 3)
                
                conn.Execute "insert into table_name(id, name, value)  values ('" & id_insert& "', '" & name_insert& "', '" & value_insert& "')"
                
                iRowNo = iRowNo + 1
                
        Loop   

   rcrds.Close
   conn.Close
   Set rcrds = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True