วิธีใช้ Excel เพื่ออ่านและเขียนฐานข้อมูลภายนอก

Excel สามารถทำอะไรก็ได้ ว่าควรจะทำทุกอย่างเป็นอีกเรื่องหนึ่งหรือไม่ แม้ว่าสเปรดชีตจะมีประสิทธิภาพมากในการจัดการข้อมูล แต่ก็ไม่ได้ดีเกินไปในการจัดเก็บข้อมูลที่เป็นมาตรฐาน การใช้ Excel เข้ากับฐานข้อมูลเชิงสัมพันธ์เช่น SQL Server ช่วยเพิ่มพลังของแอปพลิเคชัน

ก่อนอื่น คุณจะต้องมีโปรแกรม MS Access หรือโปรแกรมที่เสถียรและใช้งานได้ฟรี – SQL Server ด่วน. สันนิษฐานว่าผู้อ่านมี Ribbon สำหรับนักพัฒนา Excel ปรากฏอยู่และคุ้นเคยกับ VBA Editor และ Structured Query Language (SQL) บทความนี้ใช้ SQL Server สตริงการเชื่อมต่อ สำหรับ MS-Access โปรดดูที่ Google

ในขณะที่ Excel มีรูทีนในตัวสำหรับการรับข้อมูลจาก SQL Server ใน (พูด) ตาราง Pivot ตัวอย่างของเราจะให้ความยืดหยุ่นในการเลือกข้อมูลมากขึ้น

สตริงการเชื่อมต่อ

ฉันจะใช้ฐานข้อมูลส่วนตัว ใส่ข้อมูลไดรเวอร์ของคุณเองแทนของฉันในรูทีนย่อย ConnectDatabase จากนั้นเราก็ใช้ คอนเอ็นดีบี เป็นช่องทางการสื่อสารไปยังฐานข้อมูลของเรา - ในกรณีของฉันคือส่งคืนผลลัพธ์จากกระบวนงานที่เก็บไว้ คุณอาจใช้คำสั่ง SQL มาตรฐานเพิ่มเติมเช่น“ เลือก * จาก…”

ลำดับของธุรกิจ

ขั้นแรกเราจะโหลดตัวเลือกคำสั่งผสมจาก SQL Server เมื่อเปิดเวิร์กบุ๊กโดยใช้มาโคร Auto_open และบันทึกข้อมูลลงในชีต “ComboData” ไม่ว่าเซิร์ฟเวอร์จะอยู่บนคลาวด์หรือในเครื่อง ก็จะไม่มีความล่าช้าที่สังเกตได้ในการเริ่มต้นใช้งาน Excel ตราบใดที่สามารถเข้าถึงฐานข้อมูลได้จากเวิร์กสเตชัน

ต่อไปเราจะแยกข้อมูลที่กรองแล้วจากฐานข้อมูลและวางลงใน Excel คอลัมน์ F ถึง K

อินเทอร์เฟซ

Mine มีช่องแบบเลื่อนลงเพื่อกรองข้อมูลจากฐานข้อมูล บทบาท กล่องคำสั่งผสมทริกเกอร์การค้นหาเพื่อเติมข้อมูลในตารางทางด้านขวา

เปลี่ยนชื่อ“ Sheet1” เป็น“ Main” เพิ่มคอมโบบ็อกซ์อย่างน้อยหนึ่งอัน

รหัส

Public connDB As New ADODB.Connection
Public rstNew As New ADODB.Recordset
Public rs As New ADODB.Recordset
Public strSQL As String
Public nID As Integer

Sub auto_Open()
    Call PopulateComboData     'kicks off the first process  on Open
End Sub

Sub PopulateComboData()
    Sheets("ComboData").Range("A3:C100").ClearContents
    Call ConnectDatabase      'use the ConnectDatabase routine
    strSQL = "Select DeptID, Department, Phase from tblDept Order by Department"
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("A3").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ReadData()
    intRole = Sheets("main").Range("D7")
    Sheets("Main").Range("F4:L100").ClearContents
    Call ConnectDatabase
    strSQL = "EXEC DBTest " & intRole    'calls a stored proc with parameter
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("F4").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ConnectDatabase()
    On Error GoTo ErrConnect
    If connDB.State = 1 Then connDB.Close     'closes connection if already open
    strServer = "197.200.28.164" 
    strDBase = "Qcrew_sql"
    strUser = "joesoap_sql"
    strPWD = "frU6ra!@"
    If strPWD > "" Then 
        strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & _
        ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & _
        ";Connection Timeout=30;"
    Else        'Use windows authentication
        strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & _
        ";Trusted_Connection=yes;DATABASE=" & strDBase
    End If
    connDB.Open strConnectionstring
Exit Sub
ErrConnect:
    MsgBox Err.Description
End Sub

จัดรูปแบบตัวควบคุมกล่องคำสั่งผสมเพื่ออ่านแผ่นงาน“ ComboData” จากนั้นคลิกขวาที่กล่องคำสั่งผสมเพื่อกำหนดขั้นตอนย่อย ReadData ให้ เมื่อรายการถูกเลือกในคอมโบบ็อกซ์ให้เขียนคีย์ลงในแผ่นงาน“ หลัก” เซลล์ D7 รหัส VBA จะใช้คีย์นี้เป็นตัวกรอง (ดู intRole ด้านบน)

การอ้างอิงถึงไลบรารี dll

ใช้เมนู เครื่องมือ > การอ้างอิง ในหน้าต่างโค้ด เพื่ออ้างอิงไลบรารี Microsoft Active X Data Objects วิธีนี้จะช่วยให้ Excel สามารถใช้งานอ็อบเจ็กต์ ADODB ที่ประกาศไว้ในโค้ดได้

รูทีนย่อย ReadData ด้านบนใช้โครงสร้างข้อมูลเชิงสัมพันธ์ดังแสดงด้านล่างซึ่งยากที่จะบรรลุใน Excel เพียงอย่างเดียว

การเปลี่ยนแปลงข้อมูลเพิ่มเติมอาจทำให้เกิดการเขียนกลับไปยังฐานข้อมูลโดยใช้คำสั่ง SQL Update ที่เหมาะสมตามด้วย connDB.execute (strSQL)

สุดท้ายป้องกันไม่ให้มีการดูหรือเปลี่ยนแปลงรหัสของคุณ:  เครื่องมือ> คุณสมบัติ> การป้องกัน.

จัดการปัญหา Excel:

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

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

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