Connect SQL Server with Macro in Excel

0

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.

    
asked by Jonathan Velasco 22.11.2018 в 23:38
source

0 answers