VBA-Codierungsmuster: Importieren von DB-Abfrageergebnissen in Excel
Einführung des VBA-Codierungsmusters, das den Import von DB-Abfrageergebnissen in Excel implementiert.
In Verbindung stehende Artikel: VBA-Codierungsmuster: ADO-DB-Verbindungszeichenfolge erstellen
1. Excel-Bildschirm und Betriebsmethode
1.1. Excel-Bildschirm
Geben Sie die Verbindungszeichenfolge zum Herstellen einer Verbindung mit der Datenbank in Zelle „B1“ ein, und geben Sie die SQL-Abfrage zum Extrahieren von Daten in Zelle „B2“ ein.
Klicken Sie auf „Entwicklungstools > Einfügen > ActiveX-Steuerelement > Befehlsschaltfläche“, um eine Schaltfläche hinzuzufügen und den Namen in „Abfrage ausführen“ zu ändern.
Ein Beispielbildschirm ist wie folgt.
1.2. So importieren Sie DB-Abfrageergebnisse in Excel
Es funktioniert genauso wie im Bild oben.
- Klicken Sie auf die zu Excel hinzugefügte Schaltfläche „Abfrage ausführen“.
- DB-Verbindung aus VBA-Code
- Abfrageausführung in der verbundenen DB
- In DB ausgeführtes Abfrageergebnis zurückgeben
- Abfrageergebnis in Excel-Tabelle anzeigen
2. Wählen Sie Anbieter für DB-Verbindung aus
„2. Dies ist der Provider, der in „DB Connection in VBA Code“ verwendet wird. Sie kann als Provider-Eigenschaft des ADO.Connection-Objekts oder als ConnectionString angegeben werden.
Normalerweise wird es als ConnectionString bezeichnet, und ein Beispiel ist wie folgt.
- MS OLE DB-Anbieter für ODBC-Treiber
- Anbieter=MSDASQL.1; Passwort=[Passwort]; Persist Security Info=True; Benutzer-ID=[BenutzerID]; Datenquelle=[DSN in ODBC-Systemsteuerung festgelegt]; Anfangskatalog=[DB-Name]
- MS OLE DB für SQL Server (SQL Server-Authentifizierung)
- Anbieter=SQLOLEDB.1; Passwort=[Passwort]; Persist Security Info=True; Benutzer-ID=[BenutzerID]; Anfangskatalog=[DB-Name];Datenquelle=[Serveradresse (IP oder Hostname)]
- MS OLE DB für SQL Server (Windows-Authentifizierung)
- Anbieter=SQLOLEDB.1; Passwort=[Passwort]; Integrierte Sicherheit=SSPI; Persist Security Info=False; Benutzer-ID=[BenutzerID]; Anfangskatalog=[DB-Name];Datenquelle=[Serveradresse (IP oder Hostname)]
- Oracle-Anbieter für OLE DB
- Provider=OraOLEDB.Oracle.1;Passwort=[Passwort]; Persist Security Info=True; Benutzer-ID=[BenutzerID]; Datenquelle=[Serveradresse (IP oder Hostname)]
Wenn ein geeigneter Provider auf dem PC installiert ist, können Sie sich mit RDBMS-Servern wie Oracle, MS SQL Server, MySQL, MariaDB und PostgreSQL verbinden und auch mit lokalen RDBMS wie MS Access und SQLite verbinden.
ConnectionString kann auf folgende Weise erstellt werden.
So erstellen Sie ConnectionString 1. Verwenden der UDL-Datei
Universal Data Link (UDL)-Dateien sind ein Konzept, das in Microsoft Data Access Components (MDAC) enthalten ist.
Bezug: Universal Data Links (UDL) konfigurieren – OLE DB-Treiber für SQL Server | Microsoft-Docs
Führen Sie das folgende Verfahren aus.
- Klicken Sie mit der rechten Maustaste auf den Desktop > Neu > Textdokument
- Ändern Sie den Dateinamen in test.udl und drücken Sie die Eingabetaste
- Öffnen Sie die Datei test.udl per Doppelklick, wählen Sie im Reiter „Provider“ einen Provider aus, geben Sie im Reiter „Verbindung“ Server, Benutzername, Passwort, Datenbank etc. ein, „Verbindung testen“ und dann „OK“. " speichern.
- ConnectionString kann abgerufen werden, indem die gespeicherte Datei test.udl mit einem Editor (Notepad usw.) geöffnet wird.
![](https://prodskill.com/wp-content/uploads/2022/09/image-191.png)
![](https://prodskill.com/wp-content/uploads/2022/09/image-192.png)
Bei dieser Methode wird der Anbieter entsprechend der 32-Bit- oder 64-Bit-Plattform des Windows-Betriebssystems angezeigt. Das heißt, in 64-Bit-Betriebssystemen werden nur 64-Bit-Anbieter angezeigt. Wie der 32-Bit-Anbieter auf einem 64-Bit-Betriebssystem angezeigt wird, wird in einem separaten Artikel beschrieben.
So erstellen Sie ConnectionString 2. VBA-Code
vorherigen Post VBA-Codierungsmuster: ADO-DB-Verbindungszeichenfolge erstellen wurde ausführlich erläutert in In Summe.
- Microsoft ActiveX Data Objects 2.8-Bibliotheksreferenz hinzugefügt
- Verweis auf Microsoft OLE DB Service Component 1.0-Typbibliothek hinzugefügt
- Erstellen Sie eine ConnectionString mit dem folgenden Code
Private Sub btnBuildConnectionString_Click() On Error GoTo 0 Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range Dim eOrgXlEnableCancelKey As XlEnableCancelKey Set oCurrentRange = ActiveCell Set MSDASCObj = New MSDASC.DataLinks eOrgXlEnableCancelKey = Application.EnableCancelKey Application.EnableCancelKey = xlDisabled Set cn = New ADODB.Connection cn.ConnectionString = oCurrentRange If MSDASCObj.PromptEdit(cn) = True Then oCurrentRange = cn.ConnectionString End If Set cn = Nothing Set MSDASCObj = Nothing Application.EnableCancelKey = eOrgXlEnableCancelKey End Sub
Anbieterbezogene Vorsichtsmaßnahmen
Die 32-Bit- und 64-Bit-Plattformen des zu verwendenden Anbieters und Excel müssen gleich sein. Wenn Sie 32-Bit-Excel verwenden, müssen Sie einen 32-Bit-Anbieter verwenden, und wenn Sie 63-Bit-Excel verwenden, müssen Sie einen 64-Bit-Anbieter verwenden.
Wenn Sie ein 64-Bit-Windows-Betriebssystem und 32-Bit-Excel verwenden und versuchen, eine Verbindung zur Datenbank mit dem in der ersten Methode erstellten ConnectionString herzustellen, kann der Fehler „Anbieter kann nicht gefunden werden“ auftreten.
Dieser Fall kann gelöst werden, indem geprüft wird, ob die Plattformen von Excel und dem Anbieter identisch sind und ein geeigneter Anbieter verwendet wird.
3. Implementierung des Imports von DB-Abfrageergebnissen in Excel
3.1. Referenz hinzufügen
Führen Sie „Extras > Verweis hinzufügen“ im Menü des VBA-Code-Editors aus und fügen Sie den Verweis „Microsoft ActiveX Data Objects 2.8 Library“ hinzu.
3.2. VBA-Code zum Importieren von DB-Abfrageergebnissen in Excel
Schreiben Sie den folgenden Code in die Klickereignisprozedur der Schaltfläche, die der Excel-Tabelle hinzugefügt wurde.
Private Sub cmd_RunQuery_Click() Dim sSQL As String Dim oCon As ADODB.Connection, oRS As ADODB.Recordset Dim sConnectionString As String sConnectionString = Range("B1").Value2 sSQL = Range("B2").Value2 Set oCon = New ADODB.Connection Set oRS = New ADODB.Recordset oCon.ConnectionTimeout = 10 oCon.Open sConnectionString oCon.CommandTimeout = 60 oRS.Open sSQL, oCon, adOpenForwardOnly, adLockReadOnly Dim oSht As Worksheet, lColIdx As Long Set oSht = Worksheets.Add(After:=Me) For lColIdx = 0 To oRS.Fields.Count - 1 oSht.Range("A1").Offset(0, lColIdx).Value2 = oRS.Fields(lColIdx).Name Next lColIdx oSht.Range("A2").CopyFromRecordset oRS oRS.Close: Set oRS = Nothing oCon.Close: Set oCon = Nothing End Sub
Die Erklärung des obigen Codes ist wie folgt.
- Zeile 6: Holen Sie sich den Wert der Zelle „B1“ der Excel-Tabelle als Variable sConnectionString.
- Zeile 7: Importieren Sie den Wert der Zelle „B2“ der Excel-Tabelle in die sSQL-Variable.
- Zeile 12: Stellen Sie eine Verbindung zur Datenbank her, indem Sie den sConnectionString-Wert an das ADODB.Connection-Objekt oCon übergeben.
- Zeile 15: Führen Sie die SQL-Abfrage auf der Datenbank aus und erhalten Sie das Ergebnis in das ADODB.Recordset-Objekt oRS.
- Zeile 18: Erstellen Sie ein Blatt oSht, um das Ergebnis der Ausführung der SQL-Abfrage anzuzeigen.
- Zeilen 19-21: Gibt die Liste der Felder (Spalten) des Objekts oRS in der ersten Zeile des Blatts oSht aus.
- Zeile 22: Geben Sie den Inhalt von oRS (SQL-Abfrageausführungsergebnis) in Zelle „A2“ des oSht-Blatts aus.
3.3. Beispieldatei herunterladen
Sie können die Beispiel-Excel-Datei mit dem oben beschriebenen Code im Github-Link unten überprüfen.
Dateilink: github DAToolset/VBACode/VBA-Codierungsmuster - DB Query1.xlsm
Vielen Dank für den Beitrag ~ Da ich dem gefolgt bin, läuft die Abfrage gut in der Datenbank und die Werte werden auch in anderen Blättern gespeichert. Eine Frage ist: Wie kann ich die Abfrage aufrufen, wenn die Abfrage so lang ist, dass die SQL-Abfrageanweisung nicht in Zelle B2 enthalten sein kann? Ich weiß nicht, ob Sie die Antwort sehen werden, aber wenn ja, würde ich mich sehr darüber freuen!
Vielen Dank für Ihren Besuch und Kommentar.
Wenn die SQL-Abfrage sehr lang ist, ist es besser, den Inhalt der Abfrage als separate SQL-Datei zu speichern und den Inhalt der Datei in den VBA-Code zu importieren.
Ich werde dazu in naher Zukunft einen separaten Beitrag schreiben.