Hur man använder Excel för att läsa och skriva en extern databas

Excel kan göra nästan vad som helst; om det ska göras att göra allt är en annan sak. Även om kalkylbladet är mycket kraftfullt för att manipulera data är det inte så bra att lagra normaliserad data. Använda Excel till en relationsdatabas som SQL Server förbättrar applikationens kraft.

Till att börja med behöver du MS Access eller det mer stabila och gratis alternativet – SQL Server Uttrycka. Det antas att läsaren visar Excel Developer-bandet och är bekant med VBA Editor och Structured Query Language (SQL). Denna artikel använder SQL Server anslutningssträngar. För MS-Access, se Google.

Medan Excel har sina egna inbyggda rutiner för att få information från SQL Server i (säg) en pivottabell, kommer vårt exempel att ge mer flexibilitet i dataval.

Anslutningssträng

Jag kommer att använda en privat databas; infoga din egen förarinformation istället för min i ConnectDatabase-underrutinen. Vi använder sedan connDB som en kommunikationskanal till vår databas - i mitt fall att returnera resultat från en lagrad procedur. Du kan använda mer vanliga SQL-uttalanden som "Välj * från ..."

Företagsordning

Först kommer vi att ladda kombinationsruta val från SQL Server när arbetsboken öppnas, med hjälp av ett Auto_open-makro och dumpa det i arket "ComboData". Oavsett om servern är i molnet eller lokalt, kommer det inte att bli någon märkbar fördröjning vid start av Excel – så länge databasen är tillgänglig från arbetsstationen.

Därefter extraherar vi filtrerade data från databasen och släpper dem till Excel, kolumnerna F till K.

Gränssnittet

Gruvan har listrutor för att filtrera information från databasen. De Roll kombinationsruta utlöser en sökning för att fylla i tabellen till höger.

Byt namn på “Sheet1” som “Main”. Lägg till minst en kombinationsruta.

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

Formatera kombinationsrutans kontroll för att läsa ark "ComboData". Högerklicka sedan på kombinationsrutan för att tilldela underproceduren ReadData till den. När ett objekt är valt i kombinationsrutan, skriv dess nyckel till blad "Main", cell D7. VBA-koden använder den här nyckeln som ett filter (se intRole, ovan).

Referenser till dll-biblioteket

Använd Verktyg>Referenser i kodfönstret för att referera till Microsoft Active X Data Objects-biblioteket. Detta gör att Excel kan använda de ADODB-objekt som deklarerats i koden.

Underrutinen ReadData ovan använder en relationsdata-struktur, som visas nedan, vilket är svårt att uppnå i Excel ensam.

Ytterligare dataändringar kan utlösa en återskrivning till databasen, med lämpligt SQL Update-uttalande följt av connDB.execute (strSQL).

Slutligen, skydda din kod från att visas eller ändras:  Verktyg> Egenskaper> Skydd.

Hantera Excel-problem:

Ibland, särskilt när det innehåller komplexa program, kan Excel krascha och misslyckas med att täcka om ordentligt. I händelse av en skadad xlsx filen, kommer ett effektivt återställningsverktyg till hands att lösa de flesta problem.

Författarintroduktion:

Felix Hooker är en dataåterställningsexpert i DataNumen, Inc., som är världsledande inom teknik för återställning av data, inklusive reparation rar fel och mjukvaruprodukter för SQL-återställning. För mer information besök www.datanumen.com