on this occasion I want to request your valuable help. I have a stored procedure with 3 parameters in SQL and I need to create a Macro in Excel with 3 text fields that will feed the parameters. Then, I must collect the result of the Execution of the Procedure and I need it to be shown from cell A7 on Sheet 1.
So far I have the following code for the Macro:
Sub Botón1()
Dim vNombre_empleado As String
Dim vCargo_empleado As String
Dim vAgencia As String
UserForm1.Show
Cells.Select
Selection.Delete Shift:=x1Up
Range("A7").Select
tx_Conectar = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data
Source=SERVIDOR/Usuario;Initial Catalog=Base_de_Datos;"
With ActiveSheet.QueryTables.Add(Connection.tx_Conectar,
Destination:=Range("A7"))
Query = "EXEC [Base_de_Datos].[dbo].[Procedimiento_Almacenado]" & _
" @NOMBRE_EMPLEADO=" & "" & vNombre_empleado & "" & "," & _
" @CARGO_EMPLEADO=" & "" & vCargo_empleado & "" & "," & _
" @AGENCIA=" & "" & vAgencia & "" & "" & ""
.CommandText = Query
.MsgBox (Query)
.RowsNumbers = False
.FillAdjecentFormulas = False
.PreserveFormating = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
For the CONSULT button of the Form where the data is entered, I have the following code:
Private Sub btnConsultar_Click()
If Me.txtNombre.Text = "null" Or Me.txtNombre.Text = "" Then
tx_Conectar vNombre_empleado = "NULL"
Else
tx_Conectar vNombre_empleado = txtNombre.Text
End If
If Me.txtCargo.Text = "null" Or Me.txtCargo.Text = "" Then
tx_Conectar vCargo_empleado = "NULL"
Else
tx_Conectar vCargo_empleado = txtCargo.Text
End If
If Me.txtAgencia.Text = "null" Or Me.txtAgencia.Text = "" Then
tx_Conectar vAgencia = "NULL"
Else
tx_Conectar vAgencia = txtAgencia.Text
End If
End Sub
My problem is that I still can not see the results, I'm not very proficient in VBA and I do not know what else I need and I honestly do not know what else to do.
I have spent several hours navigating looking for some idea of what to do and I have not found anything, you are my last hope.
Thanks for helping.