Kako koristiti Excel za čitanje i pisanje vanjske baze podataka

Excel može učiniti gotovo sve; treba li ga se tjerati da radi sve je druga stvar. Iako je proračunska tablica vrlo moćna u manipuliranju podacima, nije baš dobra u pohranjivanju normaliziranih podataka. Korištenje Excela u relacijsku bazu podataka poput SQL Server povećava snagu aplikacije.

Za početak će vam trebati MS Access ili stabilniji i besplatni – SQL Server Izraziti. Pretpostavlja se da čitatelj ima prikazanu vrpcu Excel Developer i da je upoznat s VBA Editorom i Structured Query Language (SQL). Ovaj članak koristi SQL Server nizovi povezivanja. Za MS-Access, obratite se Googleu.

Dok Excel ima vlastite ugrađene rutine za dobivanje informacija iz SQL Server u (recimo) zaokretnu tablicu, naš će primjer dati više fleksibilnosti u odabiru podataka.

Niz veze

Koristit ću privatnu bazu podataka; umetnite vlastite podatke o upravljačkom programu umjesto mojih u potprogramu ConnectDatabase. Zatim koristimo connDB kao komunikacijski kanal s našom bazom podataka – u mom slučaju za vraćanje rezultata iz pohranjene procedure. Možete upotrijebiti standardnije SQL naredbe poput "Odaberi * iz..."

Red poslovanja

Prvo ćemo učitati izbore kombiniranog okvira iz SQL Server kada se radna knjiga otvori, pomoću makronaredbe Auto_open i ispisivanjem podataka u list „ComboData“. Bez obzira je li poslužitelj u oblaku ili lokalno, neće biti primjetnog kašnjenja u pokretanju Excela – sve dok je baza podataka dostupna s radne stanice.

Zatim ćemo izdvojiti filtrirane podatke iz baze podataka i ispustiti ih u Excel, stupce F do K.

Sučelje

Moj ima padajuće okvire za filtriranje informacija iz baze podataka. The Uloga kombinirani okvir pokreće pretraživanje za popunjavanje tablice desno.

Preimenujte "Sheet1" u "Main". Dodajte barem jedan kombinirani okvir.

Kodeks

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

Formatirajte kontrolu kombiniranog okvira za čitanje listova "ComboData". Zatim desnom tipkom miša kliknite kombinirani okvir da biste mu dodijelili podproceduru ReadData. Kada je stavka odabrana u kombiniranom okviru, upišite njen ključ na list "Main", ćeliju D7. VBA kod će koristiti ovaj ključ kao filter (pogledajte intRole, gore).

Reference na dll biblioteku

U prozoru koda koristite Alati>Reference za referenciranje biblioteke Microsoft Active X Data Objects. To će omogućiti Excelu korištenje ADODB objekata deklariranih u kodu.

Gornja podrutina ReadData koristi relacijsku strukturu podataka, prikazanu u nastavku, što je teško postići samo u Excelu.

Daljnje promjene podataka mogle bi pokrenuti povratni upis u bazu podataka, nakon čega slijedi odgovarajuća izjava SQL Update connDB.execute(strSQL).

Na kraju, zaštitite svoj kod od gledanja ili mijenjanja:  Alati>Svojstva>Zaštita.

Rješavanje problema programa Excel:

S vremena na vrijeme, osobito kada sadrži složene programe, Excel bi se mogao srušiti i ne uspjeti ispravno pokriti. U slučaju a oštećeno xlsx datoteku, imati pri ruci učinkovit alat za oporavak riješit će većinu problema.

Uvod za autora:

Felix Hooker je stručnjak za oporavak podataka u DataNumen, Inc., koji je svjetski lider u tehnologijama za oporavak podataka, uključujući popravak rar pogreška i softverski proizvodi za oporavak sql-a. Za više informacija posjetite www.datanumen.com