Kaip naudoti „Excel“ išorinei duomenų bazei skaityti ir rašyti

„Excel“ gali padaryti beveik bet ką; ar ji turi būti priversta daryti viską – kitas reikalas. Nors skaičiuoklė yra labai galinga manipuliuojant duomenimis, ji nėra labai tinkama normalizuotų duomenų saugojimui. „Excel“ panaudojimas reliacinėje duomenų bazėje, pvz SQL Server padidina programos galią.

Pradžiai reikės MS Access arba stabilesnės ir nemokamos versijos. SQL Server Express. Daroma prielaida, kad skaitytojas turi „Excel“ kūrėjo juostelę ir yra susipažinęs su VBA redaktoriumi ir struktūrine užklausų kalba (SQL). Šiame straipsnyje naudojami SQL Server jungčių stygos. Dėl MS-Access ieškokite „Google“.

Nors „Excel“ turi savo įmontuotas informacijos gavimo procedūras SQL Server į (tarkime) suvestinę lentelę, mūsų pavyzdys suteiks daugiau lankstumo renkantis duomenis.

Ryšio eilutė

Aš naudosiu privačią duomenų bazę; Įdėkite savo vairuotojo informaciją vietoj mano į „ConnectDatabase“ antrinę programą. Tada naudojame connDB kaip ryšio kanalą į mūsų duomenų bazę – mano atveju grąžinti išsaugotos procedūros rezultatus. Galite naudoti daugiau standartinių SQL sakinių, pvz., „Select * from…“

Darbo tvarka

Pirmiausia įkelsime kombinuotojo langelio pasirinkimus iš SQL Server kai darbaknygė atidaroma naudojant „Auto_open“ makrokomandą ir iškeliant ją į lapą „ComboData“. Nesvarbu, ar serveris yra debesyje, ar vietiniame kompiuteryje, „Excel“ paleidimas nebus pastebimas vėluojant – jei tik duomenų bazė pasiekiama iš darbo stoties.

Tada iš duomenų bazės ištrauksime filtruotus duomenis ir įmessime juos į „Excel“, stulpelius nuo F iki K.

Sąsaja

Mano yra išskleidžiamieji langeliai, skirti filtruoti informaciją iš duomenų bazės. The Vaidmuo kombinuotasis laukelis suaktyvina paiešką, kad būtų užpildyta lentelė dešinėje.

Pervardykite „Sheet1“ į „Main“. Pridėkite bent vieną kombinuotąjį laukelį.

Kodeksas

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

Suformatuokite kombinuotojo langelio valdiklį, kad galėtumėte skaityti lapus „ComboData“. Tada dešiniuoju pelės mygtuku spustelėkite kombinuotąjį laukelį, kad priskirtumėte jam „ReadData“ antrinę procedūrą. Kai elementas pasirenkamas kombinuotame laukelyje, įrašykite jo raktą į lapą „Pagrindinis“, langelyje D7. VBA kodas naudos šį raktą kaip filtrą (žr. introle, aukščiau).

Nuorodos į dll biblioteką

Norėdami nurodyti „Microsoft Active X“ duomenų objektų biblioteką, kodo lange naudokite meniu punktą „Įrankiai“ > „Nuorodos“. Tai leis programai „Excel“ naudoti kode deklaruotus ADODB objektus.

Aukščiau pateikta „ReadData“ antrinė rutina naudoja reliacinę duomenų struktūrą, parodytą toliau, o tai sunku pasiekti naudojant „Excel“.

Tolesni duomenų pakeitimai gali suaktyvinti įrašymą į duomenų bazę su atitinkamu SQL atnaujinimo sakiniu connDB.execute(strSQL).

Galiausiai apsaugokite kodą nuo peržiūros ar pakeitimo:  Įrankiai> Savybės> Apsauga.

Spręskite „Excel“ problemas:

Retkarčiais, ypač kai joje yra sudėtingų programų, „Excel“ gali sugesti ir nepavykti tinkamai uždengti. Tuo atveju, kai a sugadintas xlsx failą, veiksminga atkūrimo priemonė po ranka išspręs daugumą problemų.

Autoriaus įvadas:

Felixas Hookeris yra duomenų atkūrimo ekspertas DataNumen, Inc., kuri yra pasaulyje duomenų atkūrimo technologijų lyderė, įskaitant remontas rar klaida ir sql atkūrimo programinės įrangos produktai. Norėdami gauti daugiau informacijos, apsilankykite WWW.datanumen.com