Connexion à Oracle avec ODBC depuis une Macro Excel

Une opération qui pourrait sembler évidente mais dont la mise en œuvre relève du chemin de croix est le branchement de Excel à une base de données Oracle. Cette solution idéale pour extraire les informations directement depuis une base de données à partir d’une macro fait l’objet de très peu de documentation (en français).

L’intérêt d’utiliser une macro est de permettre de construire des requêtes dynamiques à partir du contenu des cellules. Pour cela, il a fallu raccorder l’ordinateur utilisant Excel à la base Oracle au moyen d’une connexion ODBC qui sera utilisée dans la macro.

Afin de garder une trace des actions réalisées pour le paramétrage de la connexion et l’utilisation de la connexion ODBC depuis la macro Excel, voici un petit article qui reprend l’ensemble des opérations nécessaires à la mise en place de la connexion.

Télécharger le driver ODBC depuis le site de Oracle

C’est la première chose à faire pour débuter. Cette opération est à réaliser sur tous les postes qui vont utiliser Excel pour se connecter à Oracle. Chaque utilisateur doit avoir un driver ODBC vers Oracle afin de pouvoir utiliser l’extraction de données à partir de la macro Excel.

Pour cela, il faut télécharger les fichiers d’installation depuis le site officiel de Oracle. Une recherche à partir d’un moteur de recherche est le meilleur moyen d’arriver sur cette page. Le lien direct n’est volontairement pas disponible dans cet article car les URL changent régulièrement.

Il est nécessaire de créer un compte chez Oracle pour pouvoir télécharger le driver. La création du compte prend 2 minutes.

Sur la page des téléchargements, il existe autant de drivers que de version de Oracle encore maintenues. Le driver à télécharger doit correspondre à la version de Oracle installée sur votre serveur. Dans cet exemple, il s’agit de la version 11.2.X.Y

Parmi les téléchargements disponibles il faut préalablement télécharger le Instant Client Package Basic pour récupérer le driver.

Il est nécessaire de télécharger le complément pour utiliser le driver ODBC dans des applications externes.

Il y a donc 2 fichiers à télécharger. Suite à ces téléchargements, les archives sont placées dans le même répertoire et portent les noms indiqués ci-dessous.

Une fois téléchargés, ces fichiers sont à dézippés dans un même répertoire. Dans cet exemple, il s’agit de C:\Oracle\instantclient_11_2\

L’installation du driver peut maintenant être faite. Dans le complément pour ODBC, il faut lancer l’installation avec le programme odbc_install.exe. Suite à cette installation, le driver ODBC va maintenant être disponible pour l’ajout d’une nouvelle connexion ODBC dans l’utilitaire Windows.

Toujours dans le répertoire C:\Oracle\instantclient_11_2\, il fait maintenant créer le fichier tnsnames.ora qui contient les paramètres réseau de connexion au serveur Oracle.

APPLI_PROD =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS =
       (PROTOCOL = TCP)
       (HOST = APPLI_CLUSTER)
       (PORT = 1369)
      )
    )
    (CONNECT_DATA =
     (SERVICE_NAME = APPLI)
    )
  )

Le nom APPLI_PROD sera celui à renseigner lors du paramétrage de la connexion ODBC.

Enfin, il faut ajouter une nouvelle variable d’environnement TNS_ADMIN contenant le chemin vers le fichier tnsnames.ora

Créer la source de donnée ODBC

Une fois le driver Oracle installé, il est maintenant possible de créer une nouvelle connexion ODBC vers le serveur de base de données. Il faut pour cela utiliser l’assistant disponible avec Windows.

Par défaut, Windows propose 3 sources de données. On va en ajouter une 4ème vers notre serveur Oracle.

Microsoft propose son propre driver vers Oracle mais celui-ci est obsolète et sera désactivé dans les années à venir. Microsoft recommande d’utiliser le driver officiel d’Oracle. C’est celui qui a été récupéré au début de l’installation et qui va permettre de paramétrer la nouvelle source de données.

Plus bas dans la liste, on retrouve le driver officiel de Oracle que nous avons installé en début de cet article. C’est bien celui-ci qu’il faut utiliser pour créer la connexion ODBC.

Ensuite, il reste à renseigner les paramètres de connexion et l’identifiant de la connexion présent dans le fichier tnsnames.ora paramétré précédemment.

Ensuite, il faut demander le test de la connexion. Une pop-up devrait s’ouvrir et vous permettre de renseigner le mot de passe correspondant au User ID.

Si tout est bien paramétré, alors le message « Connection successful » s’affiche dans une popup.

Utiliser la connexion ODBC dans Excel

Maintenant que la connexion APPLI_PROD a été paramétrée localement, elle est disponible depuis une macro Excel pour réaliser des sélections depuis la base de données Oracle.

Voici un exemple de l’utilisation de la connexion ODBC depuis la macro Excel. Il y a une procédure ExecuterRequete qui reçoit en paramètre une requête SQL et qui remplit l’onglet « Resultat ». Une fois cet onglet rempli, il n’y a plus de limite à votre imagination pour travailler sur ces données.

' Exécute un ordre SQL en paramètre et alimente l'onglet "Résultat"
Sub ExecuterRequete(reqSQL)
  ' Effacement de données de l'onglet Résultat
  Worksheets("Résultat").Cells.Clear
  With Worksheets("Résultat").QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=APPLI_PROD;UID=APPLI_USER;PWD=AZERTYU;DBQ=APPLI_PROD ;DBA=R;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS" _
        ), Array( _
";DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;AST=FLOAT;" _
        )), Destination:=Worksheets("Résultat").Range("$A$1"), Sql:=reqSQL)
    .BackgroundQuery = False
    .Refresh
  End With
End Sub

Lors du premier appel au driver ODBC, Excel va demander de choisir la connexion à utiliser et de renseigner le mot de passe. Cette étape est à faire uniquement la première fois. Pour les prochains appels, Excel va réutiliser l’ancienne connexion ODBC.

Cette procédure générique peut être appelée à l’intérieur d’une boucle pour enchainer les requêtes et les retravailler ensuite. Voici un exemple de l’utilisation de l’utilisation de la procédure ExecuterRequete.

' Fonction pour exécuter la requête Extraction_Client
reqSQL = "select nomClient as Nom, prenomClient as Prenom, " _
       & "sum(b.monta/100) as Montant " _
       & "from client a, clientFacture b " _
       & "where a.idClient=b.idClient and b.statutFacture='EC' " _
       & "and b.anneeFacture like '%2019%' " _
       & "GROUP BY nomClient, prenomClient"
Call ExecuterRequete(reqSQL)

Ainsi après cette requête, l’onglet « Resultat » est rempli avec les données extraites. Il suffit de compléter la macro pour faire de la mise en forme, des contrôles, du dispatching… Bref, de laisser votre créativité s’exprimer !