Accueil » Actus, Informatique

Formater une extraction dans Excel

24 avril 2011 1 681 vues 0 commentaire

Dans le cadre de certains de mes travaux, il m’arrive très souvent de récupérer des fichiers venant de requêtes diverses sous QMF, SAP ou autres. Pour en tirer le meilleur parti, il me faut ensuite les intégrer dans Excel. Cela permet ensuite de les exploiter de manière automatique avec différentes macros Excel ou avec VB. Je réutilise souvent les mêmes astuces pour rendre le fichier plus propre dans Excel. Voici ces quelques recettes pour s’en sortir rapidement.

Il est assez rare qu’une requête restitue toujours le même nombre de lignes. Dans mon cas, de mémoire, c’est jamais. L’astuce principale pour traiter automatiquement des résultats de requêtes, c’est de savoir identifier où se trouve la dernière ligne (soit le dernier enregistrement de la requête).

Ce n’est pas aussi simple qu’il y paraît car les restitutions générées par les requêteurs nous compliquent la vie. Pour QMF, le fichier texte intégré dans Excel donne le résultat sous la configuration suivante (1 ligne d’entête, un nombre fixe de lignes vides, les enregistrements qui nous intéressent mais pour lesquels nous ne connaissons pas le nombre, un nombre fixe de lignes vides et une ligne d’informations sur la date de la requête) :

  A B C
1 Entete 1 Entete 2 Entete 3
2      
3 Valeur 1 Valeur 2 Valeur 3
4   Valeur 2 Valeur3
5      
6 Date et heure de la requête    

Mon but est de formater les données pour retirer la ligne vide en-dessous de la ligne d’entête et les lignes en-dessous de celle du dernier enregistrement. Pour retirer la seconde ligne qui sera toujours présente dans les fichiers reçus, il suffit de la sélectionner et de faire un delete:

Rows("2:2").Delete Shift:=xlUp
  A B C
1 Entete 1 Entete 2 Entete 3
2 Valeur 1 Valeur 2 Valeur 3
3   Valeur 2 Valeur3
4      
5 Date et heure de la requête    

 

C’est maintenant qu’il faut être astucieux. Il faut avoir une colonne pour laquelle on est certain que la requête renvoie toujours une valeur non vide quelque soit l’enregistrement. Dans mon cas, la colonne A ne fait pas l’affaire. Par contre, je sais que c’est le cas pour la colonne B. Je vais donc pouvoir repérer le dernier enregistrement de mon fichier grâce à cette colonne. Pour cela, j’utilise la formule subtotal de la façon suivante :

' Sélection de la colonne qui nous intéresse
Columns("B:B").Select
' Définition de la plage de valeurs
Colonne = ActiveCell.EntireColumn.Address
' Mise en place du calcul
Nombrenreg = Evaluate("subtotal(3, " & Colonne & ")")

Dans l’exemple, Nombrenreg sera égal à 3.

Ensuite pour supprimer les lignes superflues, il suffit de faire (on doit supprimer les deux lignes qui suivent le dernier enregistrement) :

rows((Nombrenreg+1) & ":" & (Nombrenreg +2)).Delete Shift:=xlUp

Ce qui a permis de nettoyer le tableau pour obtenir:

  A B C
1 Entete 1 Entete 2 Entete 3
2 Valeur 1 Valeur 2 Valeur 3
3   Valeur 2 Valeur3

On obtient ainsi une table qui sera beaucoup plus exploitable sous excel. Très utile quand les commandes suivantes de la macro utilisent des filtres.

Vous ne pouvez plus déposer de commentaires car ils sont clos.