Kuidas kasutada Excelit välise andmebaasi lugemiseks ja kirjutamiseks

Excel suudab praktiliselt kõike; kas see peaks kõike tegema, on teine ​​asi. Kuigi arvutustabel on andmetega manipuleerimisel väga võimas, ei ole see normaliseeritud andmete salvestamisel liiga hea. Exceli kasutamine relatsiooniandmebaasiga nagu SQL Server suurendab rakenduse võimsust.

Alustuseks vajate MS Accessi või stabiilsemat ja tasuta programmi. SQL Server Ekspress. Eeldatakse, et lugejal on kuvatud Exceli arendaja lint ning ta tunneb VBA redaktorit ja struktureeritud päringukeelt (SQL). See artikkel kasutab SQL Server ühendusstringid. MS-Accessi kohta vaadake Google'i.

Kuigi Excelil on oma sisseehitatud rutiinid teabe hankimiseks SQL Server (ütleme) pivot-tabelisse, annab meie näide andmete valimisel rohkem paindlikkust.

Ühendusstring

Ma kasutan privaatset andmebaasi; sisestage ConnectDatabase alamrutiini minu oma asemel oma draiveriteave. Kasutame siis connDB sidekanalina meie andmebaasi – minu puhul salvestatud protseduuri tulemuste tagastamiseks. Võite kasutada standardsemaid SQL-lauseid, näiteks "Vali * alates …"

Töökorraldus

Esiteks laadime liitkasti valikud SQL Server kui töövihik avaneb, kasutades Auto_open makrot ja lisades selle lehele „ComboData”. Olenemata sellest, kas server on pilves või lokaalne, ei ole Exceli käivitamisel märgatavat viivitust – seni, kuni andmebaas on tööjaamast ligipääsetav.

Järgmisena eraldame andmebaasist filtreeritud andmed ja pukseerime need Excelisse, veergudesse F kuni K.

Liides

Minu omal on andmebaasist teabe filtreerimiseks rippmenüüd. The Roll liitkast käivitab otsingu paremal asuva tabeli täitmiseks.

Nimeta "Sheet1" ümber "Peamiseks". Lisage vähemalt üks liitkast.

Kood

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

Vormindage liitkasti juhtelement lehtede "ComboData" lugemiseks. Seejärel paremklõpsake liitkasti, et määrata sellele alamprotseduur ReadData. Kui liitkastis on valitud üksus, kirjutage selle võti lehele "Põhi" lahtrisse D7. VBA-kood kasutab seda võtit filtrina (vt inrolli ülal).

Viited dll-teegile

Microsoft Active X andmeobjektide teegile viitamiseks kasutage koodiaknas menüüd Tööriistad > Viited. See võimaldab Excelil kasutada koodis deklareeritud ADODB-objekte.

Ülaltoodud alamrutiin ReadData kasutab allpool näidatud relatsiooniandmestruktuuri, mida on Excelis üksi raske saavutada.

Edasised andmemuudatused võivad käivitada andmebaasi tagasikirjutamise, millele järgneb asjakohane SQL Update'i avaldus connDB.execute(strSQL).

Lõpuks kaitske oma koodi vaatamise või muutmise eest.  Tööriistad>Atribuudid>Kaitse.

Exceli probleemide lahendamine:

Aeg-ajalt, eriti kui see sisaldab keerulisi programme, võib Excel kokku kukkuda ega suuda seda korralikult katta. Juhul, kui a kahjustatud xlsx faili, lahendab tõhusa taastamistööriista olemasolu enamiku probleeme.

Autori sissejuhatus:

Felix Hooker on andmete taastamise ekspert DataNumen, Inc., mis on maailmas juhtiv andmete taastamise tehnoloogiate, sealhulgas remont rar viga ja SQL-i taastamise tarkvaratooted. Lisateabe saamiseks külastage www.datanumenCom