Insert data into SQL Server table from Excel

Back a few months ago I was doing some cool office automation.  Well, at least I thought it was cool. Anyway, I wanted to add to a couple of other blogs that I did and share the following code.  If you want to send data from Microsoft Excel to SQL Server, this would be one way to do it.  Now, lots of folks send data to Excel, that’s no biggie; going the other direction is a bit more of a challenge. While on the project I tried a half-dozen ways, including (believe it or not) calling an SSIS package from a stored procedure called from Excel.  Try this one…works well:

    Application.ScreenUpdating = False
        
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim strConn As String
    Dim p1 As ADODB.Parameter
    Dim p2 As ADODB.Parameter
    Dim p3 As ADODB.Parameter
    Dim p4 As ADODB.Parameter
    Dim p5 As ADODB.Parameter
    Dim p6 As ADODB.Parameter
   
    Dim wks As Worksheet
    Dim cnt As Integer, myrange As Range
   
    Set cmd = New ADODB.Command
    Set cn = New ADODB.Connection
   
    strConn = "PROVIDER=SQLOLEDB;"
    strConn = strConn & "DATA SOURCE=servername;INITIAL CATALOG=databasename;"
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    cn.Open strConn

    Set wks = Worksheets("Accts")
    cmd.ActiveConnection = strConn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "usp_myMergeProcedure"
   
    Set myrange = Columns("b:b")
    cnt = Application.WorksheetFunction.CountA(myrange)
       
    Set p1 = cmd.CreateParameter("cola", adInteger, adParamInput)
    Set p2 = cmd.CreateParameter("colb", adInteger, adParamInput)
    Set p3 = cmd.CreateParameter("colc", adVarWChar, adParamInput, 255)
    Set p4 = cmd.CreateParameter("cold", adVarWChar, adParamInput, 255)
    Set p5 = cmd.CreateParameter("cole", adVarWChar, adParamInput, 255)
    Set p6 = cmd.CreateParameter("colf", adInteger, adParamInput)
   
    cmd.Parameters.Append p1
    cmd.Parameters.Append p2
    cmd.Parameters.Append p3
    cmd.Parameters.Append p4
    cmd.Parameters.Append p5
    cmd.Parameters.Append p6
   
    For introw = 2# To cnt
        p1.value = IsNullFloat(wks.Cells(introw, 1))
        p2.value = IsNullFloat(wks.Cells(introw, 2))
        p3.value = IsNullChar(wks.Cells(introw, 3))
        p4.value = IsNullChar(wks.Cells(introw, 4))
        p5.value = IsNullChar(wks.Cells(introw, 5))
        p6.value = IsNullFloat(wks.Cells(introw, 6))
       
        cmd.Execute
    Next introw
    
    Set wks = Nothing

Thanks for reading,
Lee

-------------------------------