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"

@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
– Kash, Commented Sep 26, 2019 at 0:35
@TimWilliams ' Worksheets("Keys").Range("D8").Value ' Worksheets("Keys").Range("D7").Value ' Worksheets("Keys").Range("D3").Value ' Worksheets("Keys").Range("D5").Value — Kash
– Kash, Commented Sep 26, 2019 at 0:36
See also stackoverflow.com/questions/26230428/… - the answers there show how the values are assigned. — Tim Williams
– Tim Williams, Commented Sep 26, 2019 at 4:36

David García Bodego · Accepted Answer · 2019-09-26 04:55:05Z

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, 2019 at 4:55

David García Bodego

1,1253 gold badges15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kash Over a year ago

Thanks a bunch :)

Collectives™ on Stack Overflow

Excel VBA to INSERT data using SQL Server procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related