Kako koristiti Excel za čitanje i pisanje vanjske baze podataka

Excel može učiniti gotovo sve; da li treba naterati da se uradi sve je druga stvar. Iako je tabela vrlo moćna u manipulaciji podacima, nije previše dobra u pohranjivanju normaliziranih podataka. Iskorištavanje Excela u relacijsku bazu podataka kao što je SQL Server povećava snagu aplikacije.

Za početak, trebat će vam MS Access ili stabilniji i besplatni – SQL Server Express. Pretpostavlja se da čitač ima prikazanu Excel Developer traku i da je upoznat sa VBA Editorom i jezikom strukturiranih upita (SQL). Ovaj članak koristi SQL Server konekcioni nizovi. Za MS-Access, pogledajte Google.

Dok Excel ima svoje ugrađene rutine za dobijanje informacija SQL Server u (recimo) zaokretnu tabelu, naš primjer će dati veću fleksibilnost u odabiru podataka.

Connection String

Koristit ću privatnu bazu podataka; ubacite svoje informacije o drajveru umjesto mojih u podrutinu ConnectDatabase. Zatim koristimo connDB kao komunikacijski kanal našoj bazi podataka – u mom slučaju za vraćanje rezultata iz pohranjene procedure. Možete koristiti više standardnih SQL izraza kao što je “Odaberi * iz…”

Red poslovanja

Prvo ćemo učitati izbore iz combo-boxa SQL Server kada se radna sveska otvori, korištenjem makroa Auto_open i ispisivanjem podataka u listu „ComboData“. Bez obzira da li se server nalazi 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, kolone F do K.

Interfejs

Moj ima padajuće okvire za filtriranje informacija iz baze podataka. The uloga kombinovani okvir pokreće pretragu za popunjavanje tabele sa desne strane.

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 kombinovanog okvira za čitanje listova „ComboData“. Zatim kliknite desnim tasterom miša na kombinovani okvir da biste mu dodelili potproceduru ReadData. Kada je stavka odabrana u komboboksu, upišite njen ključ u list „Glavno“, ćelija D7. VBA kod će koristiti ovaj ključ kao filter (pogledajte intRole, gore).

Reference na dll biblioteku

Koristite Alati>Reference u prozoru koda za referenciranje biblioteke Microsoft Active X Data Objects. Ovo će omogućiti Excelu da koristi ADODB objekte deklarirane u kodu.

Podrutina ReadData iznad koristi relacionu strukturu podataka, prikazanu ispod, što je teško postići samo u Excelu.

Dalje promjene podataka mogle bi pokrenuti povratni upis u bazu podataka, uz odgovarajuću naredbu SQL Update praćenu connDB.execute(strSQL).

Konačno, zaštitite svoj kod od pregleda ili promjene:  Alati>Svojstva>Zaštita.

Rješavanje problema s Excelom:

S vremena na vrijeme, posebno kada ima složene programe, Excel bi se mogao srušiti i ne uspjeti ponovo pravilno pokriti. U slučaju a oštećen xlsx datoteka, imati pri ruci efikasan alat za oporavak riješit će većinu problema.

Uvod za autora:

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