Sådan bruges Excel til at læse og skrive en ekstern database

Excel kan stort set alt; om det skal gøres for at gøre alt, er en anden sag. Mens regnearket er meget kraftfuldt til at manipulere data, er det ikke så godt til at gemme normaliserede data. Udnyttelse af Excel til en relationsdatabase som SQL Server forbedrer applikationens magt.

Til at starte med skal du bruge MS Access eller det mere stabile og gratis – SQL Server Express. Det antages, at læseren har Excel Developer-båndet vist og er fortrolig med VBA Editor og Structured Query Language (SQL). Denne artikel bruger SQL Server forbindelsesstrenge. For MS-Access henvises til Google.

Mens Excel har sine egne indbyggede rutiner til at få information fra SQL Server i (siger) en pivottabel, vil vores eksempel give mere fleksibilitet i datavalg.

Forbindelsesstreng

Jeg bruger en privat database; indsæt dine egne driveroplysninger i stedet for mine i ConnectDatabase-underrutinen. Vi bruger derefter connDB som en kommunikationskanal til vores database - i mit tilfælde at returnere resultater fra en lagret procedure. Du bruger muligvis flere standard SQL-sætninger som "Vælg * fra ..."

Forretningsorden

Først indlæser vi valg af kombinationsbokse fra SQL Server når projektmappen åbnes, ved hjælp af en Auto_open-makro og dumping af den i arket "ComboData". Uanset om serveren er i skyen eller lokalt, vil der ikke være nogen mærkbar forsinkelse i opstart af Excel – så længe databasen er tilgængelig fra arbejdsstationen.

Dernæst udtrækker vi filtrerede data fra databasen og slipper dem i Excel, kolonne F til K.

Interface

Mine har rullelister til at filtrere oplysninger fra databasen. Det roller kombinationsfelt udløser en søgning for at udfylde tabellen til højre.

Omdøb “Ark1” som “Main”. Tilføj mindst en kombinationsboks.

Koden

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

Formater kontrolboksen til kombinationsboksen, så den læser ark “ComboData”. Højreklik derefter på kombinationsboksen for at tildele ReadData-underproceduren til den. Når et element er valgt i kombinationsboksen, skal du skrive dets nøgle til arket “Main”, celle D7. VBA-koden bruger denne nøgle som et filter (se intRole ovenfor).

Referencer til dll-biblioteket

Brug Værktøjer>Referencer i kodevinduet til at referere til Microsoft Active X Data Objects-biblioteket. Dette vil gøre det muligt for Excel at bruge de ADODB-objekter, der er deklareret i koden.

ReadData-underrutinen ovenfor bruger en relationel datastruktur, vist nedenfor, som er vanskelig at opnå i Excel alene.

Yderligere dataændringer kan udløse en tilbagekobling til databasen med den relevante SQL Update-sætning efterfulgt af connDB.execute (strSQL).

Endelig beskyt din kode mod at blive set eller ændret:  Værktøjer> Egenskaber> Beskyttelse.

Håndter Excel-problemer:

Fra tid til anden, især når det indeholder komplekse programmer, kan Excel gå ned og undlade at dække det korrekt igen. I tilfælde af en beskadiget xlsx fil, vil et effektivt gendannelsesværktøj ved hånden løse de fleste problemer.

Forfatter Introduktion:

Felix Hooker er en datagendannelsesekspert i DataNumen, Inc., som er verdens førende inden for datagendannelsesteknologier, herunder reparere rar fejl og SQL-genopretningssoftwareprodukter. For mere information besøg www.datanumen.com