Utiliser Excel VBA pour exécuter une requête SQL

L’une des capacités fortes d’Excel est le traitement et la visualisation des données. En ce qui concerne les données, le langage de requête structuré (SQL) est très utile car il s’agit du principal langage de programmation utilisé pour extraire les données de la base de données.

Normalement, les programmes SQL ont des interfaces de sortie de données intégrées où vous pouvez afficher les résultats de vos requêtes. Cependant, aucune de ces interfaces de sortie de données ne correspondra au niveau de flexibilité et de fonctionnalité qu’Excel pourrait offrir.

Dans ce didacticiel, nous combinerons la capacité d’extraction de données de SQL et les prouesses de traitement de données d’Excel. Ce schéma est généralement utilisé dans les industries où des centres de données sont établis, et le besoin d’une extraction de données rapide et facile est inévitable.

Le code ci-dessous connecte Excel au serveur SQL à l’aide d’un objet ADO qui permet la connexion via une source de données distante. Avec cet objet, VBA peut accéder et manipuler la base de données.

Cependant, l’objet ADODB ne vient pas automatiquement avec la bibliothèque par défaut utilisée par VBA. Pour activer l’objet ADODB, nous devons l’activer dans la barre d’outils References.

Pour ce faire, veuillez suivre les étapes ci-dessous.

Vous êtes maintenant prêt.

Le bloc de code ci-dessous a huit paramètres ; reportez-vous au tableau ci-dessous :

Sub getData(Sql As String, nRow As Integer, nCol As Integer, sheetDes As String, usrID as String, pssWrd as String, sidStr as string, hst as String)

Dim Connct As ADODB.Connection
Dim RcrdSet As ADODB.Recordset
Dim RcrdVal As Variant
Dim reference_x As Integer
Dim reference_y As Integer
Dim records_count As Integer

    
'CONNECTION STRING
connection_string = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hst)(PORT = 1521))(CONNECT_DATA = (SID = sidStr)))"
user_id = usrID
pass_word = pssWrd
    
'EXCEL PRINT LOCATION
reference_col = nCol
reference_row = nRow
    
' start ------ default connection setup & SQL execution code (Do not Edit entries)
cs = "Provider=OraOLEDB.Oracle;Data Source=" & connection_string & ";User Id=" & user_id & ";Password=" & pass_word & ";"
Set Connct = New ADODB.Connection
    Set RcrdSet = New ADODB.Recordset
    With Connct
        .CursorLocation = adUseClient
        .Open cs
        .CommandTimeout = 0
    RcrdSet.CursorType = asOpenForwardOnly
    RcrdSet.Open (Sql), Connct
    records_count = CInt(RcrdSet.RecordCount)
    ' end ------ default connection setup & SQL execution code (Do not Edit entries)

    ' start ------ default data print setup & close procedures (Do not Edit)
    If records_count > 0 Then
        RcrdSet.MoveFirst
        For x = 0 To RcrdSet.Fields.Count - 1
            ThisWorkbook.Sheets(sheetDes).Cells(reference_row, x + reference_col) = RcrdSet.Fields(x).Name
        Next
        ThisWorkbook.Sheets(sheetDes).Cells(reference_row + 1, reference_col).CopyFromRecordset RcrdSet
    End If
    End With
    RcrdSet.Close
    Connct.Close
    Set RcrdSet = Nothing
    Set Connct = Nothing
    'end ------ default data print setup & close procedures (Do not Edit)

End Sub

Supposons maintenant que nous ayons les informations suivantes sur votre base de données et la connexion à la base de données :

Host: database1
SID: database1@server.com
Username: username123
Password: pw123

Contenu de database1, table1 :

|   Names    |    Age   |    Gender    |    Sports    |
|------------|----------|--------------|--------------|
|   Juan     |    17    |    Male      |     Chess    |
|   Pedro    |    19    |    Male      |    Badminton |
|   Maria    |    25    |   Female     |  Volleyball  |
|  Rodolfo   |    29    |    Male      | Basketball   |
|   Cathy    |    18    |   Female     |    Chess     |
|  Michelle  |    21    |   Female     |   Swimming   |
|     Glen   |    24    |    Male      | Billiards    |

Requête SQL :

Select Names, Gender, Sports
from table1@database1
where Age <= 25

Pour exécuter cette requête SQL et sortir les entrées sur Sheet1 sur la colonne 1 et la ligne 1 d’un classeur Excel, utilisez le bloc de code ci-dessous :

Sub getData(Sql As String, nRow As Integer, nCol As Integer, sheetDes As String, usrID As String, pssWrd As String, sidStr As String, hst As String)

Dim Connct As ADODB.Connection
Dim RcrdSet As ADODB.Recordset
Dim RcrdVal As Variant
Dim reference_x As Integer
Dim reference_y As Integer
Dim records_count As Integer

    
'CONNECTION STRING
connection_string = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hst)(PORT = 1521))(CONNECT_DATA = (SID = sidStr)))"
user_id = usrID
pass_word = pssWrd
    
'EXCEL PRINT LOCATION
reference_col = nCol
reference_row = nRow
    
' start ------ default connection setup & SQL execution code (Do not Edit entries)
cs = "Provider=OraOLEDB.Oracle;Data Source=" & connection_string & ";User Id=" & user_id & ";Password=" & pass_word & ";"
Set Connct = New ADODB.Connection
    Set RcrdSet = New ADODB.Recordset
    With Connct
        .CursorLocation = adUseClient
        .Open cs
        .CommandTimeout = 0
    RcrdSet.CursorType = asOpenForwardOnly
    RcrdSet.Open (Sql), Connct
    records_count = CInt(RcrdSet.RecordCount)
    ' end ------ default connection setup & SQL execution code (Do not Edit entries)

    ' start ------ default data print setup & close procedures (Do not Edit)
    If records_count > 0 Then
        RcrdSet.MoveFirst
        For x = 0 To RcrdSet.Fields.Count - 1
            ThisWorkbook.Sheets(sheetDes).Cells(reference_row, x + reference_col) = RcrdSet.Fields(x).Name
        Next
        ThisWorkbook.Sheets(sheetDes).Cells(reference_row + 1, reference_col).CopyFromRecordset RcrdSet
    End If
    End With
    RcrdSet.Close
    Connct.Close
    Set RcrdSet = Nothing
    Set Connct = Nothing
    'end ------ default data print setup & close procedures (Do not Edit)

End Sub

Sub testSQLVBAConnection()

Dim sqlStr As String

sqlStr = "Select Names, Gender, Sports "
sqlStr = sqlStr & " from table1@database1 "
sqlStr = sqlStr & " where Age <= 25 "

Call getData(sqlStr, 1, 1, "Sheet1", "username123", "pw123", "database1@server.com", "database1")
End Sub

Sortie de testSQLVBAConnection :

|   Names    |    Gender    |    Sports    |
|------------|--------------|--------------|
|   Juan     |    Male      |     Chess    |
|   Pedro    |    Male      |    Badminton |
|   Maria    |   Female     |  Volleyball  |
|   Cathy    |   Female     |    Chess     |
|  Michelle  |   Female     |   Swimming   |