Excel kann praktisch alles; ob es gemacht werden sollte, um alles zu tun, ist eine andere Sache. Während die Tabelle beim Bearbeiten von Daten sehr leistungsfähig ist, ist sie beim Speichern normalisierter Daten nicht besonders gut. Nutzung von Excel zu einer relationalen Datenbank wie SQL Server Verbessert die Leistung der Anwendung.
Zunächst benötigen Sie MS Access oder das stabilere und kostenlose – SQL Server Ausdrücken. Es wird davon ausgegangen, dass dem Leser das Excel Developer-Menüband angezeigt wird und er mit dem VBA-Editor und der SQL-Struktur (Structured Query Language) vertraut ist. Dieser Artikel verwendet SQL Server Verbindungszeichenfolgen. Informationen zu MS-Access finden Sie bei Google.
Während Excel über eigene integrierte Routinen verfügt, von denen Informationen abgerufen werden können SQL Server In (z. B.) einer Pivot-Tabelle bietet unser Beispiel mehr Flexibilität bei der Datenauswahl.
Verbindungszeichenfolge
Ich werde eine private Datenbank verwenden. Fügen Sie Ihre eigenen Treiberinformationen anstelle meiner in die ConnectDatabase-Subroutine ein. Wir verwenden dann connDB als Kommunikationskanal zu unserer Datenbank - in meinem Fall, um Ergebnisse einer gespeicherten Prozedur zurückzugeben. Sie können mehr Standard-SQL-Anweisungen wie "Select * from ..." verwenden.
Geschäftsordnung
Zuerst laden wir Kombinationsfelder aus SQL Server Beim Öffnen der Arbeitsmappe mithilfe eines Auto_Open-Makros werden die Daten im Tabellenblatt „ComboData“ gespeichert. Unabhängig davon, ob sich der Server in der Cloud oder lokal befindet, tritt beim Starten von Excel keine spürbare Verzögerung auf – vorausgesetzt, die Datenbank ist vom Arbeitsplatzrechner aus erreichbar.
Als nächstes extrahieren wir gefilterte Daten aus der Datenbank und legen sie in Excel ab, Spalten F bis K.
Die Schnittstelle
Meins hat Dropdown-Felder, um Informationen aus der Datenbank zu filtern. Das Funktion / Rolle (Role) * Das Kombinationsfeld löst eine Suche aus, um die Tabelle rechts zu füllen.
Benennen Sie "Sheet1" in "Main" um. Fügen Sie mindestens eine Combobox hinzu.
Der Code des Chamäleons
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
Formatieren Sie das Kombinationsfeld-Steuerelement so, dass die Blätter „ComboData“ gelesen werden. Klicken Sie dann mit der rechten Maustaste auf das Kombinationsfeld, um ihm die Unterprozedur ReadData zuzuweisen. Wenn ein Element in der Combobox ausgewählt ist, schreiben Sie seinen Schlüssel in das Blatt „Main“, Zelle D7. Der VBA-Code verwendet diesen Schlüssel als Filter (siehe intRole oben).
Verweise auf die DLL-Bibliothek
Verwenden Sie im Codefenster „Tools > Verweise“, um auf die Microsoft Active X Data Objects-Bibliothek zu verweisen. Dadurch kann Excel die im Code deklarierten ADODB-Objekte verwenden.
Die obige ReadData-Subroutine verwendet eine unten gezeigte relationale Datenstruktur, die allein in Excel nur schwer zu erreichen ist.
Weitere Datenänderungen können ein Zurückschreiben in die Datenbank auslösen, gefolgt von der entsprechenden SQL Update-Anweisung connDB.execute (strSQL).
Schützen Sie Ihren Code schließlich vor dem Anzeigen oder Ändern: Extras> Eigenschaften> Schutz.
Behandeln Sie Excel-Probleme:
Von Zeit zu Zeit, insbesondere wenn komplexe Programme enthalten sind, stürzt Excel möglicherweise ab und kann nicht ordnungsgemäß wiederhergestellt werden. Im Falle eines beschädigt xlsx Mit einem effektiven Wiederherstellungstool lassen sich die meisten Probleme lösen.
Einführung des Autors:
Felix Hooker ist ein Datenrettungsexperte in DataNumen, Inc., das weltweit führend bei Datenwiederherstellungstechnologien ist, einschließlich Reparatur- rar Fehler und SQL Recovery-Softwareprodukte. Für weitere Informationen besuchen Sie www.datanumen.com €XNUMX