Ako volať a SQL Server Uložená procedúra z Excelu VBA

Údaje na serveri je možné upraviť preskúmaním záznamov na strane klienta v programe Excel VBA, ich prípadnou zmenou a uložením späť na server.
Efektívnejším spôsobom, ako to robiť, najmä ak je databáza na vzdialenom mieste a je tu veľa prenosu, je robiť prácu „na strane servera“. Toto cvičenie volá uloženú procedúru z Excelu na kategorizáciu zamestnancov do vekových skupín podľa ich dátumov narodenia (tj. 18-25 rokov, 26-35 rokov atď.) Bez hojnej výmeny údajov medzi serverom a Excelom.

Tento článok predpokladá, že čitateľ má zobrazenú pásku pre vývojárov a je oboznámený s editorom VBA. Ak nie, navštívte Google kartu „Vývojár Excel“ alebo „Okno kódu Excel“.

Cvičenie má tri prvky:

• Tabuľka údajov tblZamestnanci v rámci databázy TestDB;
• Uložená procedúra spAgeRange;
• Excel xlsm, ktorý zavoláme xlsm. Vzorový súbor programu Excel sa nachádza tu

Tabuľka dát

Vytvorte databázu v SQL Server tzv DBTest.

Pre tabuľku nastavte nasledujúce stĺpce tblZamestnanci.

Skopírujte do tabuľky toto:

2017/05/25	1	hnedý	J	1946/12/02			M
2017/05/25	2	šikovný	A	1976/03/26			F
2017/05/25	3	plavba	T	1962/07/03			M
2017/05/25	4	Lohan	L	1986/07/02			F
2017/05/25	5	Fredricksen	F	1964/03/15			M
2017/05/25	6	Snyder	L	1968/07/05			F
2017/05/25	7	Lipnicki	J	1983/11/25			M
2017/05/25	8	vysávač	S	2002/12/08			F
2017/05/25	9	Watson	E	1990/04/15			F

Uložený postup.

Spustením tohto skriptu proti TestDB vytvorte uloženú procedúru:

POUŽITIE [TestDB] GO / ****** Objekt: StoredProcedure [dbo]. [SpAgeRange] Dátum skriptu: 2017/05/10 12:16:28 ****** / NASTAVIŤ ANSI_NULLS ZÍSKAŤ NASTAVTE QUOTED_IDENTIFIER NA GO VYTVORIŤ POSTUP [dbo]. [SpAgeRange] @PayrollDate varchar (50) AKO ZAČAŤ ZAPOJIŤ NOCOUNT; UPDATE tblStaff SET Age = CONVERT (int, DATEDIFF (day, DateOfBirth, GETDATE ()) / 365.25, 0) WHERE tblStaff.PayrollDate = @PayrollDate Update tblStaff set AgeRange = '> 56', kde vek> = 56 a PayrollDate = @PayrollDate Aktualizácia sady tblStaff AgeRange = '46 až 55 ', kde vek> = 46 a vek <56 a PayrollDate = PayrollDate Aktualizácia tblStaff nastavená AgeRange = '39 až 45', kde Vek> = 39 a vek <46 a PayrollDate = @PayrollDate Aktualizácia sady tblStaff AgeRange = '31 až 38 ', kde Vek> = 30 a Vek <39 a PayrollDate = @PayrollDate Aktualizácia tblStaff nastavená AgeRange = '25 až 30', kde Vek> = 25 a Vek <30 a PayrollDate = @PayrollDate Aktualizácia tblStaff nastavená AgeRange = '18 až 24 ', kde vek> = 18 a vek <25 a PayrollDate = @PayrollDate aktualizácia tblStaff nastavená AgeRange =' <18 ', kde vek <18 a PayrollDate = @PayrollDate END

Uložená procedúra sa uloží pod databázou „Programovateľnosť“.

Excel vba

Zostáva iba zavolať uloženú procedúru z Excelu a poskytnúť Dátum výplaty parameter „2017/05/25“. Všimnete si, že som jednoducho zadal údaje  Dátum výplaty ako reťazec a nie zápasiť s rôznymi formátmi dátumu. Je dosť jednoduché previesť reťazec na dátum pomocou znaku Konvertovať funkcia ak Dátum výplaty sa má používať na aritmetické účely.

Vytvorte nový zošit. Otvorte okno s kódom VBA a vložte modul.

V ponuke Nástroje v okne kódu vyhľadajte príslušné odkazy Active X 2.nn library na uľahčenie používania dátových objektov.

Vložte nasledujúci kód do okna Kód. Po aktivácii sa pripojíte k SQL Server, ako je uvedené v procedúre ConnectDatabase

'Všetky' verejné 'v prípade, že je kód rozložený do niekoľkých modulov. 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 "Zavolať ConnectDatabase pri chybe GoTo errSP strSQL =" EXEC spAgeRange '"& PayrollDate &"' "connDB.Execute (strSQL) Exit Sub errSP: MsgBox Err.Popis End Sub Sub ConnectDatabase () Ak connDB.State = 25 Potom connDB.Close On Error GoTo ErrConnect strServer = "SERVERNAME" 'Názov alebo IP adresa SQL Server
     strDBase = "TestDB" strUser = "" 'nechajte toto pole pre autentifikáciu Windows prázdne strPwd = "" Ak strPwd> "" Potom strConnectionstring = "DRIVER = {SQL Server}; Server = "& strServer &"; Databáza = "& strDBase &"; Uid = "& strUser &"; Pwd = "& strPwd &"; Časový limit pripojenia = 30; "Else strConnectionstring =" DRIVER = {SQL Server}; SERVER = "& strServer &"; Trusted_Connection = yes; DATABASE = "& strDBase 'autentifikácia systému Windows Koniec Ak connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Ukončiť Sub ErrConnect: MsgBox Err.Popis Koniec Sub

Pridajte tlačidlo do Listu1 a priraďte ho k podprocesu “WriteStoredProcedure"

Výsledky

Stlačte tlačidlo a potom preskúmajte tblStaff, ktorý by sa mal aktualizovať podľa vekových skupín a vekových skupín. Spracovanie prebieha na strane servera.

Obnova poškodených zošitov

Ak dôjde k zlyhaniu programu Excel, môže to so sebou viesť aj vašu jedinú kópiu zošita. Dobré percento času, keď program Excel často nedokáže obnoviť poškodené zošity; v takom prípade môže byť všetka práca vykonaná od vytvorenia zošita neodvolateľnácably lost, pokiaľ nemáte nástroj na opraviť Excel súbory xlsx alebo xlsm.

Úvod autora:

Felix Hooker je expert na obnovu dát v DataNumen, Inc., ktorá je svetovým lídrom v oblasti technológií obnovy dát, vrátane rar oprava a softvérové ​​produkty na obnovenie sql. Pre viac informácií navštívte www.datanumen. S