Si të telefononi një SQL Server Procedura e ruajtur nga Excel VBA

Të dhënat në një server mund të modifikohen duke ekzaminuar të dhënat "nga ana e klientit" në Excel VBA, duke i ndryshuar ato sipas nevojës dhe duke i ruajtur ato përsëri në server.
Një mënyrë më efikase për ta bërë këtë, veçanërisht nëse baza e të dhënave është në një vend të largët dhe ka shumë trafik të përfshirë, është të kryeni punën "nga ana e serverit". Ky ushtrim thërret një procedurë të ruajtur nga Excel për të kategorizuar punonjësit në grupmosha sipas datave të tyre të lindjes (dmth. 18-25 vjeç, 26-35 vjeç, etj.), pa një shkëmbim të bollshëm të të dhënave midis serverit dhe Excel.

Ky artikull supozon se lexuesi ka të shfaqur shiritin e Zhvilluesit dhe është i njohur me Redaktuesin VBA. Nëse jo, ju lutemi Google "Excel Developer Tab" ose "Excel Code Window".

Ekzistojnë tre elementë të ushtrimit:

• Një tabelë të dhënash tblStafi brenda një baze të dhënash TestDB;
• Një procedurë e ruajtur Gama e hapësirës;
• Një Excel xlsm, të cilin ne do ta quajmë xlsm. Mund të gjendet një shembull i skedarit Excel këtu

Tabela e të Dhënave

Krijoni një bazë të dhënash në SQL Server i quajtur DBTest.

Vendosni kolonat e mëposhtme për një tabelë tblStafi.

Kopjoni sa vijon në tabelë:

Procedura e ruajtur.

Ekzekutoni këtë skript kundër TestDB për të krijuar procedurën e ruajtur:

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

Procedura e ruajtur do të ruhet nën “Programmability” në bazën e të dhënave.

Excel VBA

Gjithçka që mbetet është të telefononi procedurën e ruajtur nga Excel, duke siguruar Data e listës së pagave parametri i “2017/05/25”. Ju do të vini re se kam thjesht të dhëna të shtypura  Data e listës së pagave si një varg në vend që të luftoj me formate të ndryshme të datave. Është mjaft e thjeshtë për të kthyer një varg në një datë duke përdorur Kthej funksion nëse Data e listës së pagave duhet të përdoret për qëllime aritmetike.

Krijo një libër të ri pune. Hapni dritaren e kodit VBA dhe futni një modul.

Nga menyja Vegla e dritares së kodit, referojuni të përshtatshme Biblioteka Active X 2.nn për të lehtësuar përdorimin e objekteve të të dhënave.

Ngjitni kodin e mëposhtëm në dritaren e Kodit. Kjo, pasi të aktivizohet, do të lidhet me SQL Server, sipas nënprocedurës 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

Shtoni një buton në Sheet1 dhe caktojeni atë në nënprocedurën "WriteStoredProcedure"

Rezultatet

Shtypni butonin, më pas ekzaminoni tblStaff, i cili duhet të përditësohet me moshat dhe grupmoshat. Përpunimi është kryer nga ana e serverit.

Rikuperimi i librave të dëmtuar të punës

Nëse Excel pëson probleme, ai mund të marrë edhe kopjen e vetme të librit të punës. Një përqindje e mirë e kohës Excel shpesh nuk është në gjendje të rikuperojë librat e punës të dëmtuar; në një rast të tillë, e gjithë puna e bërë që nga krijimi i librit të punës mund të humbasë në mënyrë të pakthyeshme, përveç nëse keni një mjet për ta bërë këtë. riparimi i Excel skedarët xlsx ose xlsm.

Hyrje e autorit:

Felix Hooker është një ekspert i rikuperimit të të dhënave në DataNumen, Inc., e cila është lider botëror në teknologjitë e rikuperimit të të dhënave, duke përfshirë riparim rar dhe produkte softuerike për rikuperimin sql. Për më shumë informacion vizitoni www.datanumen.com