Come chiamare un SQL Server Procedura memorizzata da Excel VBA

I dati su un server possono essere modificati esaminando i record "lato client" in Excel VBA, modificandoli secondo necessità e salvandoli nuovamente sul server.
Un modo più efficiente per farlo, in particolare se il database si trova in una posizione remota e c'è molto traffico coinvolto, è fare il lavoro "lato server". Questo esercizio chiama una procedura memorizzata da Excel per classificare i dipendenti in fasce di età in base alla loro data di nascita (ad esempio 18-25 anni, 26-35 anni, ecc.), senza un copioso scambio di dati tra il server ed Excel.

Questo articolo presuppone che il lettore abbia visualizzato la barra multifunzione dello sviluppatore e abbia familiarità con l'editor VBA. In caso contrario, Google "Excel Developer Tab" o "Excel Code Window".

Ci sono tre elementi per l'esercizio:

• Una tabella di dati tblStaff all'interno di un database DB di prova;
• Una procedura memorizzata spAgeRange;
• Un Excel xlsm, che chiameremo XLSM. È possibile trovare un file Excel di esempio Qui.

Tabella dati

Crea un database in SQL Server detto DBTest.

Imposta le seguenti colonne per una tabella tblStaff.

Copia quanto segue nella tabella:

Procedura memorizzata.

Esegui questo script su TestDB per creare la stored procedure:

USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[spAgeRange] Script Date: 2017/05/10 12:16:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spAgeRange]

    @PayrollDate varchar(50)
 
AS
BEGIN

    SET NOCOUNT ON;
    UPDATE tblStaff SET Age = CONVERT(int, DATEDIFF(day, DateOfBirth, GETDATE()) / 365.25, 0) 
    WHERE tblStaff.PayrollDate = @PayrollDate
 
    Update tblStaff set AgeRange = '>56' where Age >= 56 and PayrollDate = @PayrollDate

    Update tblStaff set AgeRange = '46 to 55' where Age >= 46 and Age < 56 and PayrollDate = PayrollDate 
 
    Update tblStaff set AgeRange = '39 to 45' where Age >= 39 and Age < 46 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '31 to 38' where Age >= 30 and Age < 39 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '25 to 30' where Age >= 25 and Age < 30 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '18 to 24' where Age >= 18 and Age < 25 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '<18' where Age < 18 and PayrollDate = @PayrollDate

END

La procedura memorizzata verrà salvata in "Programmabilità" nel database.

Excel VBA

Non resta che chiamare la stored procedure da Excel, fornendo il file Data libro paga parametro del “2017/05/25”. Noterai che ho semplicemente digitato i dati  Data libro paga come una stringa piuttosto che lottare con diversi formati di data. È abbastanza semplice convertire una stringa in una data usando l' convertire funzione se Data libro paga deve essere utilizzato per scopi aritmetici.

Crea una nuova cartella di lavoro. Apri la finestra del codice VBA e inserisci un modulo.

Dal menu Strumenti della finestra del codice, fare riferimento al file appropriato Libreria ActiveX 2.nn per facilitare l'uso di oggetti di dati.

Incolla il codice seguente nella finestra Codice. Questo, una volta attivato, si collegherà a SQL Server, come da procedura secondaria ConnectDatabase

'All "public" in case the code is spread over several modules.
Public connDB As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
Public strConnectionstring As String
Public strServer As String
Public strDBase As String
Public strUser As String
Public strPwd As String
Public PayrollDate As String

Sub WriteStoredProcedure()
     PayrollDate = "2017/05/25"
     Call ConnectDatabase
     On Error GoTo errSP
     strSQL = "EXEC spAgeRange '" & PayrollDate & "'"
     connDB.Execute (strSQL)
     Exit Sub
errSP:
MsgBox Err.Description
End Sub

Sub ConnectDatabase()
     If connDB.State = 1 Then connDB.Close
     On Error GoTo ErrConnect
     strServer = "SERVERNAME" ‘The name or IP Address of the SQL Server
     strDBase = "TestDB"
     strUser = "" 'leave this blank for Windows authentication
     strPwd = ""

     If strPwd > "" Then
         strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPwd & ";Connection Timeout=30;"
     Else
         strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" &    strDBase 'Windows authentication
     End If
     connDB.ConnectionTimeout = 30
     connDB.Open strConnectionstring
Exit Sub
ErrConnect:
     MsgBox Err.Description
End Sub

Aggiungi un pulsante al Foglio1 e assegnalo alla procedura secondaria "WriteStoredProcedure".

I risultati

Premi il pulsante, quindi esamina tblStaff, che dovrebbe essere aggiornato con età e fasce di età. L'elaborazione è avvenuta lato server.

Ripristino di cartelle di lavoro danneggiate

Se Excel si blocca, potrebbe benissimo perdere anche l'unica copia della cartella di lavoro. In una buona percentuale dei casi Excel non è in grado di recuperare le cartelle di lavoro danneggiate; in tal caso, tutto il lavoro svolto dalla creazione della cartella di lavoro potrebbe andare irrimediabilmente perso, a meno che non si disponga di uno strumento per riparare Excel file xlsx o xlsm.

Introduzione dell'autore:

Felix Hooker è un esperto di recupero dati in DataNumen, Inc., che è il leader mondiale nelle tecnologie di recupero dati, tra cui riparazione rar e prodotti software di recupero SQL. Per maggiori informazioni visita www.datanumen.com