Cara Memanggil a SQL Server Prosedur yang disimpan dari Excel VBA

Data pada pelayan dapat diubah suai dengan memeriksa catatan 'sisi klien' di Excel VBA, mengubahnya seperti yang diperlukan, dan menyimpannya kembali ke pelayan.
Cara yang lebih cekap untuk melakukan ini, terutamanya jika pangkalan data berada di lokasi terpencil dan banyak lalu lintas yang terlibat, adalah dengan melakukan kerja 'server-side'. Latihan ini memanggil prosedur tersimpan dari Excel untuk mengkategorikan pekerja mengikut lingkungan umur mengikut tarikh lahir mereka (iaitu 18-25 tahun, 26-35 tahun, dll.), Tanpa pertukaran data yang banyak antara pelayan dan Excel.

Artikel ini menganggap pembaca memaparkan pita Pengembang dan sudah biasa dengan Editor VBA. Sekiranya tidak, sila Google "Tab Pembangun Excel" atau "Tetingkap Kod Excel".

Terdapat tiga elemen latihan:

• Jadual data tblKakitangan dalam pangkalan data TestDB;
• Prosedur yang disimpan Julat spAge;
• Excel xlsm, yang akan kami panggil xlsm. Contoh fail Excel boleh didapati di sini

Jadual Data

Buat pangkalan data di SQL Server dipanggil Ujian DBT.

Sediakan lajur berikut untuk jadual tblKakitangan.

Salin yang berikut ke dalam jadual:

Prosedur yang Disimpan.

Jalankan skrip ini terhadap TestDB untuk membuat prosedur yang tersimpan:

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

Prosedur yang disimpan akan disimpan di bawah "Programmability" dalam pangkalan data.

Excel VBA

Yang tinggal hanyalah memanggil prosedur yang tersimpan dari Excel, dengan menyediakan Tarikh Gaji parameter "2017/05/25". Anda akan maklum bahawa saya hanya menaip data  Tarikh Gaji sebagai rentetan daripada bergulat dengan format tarikh yang berbeza-beza. Cukup mudah untuk menukar rentetan ke tarikh menggunakan Tukar berfungsi sekiranya Tarikh Gaji akan digunakan untuk tujuan aritmetik.

Buat buku kerja baru. Buka tetingkap kod VBA dan masukkan modul.

Dari menu Alat tetingkap kod, rujuk yang sesuai Pustaka Active X 2.nn untuk memudahkan penggunaan objek data.

Tampal kod berikut ke dalam tetingkap Kod. Ini, setelah diaktifkan, akan bersambung ke SQL Server, mengikut sub prosedur 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

Tambahkan butang ke Sheet1 dan tetapkan ke sub prosedur "MenulisProsesed"

Keputusan

Tekan butang, kemudian periksa tblStaff, yang harus dikemas kini dengan umur dan lingkungan umur. Pemprosesan telah berlaku di sisi pelayan.

Memulihkan buku kerja yang rosak

Sekiranya Excel ranap, ia mungkin akan menyebabkan satu-satunya salinan buku kerja anda terhempas bersamanya. Sebahagian besar masa Excel selalunya tidak dapat memulihkan buku kerja yang rosak; dalam kes sedemikian, semua kerja yang dilakukan sejak penciptaan buku kerja mungkin hilang secara kekal, melainkan anda mempunyai alat untuk baiki Excel fail xlsx atau xlsm.

Pengenalan Pengarang:

Felix Hooker adalah pakar pemulihan data di DataNumen, Inc., yang merupakan pemimpin dunia dalam teknologi pemulihan data, termasuk pembaikan rar dan produk perisian pemulihan sql. Untuk maklumat lebih lanjut, lawati www.datanumen.com