Export Data from Excel using VBA to SQL database table

In  this post I will give you the procedure on how to Export Data from Excel using VBA to SQL database table.

EXPORT DATA TO SQL TABLE USING VBA

Sub ExportToTableDirectly()

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Set conn = New ADODB.Connection
conn.ConnectionString = [ConnProp].Cells(1, 1).Value
conn.Open

‘ database table structure is already defined in SQL using management studio

‘In this case, my data is present in a range on the worksheet and I want to push this entire range in separate records of the database table.

For i = 1 To [PushJRng].Rows.Count – 1
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText

‘My database table (JobDetails) has 3 columns (fields) and data in my worksheet range is already in the form: ‘data1’, ‘data2’, ‘data3’
cmd.CommandText = “INSERT INTO JobDetails (JobType,ActvOrTemplate,JobName) ” & _
“Values (” & [PushJRng].Cells(i, 1).Value & “)”
cmd.Execute
Next i

‘You would need to make sure that if you have a primary key column in database table, there is no duplicate data in your worksheet range for the respective column

‘Also make sure that identity specification for every column field in sql table is not set to yes otherwise you will get error

conn.Close
Set conn = Nothing
Set cmd = Nothing
End Sub

Leave a Reply