วิธีการโทร SQL Server กระบวนงานที่จัดเก็บจาก Excel VBA

ข้อมูลบนเซิร์ฟเวอร์สามารถแก้ไขได้โดยการตรวจสอบ "ฝั่งไคลเอ็นต์" ใน Excel VBA เปลี่ยนข้อมูลตามต้องการและบันทึกกลับไปที่เซิร์ฟเวอร์
วิธีที่มีประสิทธิภาพมากขึ้นในการดำเนินการนี้โดยเฉพาะอย่างยิ่งหากฐานข้อมูลอยู่ในตำแหน่งที่ห่างไกลและมีการรับส่งข้อมูลจำนวนมากที่เกี่ยวข้องคือการทำงาน 'ฝั่งเซิร์ฟเวอร์' แบบฝึกหัดนี้เรียกขั้นตอนการจัดเก็บจาก Excel เพื่อจัดหมวดหมู่พนักงานเป็นช่วงอายุตามวันเกิด (เช่น 18-25 ปี 26-35 ปีเป็นต้น) โดยไม่มีการแลกเปลี่ยนข้อมูลจำนวนมากระหว่างเซิร์ฟเวอร์และ Excel

บทความนี้ถือว่าผู้อ่านมี Ribbon ของนักพัฒนาปรากฏอยู่และคุ้นเคยกับ VBA Editor มิฉะนั้นโปรดใช้“ แท็บนักพัฒนา Excel” ของ Google หรือ“ หน้าต่างรหัส Excel”

การออกกำลังกายมีสามองค์ประกอบ:

• ตารางข้อมูล tblStaff ภายในฐานข้อมูล เทสดีบี;
• ขั้นตอนการจัดเก็บ spAgeRange;
• Excel xlsm ซึ่งเราจะเรียก xlsm. สามารถพบไฟล์ Excel ตัวอย่างได้ Good Farm Animal Welfare Awards

ตารางข้อมูล

สร้างฐานข้อมูลใน SQL Server ที่เรียกว่า ดีบีเทส.

ตั้งค่าคอลัมน์ต่อไปนี้สำหรับตาราง tblStaff.

คัดลอกสิ่งต่อไปนี้ลงในตาราง:

ขั้นตอนการเก็บ.

รันสคริปต์นี้กับ TestDB เพื่อสร้างโพรซีเดอร์ที่เก็บไว้:

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

กระบวนงานที่จัดเก็บจะถูกบันทึกไว้ภายใต้“ Programmability” ในฐานข้อมูล

เอ็กเซล VBA

สิ่งที่เหลืออยู่คือการเรียกใช้กระบวนงานที่เก็บไว้จาก Excel โดยให้ไฟล์ วันที่เงินเดือน พารามิเตอร์ของ“ 2017/05/25” คุณจะสังเกตว่าฉันพิมพ์ข้อมูล  วันที่เงินเดือน เป็นสตริงแทนที่จะต่อสู้กับรูปแบบวันที่ที่แตกต่างกัน ง่ายพอที่จะแปลงสตริงเป็นวันที่โดยใช้ แปลง ฟังก์ชั่นถ้า วันที่เงินเดือน จะถูกใช้เพื่อวัตถุประสงค์ทางคณิตศาสตร์

สร้างสมุดงานใหม่ เปิดหน้าต่างรหัส VBA และใส่โมดูล

จากเมนูเครื่องมือของหน้าต่างรหัสอ้างอิงสิ่งที่เหมาะสม ไลบรารี Active X 2.nn เพื่ออำนวยความสะดวกในการใช้วัตถุข้อมูล

วางรหัสต่อไปนี้ลงในหน้าต่างรหัส สิ่งนี้เมื่อเปิดใช้งานแล้วจะเชื่อมต่อกับ SQL Serverตามโพรซีเดอร์ย่อย 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

เพิ่มปุ่มใน Sheet1 และกำหนดให้กับขั้นตอนย่อย“เขียน"

ผลลัพธ์

กดปุ่มจากนั้นตรวจสอบ tblStaff ซึ่งควรอัปเดตตามอายุและช่วงอายุ การประมวลผลเกิดขึ้นที่ฝั่งเซิร์ฟเวอร์

การกู้คืนสมุดงานที่เสียหาย

หากโปรแกรม Excel เกิดขัดข้อง อาจทำให้สำเนาไฟล์เวิร์กบุ๊กของคุณหายไปด้วย ในหลายกรณี Excel มักไม่สามารถกู้คืนเวิร์กบุ๊กที่เสียหายได้ ในกรณีเช่นนี้ งานทั้งหมดที่ทำมาตั้งแต่สร้างเวิร์กบุ๊กอาจสูญหายไปอย่างถาวร เว้นแต่คุณจะมีเครื่องมือช่วย ซ่อมแซม Excel xlsx หรือ xlsm

บทนำผู้เขียน:

Felix Hooker เป็นผู้เชี่ยวชาญด้านการกู้คืนข้อมูลใน DataNumen, Inc. ซึ่งเป็นผู้นำระดับโลกด้านเทคโนโลยีการกู้คืนข้อมูล ได้แก่ การซ่อมแซมที่หายาก และผลิตภัณฑ์ซอฟต์แวร์กู้คืน sql ดูข้อมูลเพิ่มเติมได้ที่ wwwdatanumenด้วย.