როგორ გამოვიყენოთ Excel გარე მონაცემთა ბაზის წასაკითხად და ჩასაწერად

Excel-ს შეუძლია პრაქტიკულად ყველაფერი; უნდა აიძულოს თუ არა ყველაფერი გააკეთოს, ეს სხვა საკითხია. მიუხედავად იმისა, რომ ელცხრილი ძალიან ძლიერია მონაცემების მანიპულირებისთვის, ის არც ისე კარგია ნორმალიზებული მონაცემების შესანახად. Excel-ის გამოყენება რელაციურ მონაცემთა ბაზაში, როგორიცაა SQL Server აძლიერებს აპლიკაციის ძალას.

დასაწყისისთვის, დაგჭირდებათ MS Access ან უფრო სტაბილური და უფასო ვერსია – SQL Server ექსპრესი. ვარაუდობენ, რომ მკითხველს აქვს Excel დეველოპერის ლენტი ნაჩვენები და იცნობს VBA რედაქტორს და სტრუქტურირებული შეკითხვის ენას (SQL). ეს სტატია იყენებს SQL Server კავშირის სიმები. MS-Access-ისთვის მიმართეთ Google-ს.

მიუხედავად იმისა, რომ Excel-ს აქვს საკუთარი ჩაშენებული რუტინები ინფორმაციის მისაღებად SQL Server (ვთქვათ) კრებსითი ცხრილის სახით, ჩვენი მაგალითი უფრო მეტ მოქნილობას მისცემს მონაცემთა შერჩევისას.

კავშირის სტრიქონი

მე ვიყენებ კერძო მონაცემთა ბაზას; ჩადეთ თქვენი დრაივერის ინფორმაცია ჩემის ნაცვლად ConnectDatabase ქვე-რუტინაში. შემდეგ ვიყენებთ connDB როგორც საკომუნიკაციო არხი ჩვენს მონაცემთა ბაზაში – ჩემს შემთხვევაში შენახული პროცედურის შედეგების დაბრუნება. თქვენ შეგიძლიათ გამოიყენოთ უფრო სტანდარტული SQL განცხადებები, როგორიცაა "აირჩიეთ * დან ..."

ბიზნესის ორდენი

პირველ რიგში, ჩვენ ჩატვირთავთ კომბინირებული ყუთის არჩევანს SQL Server სამუშაო წიგნის გახსნისას, Auto_open მაკროს გამოყენებით და მისი „ComboData“ ფურცელში ჩასმით. სერვერი ღრუბელშია თუ ლოკალურში, Excel-ის გაშვებისას შესამჩნევი შეფერხება არ იქნება - იმ პირობით, რომ მონაცემთა ბაზაზე წვდომა სამუშაო სადგურიდან იქნება შესაძლებელი.

შემდეგი, ჩვენ ამოვიღებთ გაფილტრულ მონაცემებს მონაცემთა ბაზიდან და ჩავყრით Excel-ში, სვეტები F-დან K-მდე.

ინტერფეისი

ჩემსას აქვს ჩამოსაშლელი ყუთები მონაცემთა ბაზიდან ინფორმაციის გასაფილტრად. The როლი კომბინირებული ყუთი აწარმოებს ძიებას ცხრილის მარჯვნივ შესავსებად.

დაარქვით „Sheet1“-ს „მთავარი“. დაამატეთ მინიმუმ ერთი კომბობოქსი.

კოდექსი

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 ბიბლიოთეკის მითითებისთვის კოდის ფანჯარაში გამოიყენეთ Tools>References. ეს Excel-ს საშუალებას მისცემს გამოიყენოს კოდში დეკლარირებული ADODB ობიექტები.

ზემოთ მოყვანილი ReadData ქვე-რუტინი იყენებს მონაცემთა რელაციურ სტრუქტურას, რომელიც ნაჩვენებია ქვემოთ, რომლის მიღწევა ძნელია მხოლოდ Excel-ში.

მონაცემთა შემდგომმა ცვლილებებმა შეიძლება გამოიწვიოს მონაცემთა ბაზაში ჩაწერა, შესაბამისი SQL Update განაცხადის შემდეგ connDB.execute(strSQL).

და ბოლოს, დაიცავით თქვენი კოდი ნახვის ან შეცვლისგან:  Tools>Properties>Protection.

Excel პრობლემების მოგვარება:

დროდადრო, განსაკუთრებით მაშინ, როდესაც ის შეიცავს რთულ პროგრამებს, Excel შეიძლება ავარიული იყოს და სწორად ვერ დაფაროს. იმ შემთხვევაში, თუ ა დაზიანებული xlsx ფაილის შემთხვევაში, ეფექტური აღდგენის ინსტრუმენტის ქონა პრობლემების უმეტესობას მოაგვარებს.

ავტორი შესავალი:

ფელიქს ჰუკერი არის მონაცემთა აღდგენის ექსპერტი DataNumen, Inc., რომელიც მსოფლიო ლიდერია მონაცემთა აღდგენის ტექნოლოგიებში, მათ შორის სარემონტო rar შეცდომა და sql აღდგენის პროგრამული პროდუქტები. დამატებითი ინფორმაციისთვის ეწვიეთ www.datanumen. ერთად