Cum se utilizează Excel pentru a citi și a scrie o bază de date externă

Excel poate face practic orice; dacă ar trebui să facă totul este o altă chestiune. În timp ce foaia de calcul este foarte puternică în manipularea datelor, nu este prea bună la stocarea datelor normalizate. Valorificarea Excel la o bază de date relațională, cum ar fi SQL Server sporește puterea aplicației.

Pentru început, veți avea nevoie de MS Access sau de versiunea mai stabilă și gratuită – SQL Server Expres. Se presupune că cititorul are afișată panglica Excel Developer și este familiarizat cu Editorul VBA și limbajul de interogare structurat (SQL). Acest articol folosește SQL Server șiruri de conexiune. Pentru MS-Access, consultați Google.

În timp ce Excel are propriile sale rutine încorporate pentru a obține informații de la SQL Server într-un tabel pivot (să zicem), exemplul nostru va oferi mai multă flexibilitate în selectarea datelor.

Șirul de conexiune

Voi folosi o bază de date privată; introduceți propriile informații despre driver în locul meu în subrutina ConnectDatabase. Folosim apoi connDB ca canal de comunicare către baza noastră de date – în cazul meu pentru a returna rezultate dintr-o procedură stocată. Este posibil să utilizați mai multe instrucțiuni SQL standard, cum ar fi „Selectați * din...”

Ordinea Afacerilor

În primul rând, vom încărca opțiuni din caseta combinată SQL Server când se deschide registrul de lucru, utilizând o macrocomandă Auto_open și salvându-l în foaia „ComboData”. Indiferent dacă serverul este în cloud sau local, nu va exista nicio întârziere vizibilă la pornirea Excel - atâta timp cât baza de date este accesibilă de pe stația de lucru.

Apoi, vom extrage datele filtrate din baza de date și le vom plasa în Excel, coloanele F la K.

Interfața

Al meu are casete drop-down pentru a filtra informațiile din baza de date. The Rol caseta combinată declanșează o căutare pentru a popula tabelul din dreapta.

Redenumiți „Sheet1” ca „Main”. Adăugați cel puțin o casetă combinată.

Codul

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

Formatați controlul casetei combinate pentru a citi foile „ComboData”. Apoi faceți clic dreapta pe caseta combinată pentru a-i atribui subprocedura ReadData. Când un articol este selectat în caseta combinată, scrieți cheia acestuia în foaia „Principal”, celula D7. Codul VBA va folosi această cheie ca filtru (a se vedea intRole, mai sus).

Referințe la biblioteca DLL

Folosiți Instrumente>Referințe din fereastra de cod pentru a face referire la biblioteca de obiecte de date Microsoft Active X. Acest lucru va permite Excel să utilizeze obiectele ADODB declarate în cod.

Subrutina ReadData de mai sus utilizează o structură de date relaționale, prezentată mai jos, care este dificil de realizat doar în Excel.

Modificările ulterioare ale datelor ar putea declanșa o rescriere în baza de date, cu instrucțiunea SQL Update corespunzătoare urmată de connDB.execute(strSQL).

În cele din urmă, protejați-vă codul de a fi vizualizat sau modificat:  Instrumente>Proprietăți>Protecție.

Gestionați problemele Excel:

Din când în când, în special atunci când deține programe complexe, Excel se poate bloca și nu reușește să recupereze corect. În cazul a xlsx deteriorat fișier, un instrument eficient de recuperare la îndemână va rezolva majoritatea problemelor.

Introducerea autorului:

Felix Hooker este un expert în recuperarea datelor DataNumen, Inc., care este lider mondial în tehnologiile de recuperare a datelor, inclusiv repara rar eroare și produse software de recuperare sql. Pentru mai multe informații vizitați www.datanumen.com