Data di server dapat dimodifikasi dengan memeriksa catatan 'sisi klien' di Excel VBA, mengubahnya sesuai kebutuhan, dan menyimpannya kembali ke server.
Cara yang lebih efisien untuk melakukan ini, terutama jika database berada di lokasi yang jauh dan ada banyak lalu lintas yang terlibat, adalah dengan melakukan pekerjaan 'sisi server'. Latihan ini memanggil prosedur tersimpan dari Excel untuk mengkategorikan karyawan ke dalam rentang usia sesuai dengan tanggal lahir mereka (yaitu 18-25 tahun, 26-35 tahun, dll.), Tanpa pertukaran data yang berlebihan antara server dan Excel.
Artikel ini mengasumsikan bahwa pembaca memiliki pita Pengembang yang ditampilkan dan terbiasa dengan Editor VBA. Jika tidak, harap Google "Tab Pengembang Excel" atau "Jendela Kode Excel".
Ada tiga elemen latihan:
- Tabel data tblStaf dalam database UjiDB;
- Sebuah prosedur yang tersimpan spAgeRange;
- Excel xlsm, yang akan kami sebut xlsm. Contoh file Excel dapat ditemukan di sini
Data Tabel
Buat database di SQL Server bernama Tes DBT.
Siapkan kolom berikut untuk tabel tblStaf.

Salin yang berikut ini ke dalam tabel:
| 2017/05/25 | 1 | Coklat | J | 1946/12/02 | M | ||
| 2017/05/25 | 2 | Cerdas | A | 1976/03/26 | F | ||
| 2017/05/25 | 3 | pelayaran | 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 | Lipnik | J | 1983/11/25 | M | ||
| 2017/05/25 | 8 | Alat pengisap debu | S | 2002/12/08 | F | ||
| 2017/05/25 | 9 | Watson | E | 1990/04/15 | F |
Prosedur Tersimpan.
Jalankan skrip ini terhadap TestDB untuk membuat prosedur 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 tersimpan akan disimpan di bawah "Programmability" dalam database.
excel-vba
Yang tersisa hanyalah memanggil prosedur tersimpan dari Excel, menyediakan file Tanggal Penggajian parameter “2017/05/25”. Anda akan perhatikan bahwa saya hanya mengetik data Tanggal Penggajian sebagai string daripada bergulat dengan berbagai format tanggal. Cukup sederhana untuk mengubah string menjadi tanggal menggunakan mengubah berfungsi jika Tanggal Penggajian akan digunakan untuk tujuan aritmatika.
Buat buku kerja baru. Buka jendela kode VBA dan masukkan modul.
Dari menu Tools jendela kode, referensi yang sesuai Pustaka ActiveX 2.nn untuk memfasilitasi penggunaan objek data.
Tempel kode berikut ke jendela Kode. Ini, setelah diaktifkan, akan terhubung ke SQL Server, sesuai 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 tombol ke Sheet1 dan tetapkan ke sub prosedur "TulisTersimpanProsedur"
Hasil
Tekan tombol, lalu periksa tblStaff, yang harus diperbarui dengan usia dan rentang usia. Pemrosesan dilakukan di sisi server.
Memulihkan buku kerja yang rusak
Jika Excel mengalami kerusakan, kemungkinan besar salinan buku kerja Anda satu-satunya juga akan ikut rusak. Sebagian besar waktu, Excel seringkali tidak dapat memulihkan buku kerja yang rusak; dalam kasus seperti itu, semua pekerjaan yang dilakukan sejak pembuatan buku kerja mungkin hilang secara permanen, kecuali Anda memiliki alat untuk memulihkannya. memperbaiki Excel xlsx atau file xlsm.
Pengantar Penulis:
Felix Hooker adalah pakar pemulihan data di DataNumen, Inc., yang merupakan pemimpin dunia dalam teknologi pemulihan data, termasuk perbaikan langka dan produk perangkat lunak pemulihan sql. Untuk informasi lebih lanjut kunjungi www.datanumen.com
