Excel kan vrijwel alles; of het moet worden gemaakt om alles te doen, is een andere kwestie. Hoewel de spreadsheet erg krachtig is in het manipuleren van gegevens, is hij niet zo goed in het opslaan van genormaliseerde gegevens. Excel gebruiken voor een relationele database zoals SQL Server vergroot de kracht van de applicatie.
Om te beginnen heb je MS Access nodig, of de stabielere en gratis variant – SQL Server Uitdrukken. Aangenomen wordt dat de lezer het Excel Developer-lint heeft weergegeven en bekend is met de VBA Editor en Structured Query Language (SQL). Dit artikel maakt gebruik van SQL Server verbindingsreeksen. Raadpleeg Google voor MS-Access.
Hoewel Excel zijn eigen ingebouwde routines heeft om informatie uit te halen SQL Server in (laten we zeggen) een draaitabel, zal ons voorbeeld meer flexibiliteit bieden bij het selecteren van gegevens.
Verbindingsdraad
Ik zal een privé-database gebruiken; plaats uw eigen stuurprogramma-informatie in plaats van de mijne in de ConnectDatabase-subroutine. We gebruiken dan verbind als communicatiekanaal naar onze database - in mijn geval om resultaten van een opgeslagen procedure te retourneren. U kunt meer standaard SQL-instructies gebruiken, zoals 'Selecteer * van ...'
Orde van zaken
Eerst laden we keuzelijsten met invoervak uit SQL Server Wanneer het werkblad wordt geopend, wordt een Auto_open-macro gebruikt om de gegevens in het blad "ComboData" te plaatsen. Of de server zich nu in de cloud of lokaal bevindt, er zal geen merkbare vertraging zijn bij het opstarten van Excel, zolang de database maar toegankelijk is vanaf het werkstation.
Vervolgens halen we gefilterde gegevens uit de database en plaatsen deze in Excel, kolommen F tot K.
De interface
De mijne heeft vervolgkeuzelijsten om informatie uit de database te filteren. De Rol combobox activeert een zoekopdracht om de tabel rechts te vullen.
Hernoem “Blad1” naar “Hoofd”. Voeg minimaal één combobox toe.
De code
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
Formatteer het besturingselement met invoervak om de bladen "ComboData" te lezen. Klik vervolgens met de rechtermuisknop op de keuzelijst om de ReadData-subprocedure eraan toe te wijzen. Wanneer een item is geselecteerd in de combobox, schrijft u de sleutel naar het blad "Hoofd", cel D7. De VBA-code gebruikt deze sleutel als een filter (zie intRole hierboven).
Verwijzingen naar de DLL-bibliotheek
Gebruik Extra > Verwijzingen in het codevenster om te verwijzen naar de Microsoft ActiveX-gegevensobjectenbibliotheek. Hierdoor kan Excel de in de code gedeclareerde ADODB-objecten gebruiken.
De ReadData-subroutine hierboven gebruikt een relationele gegevensstructuur, zoals hieronder weergegeven, die moeilijk te realiseren is in alleen Excel.
Verdere gegevenswijzigingen kunnen een terugschrijven naar de database veroorzaken, met de juiste SQL Update-instructie gevolgd door connDB.execute (strSQL).
Bescherm tot slot uw code zodat deze niet kan worden bekeken of gewijzigd: Extra> Eigenschappen> Bescherming.
Omgaan met Excel-problemen:
Van tijd tot tijd, vooral wanneer het complexe programma's bevat, kan Excel crashen en de juiste dekking niet herstellen. In het geval van een beschadigde xlsx Het is handig om een effectief herstelprogramma bij de hand te hebben, want dat lost de meeste problemen op.
Auteur Introductie:
Felix Hooker is een expert op het gebied van gegevensherstel DataNumen, Inc., de wereldleider in technologieën voor gegevensherstel, waaronder reparatie rar fout en sql-herstelsoftwareproducten. Voor meer informatie bezoek www.datanumen.com