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.

1. Klicken Sie auf die zu Excel hinzugefügte Schaltfläche „Abfrage ausführen“.
2. DB-Verbindung aus VBA-Code
3. Abfrageausführung in der verbundenen DB
4. In DB ausgeführtes Abfrageergebnis zurückgeben
5. 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.

1. Klicken Sie mit der rechten Maustaste auf den Desktop > Neu > Textdokument
2. Ändern Sie den Dateinamen in test.udl und drücken Sie die Eingabetaste
3. Ö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.
4. ConnectionString kann abgerufen werden, indem die gespeicherte Datei test.udl mit einem Editor (Notepad usw.) geöffnet wird.

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