How to Export excel data to SQL table with VBA?

Please see the following code for Exporting data from Excel to SQL.

Step 1 before inserting this code into you VBE (Alt + F11) is to enable the library reference for Outlook so that you have access to Outlook classes for the below code.

Press (Alt + F11) keys, Go to Tools, References, and make sure "Microsoft ActiveX Data Objects 6.1 Library". if you have an earlier or later version than 6.1 that should be fine as well.

How my worksheet looks like

How my Database table looks like after the import


Sub ExportToSQL()
'Turns off screenupdating for faster script.
Application.ScreenUpdating = False 
'Imports the "SQL" class and library references called ActiveX Data Objects Library.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
'Create the connection string to our server.
sConnString = "Provider=SQLOLEDB;" & _
              "Data Source=Servername;" & _
              "Initial Catalog=Database;" & _
              "Trusted_connection=yes;"'Provider = Fix variable.
'Data Source = Type in your servername.
'Initial Catalog = Type in your database name.'2 options of connecting to the server.
'1) "Trusted_connection=yes;" = This refers to Windows authentication setting in your database.
'2) "User Id=myUsername;Password=myPassword;" = If you are not using Windows authentication, you need to specify your username and password.

'Specify import range to loop.
Dim Rng As Range
EndPlace = Range("A3").End(xlDown).End(xlToRight).Address(False, False)
Set Rng = Application.Range("A3:" & EndPlace)
Dim Row As Range

'Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Setting the name of my SQL table.
MyTable = "TestTable" 'Change this to your SQL table name.'Open the connection and execute.
conn.Open sConnString
'*************************Inserts new data*****************************
'Insert new rows. Throws error on the issue row.
    For Each Row In Rng.Rows
    If Row.Cells(1).Value <> "" Then
        Nr = Nr + 1
        'Collect my info here so my SQL string looks a bit nicer below.
        PersonID = Row.Cells(1).Value 'I can recommend using a Primary 
        Key here instead.
        FirstName = Row.Cells(2).Value
        LastName = Row.Cells(3).Value
        Address = Row.Cells(4).Value
        SQLstringValue = "(" & PersonID & ",'" & FirstName & "', '" & 
        LastName & "', '" & Address & "')"
        SQLstring = "insert into " & MyTable & " values " & 
        'Debug.Print SQLstring ', great way to see if your string turns 
        out correctly.
        Row.Cells(1).Activate 'Activates row for errorhandler.
        On Error GoTo ErrorCode:
            Set rs = conn.Execute(SQLstring, Records) 'Execute upload.
    End If
    Next Row
'***********************END - Inserts new data*************************

    ' Clean up, closing the serverconnection.
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
'Turns on screenupdating now that we are done.
Application.ScreenUpdating = True 

If Nr > 0 Then
    MsgBox ("Nice Job, Import was successful! " & Nr & " rows were imported")

    MsgBox ("Please change your settings. " & Nr & " rows were imported")

End If

Exit Sub 'Stops scripts and displays which row to fix.

    MsgBox ("Incomplete upload. Please fix row: " & ActiveCell.Row & "  
    and try again")
End Sub

Download Excel Workbook here.

Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.

Download ZIP • 70KB

0 views0 comments