Here is my code. If you notice, down within my CrossingFiles function, where I set up my strSQL, I am also performing a select [variable]. The variable (in SQL) will display my error message (set up in the stored procedure). How can I get the results of the error message within VB and then post each message (For each execution of the stored proc) in an error log? Is it possible?

Dim FileToOpen As String

Private Sub Form_Load()

    Dim MyXL As Excel.Application 'create MyXL
    Set MyXL = New Excel.Application
    Dim WkbkName As String

    On Error GoTo ErrorHandler
        Set MyXL = GetObject(, "Excel.Application")

        If Err.Number = 429 Then
            Set appExcel = CreateObject("Excel.Application")
        End If


    FileToOpen = Application.GetOpenFilename 'Opens up explorer

    If FileToOpen = "False" Or FileToOpen = "" Then
        MsgBox "Ensure file is chosen correctly"
    Else
        Workbooks.Open FileName:=FileToOpen 'Opens up the file selected
        Application.Visible = True 'in Excel
    End If


    Application.ActiveWorkbook.Save

    Call CrossingFiles 'Calls function

    Application.ActiveWorkbook.Save
    Application.DisplayAlerts = False
    Application.ActiveWorkbook.Close

    Set MyXL = Nothing

    Unload OpenFile

ErrorHandler: ' Error-handling routine.
Dim StrErr As String
StrErr = Err.Number & " - " & Err.Description
    If Err = 364 Then
      Exit Sub
    End If
    MsgBox (StrErr), vbOKOnly, Error

End Sub

Sub CrossingFiles()

    Dim wsData As Worksheet
    Dim fund As String
    Dim trans_type As String
    Dim security_id As String
    Dim shares As String
    Dim strRangeA As String
    Dim strRangeB As String
    Dim strRangeC As String
    Dim strRangeE As String
    Dim strSQL As String
    Dim i As Integer
    Dim adoConn As ADODB.Connection
    Set wsData = ActiveSheet
    Set adoConn = New ADODB.Connection
    i = 1

    wsData.Rows(1).Delete

    adoConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ODBCsrc;Initial Catalog=main"
    adoConn.Open

    'This will use the new worksheet as the 'trading ground' and will create all of the values
    'which will then be copied over to the original D column
    Dim MyColumns_Range As Range
    Set MyColumns_Range = Range(wsData.Cells(1, "A"), wsData.Cells(1, "A").End(xlDown))
    For Each c In MyColumns_Range
        strRangeA = "A" & i
        strRangeB = "B" & i
        strRangeC = "C" & i
        strRangeE = "E" & i

        fund = Range(strRangeA).Value
        trans_type = Range(strRangeB).Value
        security_id = Range(strRangeC).Value
        shares = Range(strRangeE).Value

        strSQL = "declare @flag varchar(1), @desc varchar(255) "
        strSQL = strSQL + "EXECUTE stored_proc '" & fund & "', '" & trans_type & "', '" & security_id & "', '" & shares & "', '@flag output', '@desc output' "
        strSQL = strSQL + "select @desc"

        adoConn.Execute strSQL, , adCmdText

        i = i + 1

        fund = " "
        trans_type = " "
        security_id = " "
        shares = " "
    Next c

    Set wsData = Nothing
    adoConn.Close

    'deletes the newly created worksheet and sets everything to original status
    'yet with new data
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True

End Sub

I've edited my code (specifically the CrossingFiles function):

Sub CrossingFiles()

    Dim wsData As Worksheet
    Dim fund As String
    Dim trans_type As String
    Dim security_id As String
    Dim shares As String
    Dim strRangeA As String
    Dim strRangeB As String
    Dim strRangeC As String
    Dim strRangeE As String
    Dim strSQL As String
    Dim strDesc As String
    Dim i As Integer
    Dim adoConn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set wsData = ActiveSheet
    Set adoConn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    i = 1

    wsData.Rows(1).Delete

    adoConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ODBCsrc;Initial Catalog=main"
    adoConn.Open

    'This will use the new worksheet as the 'trading ground' and will create all of the values
    'which will then be copied over to the original D column
    Dim MyColumns_Range As Range
    Set MyColumns_Range = Range(wsData.Cells(1, "A"), wsData.Cells(1, "A").End(xlDown))
    For Each c In MyColumns_Range
        strRangeA = "A" & i
        strRangeB = "B" & i
        strRangeC = "C" & i
        strRangeE = "E" & i

        fund = Range(strRangeA).Value
        trans_type = Range(strRangeB).Value
        security_id = Range(strRangeC).Value
        shares = Range(strRangeE).Value

        strSQL = "declare @flag varchar(1), @desc varchar(255) "
        strSQL = strSQL + "EXECUTE stored_proc '" & fund & "', '" & trans_type & "', '" & security_id & "', '" & shares & "', '@flag output', '@desc output' "
        strSQL = strSQL + "select @flag 'flag', @desc 'desc'"

        rst.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly
        strDesc = rst.Fields("flag") & " " & rst.Fields("desc")
        rst.Close

        i = i + 1

        fund = " "
        trans_type = " "
        security_id = " "
        shares = " "
    Next c

    Set wsData = Nothing
    adoConn.Close

    'saves any changes made to the workbook and turns off the prompts ('are you sure')
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True

End Sub

If you notice I am trying desperately to retrieve the resultset from the @flag and the @desc fields. Yet they consistently come back blank. Yet when I run the actual query in SQL I get data. What am I doing wrong?

This is much easier done by:

  1 Create a command object, with the options set to stored procedure.
  2 Create and append to the command object 1 parameter per argument.
  3 Fill in the values of the parameters that the stroed procedure needs to use:
      fund
      trans_type
      security_id
      shares

  4 Run cmd.Execute
  5 In a loop, the parameters can be changed without changing any of the other contents of the command object, and it can again be .Execute(d).

I think I may be a little confused. I created the command object and added the parameters. However I consistently get the same error (object variable or With Block variable not set). What am I doing wrong?

Here is my updated code:

Sub CrossingFiles()

    Dim wsData As Worksheet
    Dim fund As String
    Dim trans_type As String
    Dim security_id As String
    Dim shares As String
    Dim strRangeA As String
    Dim strRangeB As String
    Dim strRangeC As String
    Dim strRangeE As String
    Dim prmfund As ADODB.Parameter
    Dim prmtrans As ADODB.Parameter
    Dim prmsec As ADODB.Parameter
    Dim prmshare As ADODB.Parameter
    Dim prmFlg As ADODB.Parameter
    Dim prmErr As ADODB.Parameter
    Dim strSQL As String
    Dim strDesc As String
    Dim cmd As Command
    Dim i As Integer
    Dim adoConn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim txtLog As String
    Dim flag As String
    Dim desc As String
    Set wsData = ActiveSheet
    Set adoConn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Set cmd = New ADODB.Command
    i = 1
    txtLog = " "
    flag = " "
    desc = " "

    wsData.Rows(1).Delete

    adoConn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ODBCsrc;Initial Catalog=main"
    adoConn.Open

    'This will go through the worksheet, row by row, and send the necessary data to the DB
    Dim MyColumns_Range As Range
    Set MyColumns_Range = Range(wsData.Cells(1, "A"), wsData.Cells(1, "A").End(xlDown))
    For Each c In MyColumns_Range
        strRangeA = "A" & i
        strRangeB = "B" & i
        strRangeC = "C" & i
        strRangeE = "E" & i

        fund = Range(strRangeA).Value
        trans_type = Range(strRangeB).Value
        security_id = Range(strRangeC).Value
        shares = Range(strRangeE).Value

        Set cmd.ActiveConnection = adoConn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "stored_proc"

        'Declare the parameters
        Set prmfund = cmd.CreateParameter(prmfund, adVarChar, adParamInput)
        cmd.Parameters.Append prmfund
        prmfund.Value = fund

        Set prmtrans = cmd.CreateParameter(prmtrans, adVarChar, adParamInput)
        cmd.Parameters.Append prmtrans
        prmtrans.Value = trans_type

        Set prmsec = cmd.CreateParameter(prmsec, adVarChar, adParamInput)
        cmd.Parameters.Append prmsec
        prmsec.Value = security_id

        Set prmshare = cmd.CreateParameter(prmshare, adVarChar, adParamInput)
        cmd.Parameters.Append prmshare
        prmshare.Value = shares

        Set prmFlg = cmd.CreateParameter(prmFlg, adVarChar, adParamOutput)
        cmd.Parameters.Append prmFlg

        Set prmErr = cmd.CreateParameter(prmErr, adVarChar, adParamOutput)
        cmd.Parameters.Append prmErr

        Set rst = cmd.Execute

        i = i + 1

        fund = " "
        trans_type = " "
        security_id = " "
        shares = " "
    Next c

    Set wsData = Nothing
    adoConn.Close

    'saves any changes made to the workbook and turns off the prompts ('are you sure')
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True

End Sub

Do I still open and close or even use my rst?
Also how can I retrieve the output parameters?