Excel VBA to INSERT data using SQL Server procedure

Question

I need some assistance in writing the following code. I wrote my comments where I am stuck:

Sub UploadDataUsingProc()

    Call CheckFields
    Call Variables.SetVariables
    Call Variables.VariableValues
    Dim sqlCommand As ADODB.Command
    Dim TargetServer As ADODB.Connection
    Set TargetServer = New ADODB.Connection
    TargetServer.CommandTimeout = 99999

    TargetServer.Open ( _
    "driver=" & driver & ";" & _
    "server=" & server & _
    ";database=" & databasename & ";" & _
    ";UID=" & username & _
    ";PWD=" & password & ";" & _
    "Encrypt=YES;")

    ' Set the stored procedure
    Set sqlCommand = New ADODB.Command
    sqlCommand.ActiveConnection = TargetServer
    sqlCommand.CommandType = adCmdStoredProc 'Set sqlCommand to be a stored procedure
    sqlCommand.CommandText = "Proc_ReportMonthlySubmission"

    ' This is where I am not sure how do code further... Following Excel fields needs to be
    ' inserted into a SQL Server table using a stored procedure called "Proc_ReportMonthlySubmission"
    '
    ' Worksheets("Keys").Range("D8").Value
    ' Worksheets("Keys").Range("D7").Value
    ' Worksheets("Keys").Range("D3").Value
    ' Worksheets("Keys").Range("D5").Value

    'Set Procedure = "Exec " & Procedure
    TargetServer.Execute (Procedure)
    MsgBox "Procedure has run"

https://stackoverflow.com/questions/24267080/calling-stored-procedure-vba — Tim Williams, Sep 25 '19 at 23:03
@TimWilliams - I appreciate your example and I reviewed it but where do I enter the spreadsheet cell numbers within the code? So it can pass those values through the store procedure... Following are the cell numbers where I need to get the values and pass them to the Procedure: — Kash, Sep 26 '19 at 00:35
@TimWilliams ' Worksheets("Keys").Range("D8").Value ' Worksheets("Keys").Range("D7").Value ' Worksheets("Keys").Range("D3").Value ' Worksheets("Keys").Range("D5").Value — Kash, Sep 26 '19 at 00:36
See also https://stackoverflow.com/questions/26230428/vba-call-sql-server-stored-procedure-with-two-arguments - the answers there show how the values are assigned. — Tim Williams, Sep 26 '19 at 04:36

score 0 · Accepted Answer · answered Sep 26 '19 at 04:55

0

Try this one... (just follow there, as exposed by Tim Williams)

With sqlCommand
    .Parameters.Item("Whatever_field_you_have").Value = _
                              Worksheets("Keys").Range("D8").Value
    ...

End With

Hope it helps

answered Sep 26 '19 at 04:55

David García Bodego

1,058
3
13
21

Thanks a bunch :) – Kash Sep 26 '19 at 12:46

Excel VBA to INSERT data using SQL Server procedure

1 Answers1