Paano Tumawag a SQL Server Nakaimbak na Pamamaraan mula sa Excel VBA

Ang data sa isang server ay maaaring mabago sa pamamagitan ng pagsusuri sa talaan ng 'client-side' sa Excel VBA, binabago ang mga ito ayon sa kinakailangan, at mai-save ito pabalik sa server.
Ang isang mas mahusay na paraan ng paggawa nito, lalo na kung ang database ay nasa isang malayuang lokasyon at maraming kasangkot na trapiko, ay ang gawin ang 'server-side' ng trabaho. Ang ehersisyo na ito ay tumatawag sa isang nakaimbak na pamamaraan mula sa Excel upang maikategorya ang mga empleyado sa mga saklaw ng edad ayon sa kanilang mga petsa ng kapanganakan (ie 18-25 taon, 26-35 taon, atbp.), Nang walang maraming pagpapalitan ng data sa pagitan ng server at Excel.

Ipinapalagay ng artikulong ito na ang mambabasa ay ipinapakita ang Developer ribbon at pamilyar sa VBA Editor. Kung hindi, mangyaring Google "Excel Developer Tab" o "Excel Code Window".

Mayroong tatlong mga elemento sa ehersisyo:

• Isang talahanayan ng data tblStaff sa loob ng isang database TestDB;
• Isang nakaimbak na pamamaraan spAgeRange;
• Isang Excel xlsm, na tatawagin namin xlsm. Ang isang sample na Excel file ay maaaring matagpuan dito

Table Data

Lumikha ng isang database sa SQL Server tinatawag DBTest.

I-set up ang mga sumusunod na haligi para sa isang talahanayan tblStaff.

Kopyahin ang sumusunod sa talahanayan:

Nakaimbak na Pamamaraan.

Patakbuhin ang script na ito laban sa TestDB upang likhain ang nakaimbak na pamamaraan:

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

Ang nakaimbak na pamamaraan ay nai-save sa ilalim ng "Programmability" sa database.

Excel vba

Ang natitira lamang ay tawagan ang nakaimbak na pamamaraan mula sa Excel, na ibibigay ang PayrollDate parameter ng “2017/05/25”. Mapapansin mong mayroon akong simpleng nai-type na data  PayrollDate bilang isang string sa halip na makipagbuno sa iba't ibang mga format ng petsa. Ito ay sapat na simple upang mai-convert ang isang string sa isang petsa gamit ang Palitan pagpapaandar kung PayrollDate ay gagamitin para sa mga hangaring aritmetika.

Lumikha ng isang bagong workbook. Buksan ang window ng VBA code at magpasok ng isang module.

Mula sa menu ng Mga tool window ng code, i-refer ang naaangkop Aktibidad ng Aktibong X 2.nn upang mapadali ang paggamit ng mga data object.

Idikit ang sumusunod na code sa window ng Code. Ito, sa sandaling naaktibo, ay kumokonekta sa SQL Server, ayon sa sub na pamamaraan ng 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

Magdagdag ng isang pindutan sa Sheet1 at italaga ito sa sub pamamaraan na "IsulatSistulangPamamaraan"

Ang Resulta

Pindutin ang pindutan, pagkatapos suriin ang tblStaff, na dapat na ma-update sa mga edad at saklaw ng edad. Ang pagpoproseso ay naganap sa server-side.

Pag-recover ng mga nasirang workbook

Kung sakaling mag-crash ang Excel, maaaring kasama nitong masira ang nag-iisang kopya ng workbook mo. Madalas, hindi na mababawi ng Excel ang mga nasirang workbook; sa ganitong kaso, lahat ng gawaing nagawa simula nang malikha ang workbook ay maaaring tuluyang mawala, maliban na lang kung mayroon kang tool para dito. ayusin ang Excel xlsx o xlsm file.

Panimula ng May-akda:

Si Felix Hooker ay isang dalubhasa sa pagbawi ng data sa DataNumen, Inc., na pinuno ng mundo sa mga teknolohiya sa pagbawi ng data, kasama ang pagkukumpuni ng rar at mga produkto ng software sa pag-recover ng sql. Para sa karagdagang impormasyon pagbisita www.datanumen. Sa