Hvordan bruke Excel til å lese og skrive en ekstern database

Excel kan gjøre nesten alt; om den skal få til å gjøre alt er en annen sak. Selv om regnearket er veldig kraftig til å manipulere data, er det ikke så bra til å lagre normaliserte data. Utnytte Excel til en relasjonsdatabase som SQL Server forbedrer applikasjonens kraft.

Til å begynne med trenger du MS Access eller det mer stabile og gratis alternativet – SQL Server Uttrykke. Det forutsettes at leseren har Excel Developer-båndet vist, og er kjent med VBA Editor og Structured Query Language (SQL). Denne artikkelen bruker SQL Server koblingsstrenger. For MS-Access, se Google.

Mens Excel har egne innebygde rutiner for å hente informasjon fra SQL Server inn i (si) en pivottabell, vil vårt eksempel gi mer fleksibilitet i datavalg.

Tilkoblingsstreng

Jeg skal bruke en privat database; legg inn din egen driverinformasjon i stedet for min i ConnectDatabase-underrutinen. Vi bruker da konnDB som en kommunikasjonskanal til databasen vår – i mitt tilfelle for å returnere resultater fra en lagret prosedyre. Du kan bruke mer standard SQL-setninger som "Velg * fra ..."

Forretningsorden

Først vil vi laste inn kombinasjonsboksvalg fra SQL Server når arbeidsboken åpnes, ved å bruke en Auto_open-makro og legge den i arket «ComboData». Enten serveren er i skyen eller lokalt, vil det ikke være noen merkbar forsinkelse i oppstart av Excel – så lenge databasen er tilgjengelig fra arbeidsstasjonen.

Deretter vil vi trekke ut filtrerte data fra databasen og slippe dem inn i Excel, kolonnene F til K.

Grensesnittet

Min har rullegardinbokser for å filtrere informasjon fra databasen. De Rolle kombinasjonsboksen utløser et søk for å fylle ut tabellen til høyre.

Gi nytt navn til "Sheet1" til "Main". Legg til minst én kombinasjonsboks.

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 kombinasjonsbokskontrollen for å lese arkene "ComboData". Høyreklikk deretter kombinasjonsboksen for å tilordne ReadData-underprosedyren til den. Når et element er valgt i kombinasjonsboksen, skriv nøkkelen til arket "Hoved", celle D7. VBA-koden vil bruke denne nøkkelen som et filter (se intRole ovenfor).

Referanser til dll-biblioteket

Bruk Verktøy>Referanser i kodevinduet for å referere til Microsoft Active X Data Objects-biblioteket. Dette vil gjøre det mulig for Excel å bruke ADODB-objektene som er deklarert i koden.

ReadData-underrutinen ovenfor bruker en relasjonsdatastruktur, vist nedenfor, som er vanskelig å oppnå i Excel alene.

Ytterligere dataendringer kan utløse en tilbakeskrivning til databasen, med den riktige SQL Update-setningen etterfulgt av connDB.execute(strSQL).

Til slutt, beskytt koden din mot å bli sett eller endret:  Verktøy>Egenskaper>Beskyttelse.

Håndtere Excel-problemer:

Fra tid til annen, spesielt når den inneholder komplekse programmer, kan Excel krasje og ikke gjenopprette de riktige. I tilfelle av en skadet xlsx filen, vil det å ha et effektivt gjenopprettingsverktøy for hånden løse de fleste problemer.

Forfatterintroduksjon:

Felix Hooker er en datagjenopprettingsekspert innen DataNumen, Inc., som er verdensledende innen datagjenopprettingsteknologier, inkludert reparasjon rar feil og sql-programvareprodukter. For mer informasjon besøk www.datanumen. Med