Kako poklicati a SQL Server Shranjen postopek iz Excelovega VBA

Podatke na strežniku lahko spremenite tako, da v Excelu VBA pregledate zapise na strani odjemalca, jih po potrebi spremenite in shranite nazaj na strežnik.
Učinkovitejši način za to, zlasti če je baza podatkov na oddaljeni lokaciji in je v njej veliko prometa, je, da delo opravite na strani strežnika. Ta vaja zahteva shranjen postopek iz Excela za razvrščanje zaposlenih v starostna obdobja glede na rojstne datume (tj. 18–25 let, 26–35 let itd.), Brez obilne izmenjave podatkov med strežnikom in Excelom.

Ta članek predvideva, da ima bralec prikazan trak za razvijalce in da pozna urejevalnik VBA. V nasprotnem primeru prosimo za Google »Excel Developer Tab« ali »Excel Code Window«.

V vaji so trije elementi:

• Podatkovna tabela tblStaff znotraj baze podatkov TestDB;
• Shranjeni postopek spAgeRange;
• Excel xlsm, ki ga bomo poklicali xlsm. Najdete vzorčno datoteko Excel tukaj

Tabela podatkov

Ustvari bazo podatkov v SQL Server se imenuje DBTest.

Za tabelo nastavite naslednje stolpce tblStaff.

V tabelo kopirajte naslednje:

Shranjeni postopek.

Zaženite ta skript proti TestDB, da ustvarite shranjeni postopek:

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

Shranjeni postopek bo shranjen v razdelku »Programabilnost« v bazi podatkov.

Excel VBA

Vse, kar ostane, je poklicati shranjeno proceduro iz Excela in zagotoviti Datum izplačil parameter »2017/05/25«. Opazili boste, da sem preprosto vtipkal podatke  Datum izplačil kot niz, namesto da se borimo z različnimi formati datumov. Dovolj je preprosto pretvoriti niz v datum z uporabo Pretvarjanje funkcija, če Datum izplačil se uporablja za aritmetične namene.

Ustvarite nov delovni zvezek. Odprite okno kode VBA in vstavite modul.

V meniju Orodja okna s kodo se sklicujte na ustrezno Knjižnica Active X 2.nn za lažjo uporabo podatkovnih objektov.

V okno Code prilepite naslednjo kodo. Ko se bo enkrat aktiviral, se bo povezal z SQL Server, kot je v postopku 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

Na list 1 dodajte gumb in ga dodelite podprocesu “WriteStoredProcedure"

Rezultati

Pritisnite gumb in nato preglejte tblStaff, ki ga je treba posodobiti glede na starost in starostna obdobja. Obdelava je potekala na strani strežnika.

Obnovitev poškodovanih delovnih zvezkov

Če se Excel sesuje, lahko s seboj poruši tudi edino kopijo delovnega zvezka. Excel v precejšnjem odstotku primerov ne more obnoviti poškodovanih delovnih zvezkov; v takem primeru je lahko vse delo, opravljeno od nastanka delovnega zvezka, nepreklicno izgubljeno, razen če imate orodje za to. popravilo Excel datoteke xlsx ali xlsm.

Uvod avtorja:

Felix Hooker je strokovnjak za obnovitev podatkov v DataNumen, Inc., ki je vodilna na svetu na področju tehnologij za obnovitev podatkov, vključno z popravilo rar-ja in sql programske izdelke za obnovitev. Za več informacij obiščite www.datanumen.com