Archive for mai, 2013

Importer un flux RSS dans Excel

Il est possible d’importer un flux RSS dans Excel. Un flux RSS n’est ni plus ni moins qu’un fichier XML mis à disposition sur le Web. Nous avons vu qu’il est possible dans Excel d’importer des données Web, ainsi que des données XML. Il est donc logique qu’Excel puisse lire un flux RSS.

Tout d’abord, sélectionner le flux RSS désiré. Pour notre exemple, nous allons encore une fois rechercher des taux de change mis à disposition en flux RSS. Une rapide recherche nous fournit un flux RSS adéquat. Nous procédons alors à l’import de données Web dans Excel, menu « Données – Données externes – A partir du Web » et nous collons l’adresse du flux RSS.

Attention, sur certains flux RSS, il sera nécessaire de cliquer sur l’option « Masquer les icônes » entourée en rouge sur la capture d’écran ci-dessus. Nous validons avec le bouton « Importer » et nous obtenons un tableau Excel facilement exploitable.

L’utilisation des flux RSS dans Excel offre une fonctionnalité proche de certains Web Services !

Top

Ecrire un tri en VBA Excel

En VBA, la méthode « Sort » s’applique à des objets « Range » (à des plages de cellules). Nous avons fréquemment besoin d’écrire des sort sur des plages en VBA Excel. Comme souvent dans ce cas, nous n’avons qu’à faire appel à l’enregistreur de macros, faire un tri et adapter le code. Malheureusement, dans ce cas, le code est disons… Trop riche… Voici en effet le code généré pour effectuer un tri sur une plage A3:F75 :

Range("A3:F3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Echeancier").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Echeancier").Sort.SortFields.Add Key:=Range( _
"A4:A75"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Echeancier").Sort
.SetRange Range("A3:F75")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Franchement, ça ne donne pas envie d’écrire un tri… Voici donc la marche à suivre :

 1/ Déclarer un Objet Range,

2/ Affecter l’objet Range, dont la première ligne contient les titres,

3/ Affecter les clés de tri,

4/ Appliquer la méthode Sort à l’objet Range sur les clés désirées.

 Voici un exemple :

Dim MonEcheancier As Excel.Range
Set MonEcheancier = Range(MaFeuilleEcheancier.Cells(1, 1), MaFeuilleEcheancier.Cells(1, 1).End(xlDown).Offset(0, 5))
MonEcheancier.Sort Key1:=MaFeuilleEcheancier.Cells(1, 1), Order1:=xlAscending, Header:=xlYes

Si nous avions plusieurs clés, nous n’aurions qu’à écrire MaRange.Sort Key1:=xxx Key2:=yyy etc. Nous avons également positionné l’option Header à oui, car nous avons une en-tête qui ne sera pas triée.

Pour certains, l’affection du Range pourrait sembler confuse… Une Range simple (rectangulaire) est délimitée par 2 cellules, en haut à gauche et en bas à droite. La syntaxe de Range est donc Range (Cell1, Cell2).

Une Cellule est identifiée par son numéro de ligne et son numéro de colonne. On écrit Cells(1,5) pour la 5ème colonne de la 1ère ligne. On peut préfixer Cells par la feuille qu’on adresse, ici MaFeuilleEcheancier, qu’on aura au préalable déclarée avec :

 Dim MonProjet As Excel.Workbook
Dim MaFeuilleEcheancier As Excel.Worksheet
Set MonProjet = Excel.ActiveWorkbook
Set MaFeuilleEcheancier = MonProjet.Worksheets("Echeancier")

Enfin, .End(XlDown) permet d’adresser directement la dernière cellule non vide vers le bas (XlToRight pour aller vers la droite) et .Offset permet de se décaler de 0 lignes et 5 colonnes dans l’exemple.

Top

Améliorer les performances de la fonction RECHERCHEV Excel

Ou comment améliorer les performances de la fonction RECHERCHEV… De façon spectaculaire !

Sur quelques articles de ce site, nous avons évoqué les performances poussives de la fonction RECHERCHEV, lorsqu’elle est utilisée en mode recherche exacte (dernier paramètre à VRAI). Dommage pour la fonction la plus utile du

Pourtant, la solution pour accélérer radicalement cette fonction est vraiment simple. Tellement simple qu’on se demande bien comment diable cette correction n’est pas implémentée directement dans Excel

Il suffit juste de travailler avec cette même fonction RECHERCHEV en recherche approximative, et tester si la valeur retournée est égale à la valeur cherchée. Si nous effectuons la recherche suivante :

=RECHERCHEV(B2;clients!$A$1:$C$10001;3;FAUX)

Nous cherchons la 3ème colonne de la correspondance exacte de B2. Nous pouvons écrire très simplement :

=SI(RECHERCHEV(B2;clients!$A$1:$C$10001;1;VRAI)<>B2;NA();RECHERCHEV(B2;clients!$A$1:$C$10001;3;VRAI))

Attention, les données cibles doivent être triées !

Ce qui revient à faire une recherche approximative (qui est 50 fois plus rapide…), tester la valeur retournée, et si elle est différence, forcer le résultat à #N/A. Si elle est égale, nous renvoyons la 3ème colonne.

Simplissime, et pour les tableaux « monstrueux » à plusieurs centaines de milliers de lignes, le résultat sera sans appel. Dans un cas avec 500.000 lignes, on est passé de 14s à 1s.

La fonction RECHERCHE est lente ? Plus maintenant !

Top

Utiliser des fonctions du Framework .NET en VBA

On peut faire beaucoup, beaucoup de choses en Excel/VBA… Au fil des articles, nous avons souvent étudié la possibilité de faire interagir Excel avec d’autres applications (Power MAC, Word, PowerPoint, Business Objects…) ou avec diverses fonctionnalités Windows (gestion de fichier FSO…). Pour cela, il faut faire appel à des bibliothèques de fonctionnalités, accessibles via le menu de l’éditeur VBA « outils – références ».

S’il existe une bibliothèque de fonctionnalités (si on peut l’appeler ainsi…) bien documentée, il s’agit du Framework .NET fourni par Microsoft. Mais à priori, pour l’utiliser, nous devons avoir affaire aux langages de « haut niveaux » que sont VB.NET et C# (entre autres), ainsi qu’à l’atelier de développement Visual Studio. Pourtant, en étant (vraiment…) motivé, on peut utiliser des fonctionnalités du Framework en VBA office. Pour l’exemple, nous allons utiliser une classe du Framework 2.0 :

la classe STRINGBUILDER (SYSTEM.TEXT.STRINGBUILDER) , qui est censée être plus rapide pour traiter les chaines de caractère. L’exemple n’est pas le meilleur du monde, car on n’est pas sensé exécuter un code rapide en VBA…

Pour utiliser une classe du Framework Dotnet, il est nécessaire (comme toujours quand on utilise des bibliothèques autres que VisualBasic, Office et Excel) d’en importer la référence via le menu de l’éditeur VBA d’Excel : « Outils – Références ». Mais on ne trouvera aucune référence au Framework, .NET ou dotnet ! Il faut l’importer manuellement. Comment savoir où se trouve cette bibliothèque, dans quel fichier dll ?

Pour cela, ouvrons le programme Visual Studio (une version gratuite Visual Basic Express Edition fait parfaitement l’affaire), nous affichons l’explorateur d’objet (F2), et nous cherchons StringBuilder :

Nous affichons l’espace de nom Sytem.Text pour connaitre la dll, il s’agit de la mscorlib :

Le chemin est le suivant (pour le Framework 2.0, si on a choisi ce Framework dans les propriétés du projet Visual Studio) :

Nous pouvons désormais importer non pas la dll dans VBA, mais le fichier avec l’extension tlb

Le code suivant fonctionne parfaitement sous Visual Studio en .NET :

Pourtant, en VBA, cela ne fonctionne pas !

Cela est dû au fait que les fonctions et les procédures (les méthodes donc…) .NET peuvent avoir plusieurs signatures, et visiblement, le compilateur VBA ne s’y retrouve pas… On observe dans l’explorateur d’objets de Visual Studio (voir copie d’écran un peu plus haut) une multitude de signatures différentes pour la méthode Append de l’objet StringBuilder. Pas moins de 19 !

Pour « invoquer » une signature, il suffit (ce n’était pas trivial…) de suffixer la méthode Append par son numéro. Pour adresser la bonne signature, celle qui attend du type « String » en paramètre, il faut suffixer par un numéro mais lequel ? Et bien, cela se corse… Il faut essayer toutes les possibilités ! Sur certains forums, il est dit qu’il suffit de donner le numéro inverse de l’ordre d’apparition dans l’explorateur d’objet, mais je n’ai rien constaté de tel. Non, il faut tester toutes les combinaisons une à une, jusqu’à la suivante :

C’est donc laborieux, mais nous avons réussi ! Cette manipulation n’est bien sûr à réserver qu’à des cas bien précis, et affirmons-le tout de suite, ce sera très rare ! De plus, l’explorateur d’objet n’est pas opérationnel en VBA, l’IntelliSense n’est plus de la partie… Encore plus mystérieux, la méthode Replace fonctionne sans invoquer la bonne signature, pas de numéro à suffixer ! Comme évoqué en début d’article, cela fonctionne, mais il faut rester motivé…

Sub TestDotnet()

Dim MonStringBuilder As New StringBuilder

Set MonStringBuilder = MonStringBuilder.Append_3(« test1 numéro 1 »)

Set MonStringBuilder = MonStringBuilder.Replace(« numéro », « chiffre »)

MsgBox (MonStringBuilder.ToString())

End Sub

Top

Générer automatiquement un document PowerPoint

Voici un exemple de code pour générer automatiquement un document PowerPoint. Bien entendu, nous pourrons générer des documents PowerPoint à partir de bases de données Excel.

Sub GenerationPowerPoint()
'programme de génération d'un document Powerpoint à partir d'un modèle
 
'instanciantion PowerPoint (ajouter la référence Microsoft PowerPoint 1x.xx Library
'dans éditeur VBA menu Outils - Référence
Dim MonPowerPoint As New PowerPoint.Application
MonPowerPoint.Visible = True
 
'ouverture de la présentation modèle (1ere diapositive titre, 2ème diapositive modèle des autres diapos)
Dim MaPresentation As PowerPoint.Presentation
Set MaPresentation = MonPowerPoint.Presentations.Open2007("D:\MonModele.pptx")
 
'on recopie le modele (MonModele) pour créer des diapositives supplémentaires
'modèle composé d'un titre et d'un cadre de texte
Dim MaDiapositive As PowerPoint.Slide
Dim MonModele As PowerPoint.CustomLayout
Set MonModele = MaPresentation.Slides(2).CustomLayout
Set MaDiapositive = MaPresentation.Slides.AddSlide(3, MonModele)
 
'pour activer une diapositive avec son numéro de page
Set MaDiapositive = MaPresentation.Slides(2)
 
'saisie du titre de la diapositive
MaDiapositive.Shapes.Title.TextFrame.TextRange.Text = "Titre diapo"
 
'saisie de 2 lignes dans le cadre principal de la diapositive
'attention, ici on choisit le 3ème cadre à titre d'exemple, à adapter
With MaDiapositive.Shapes(3).TextFrame.TextRange
.InsertAfter ("ligne 1")
.InsertAfter (Chr(10))
.InsertAfter ("ligne 2")
End With
 
'ajout et saisie d'un cadre avec texte
Set MonText = MaDiapositive.Shapes.AddShape(msoShapeRectangle, 300, 300, 250, 140)
With MonText.TextFrame.TextRange
.Text = "exemple de texte en arial"
.Font.Name = "arial"
End With
 
End Sub
Top

Gestion de projet : faire un planning avec Excel

L’environnement bureautique ne comprend pas toujours d’outil de gestion de projet. Qu’importe, Excel fera largement l’affaire. On peut en effet faire des plannings plutôt design, en quelques manipulations.

Tout d’abord, saisissons notre planning en mode « texte » comme ci-dessous (planning totalement fictif pour l’exemple).

Nous avons positionné les colonnes Direction, Projet, Chantier et Equipe. Nous avons également placé des numéros de semaine, ainsi que les nombre de jours de charge par équipe. En réfléchissant un peu, nous pouvons percevoir l’intérêt d’un tel planning : il ressemble plus à une base de données qu’à un planning classique (diagramme de GANT). Il sera donc très facile de le retravailler par du code VBA, de consolider plusieurs planning, d’avoir une vision synthétique en utilisant des filtres, etc…

Et nous pouvons insister lourdement sur un point : quand on construit un planning Excel, il n’est pas très judicieux d’utiliser des rectangles graphiques de couleur que l’on déplace laborieusement à la souris… Nous allons voir que l’on peut faire plus simple à partir du planning « texte » précédent.

Dans un premier temps, sélectionnons la plage de valeur du planning (E2:W31 dans l’exemple), puis nous allons faire une mise en forme conditionnelle personnalisée avec le choix « Accueil – Mise en forme conditionnelle – Nouvelle Règle » :

Et cliquons sur « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué ».

Si la première cellule de notre plage était E2, nous la sélectionnons. Le « générateur » de formule va commencer à écrire =$E$2. Ce que nous voulons mettre en forme, ce sont les cellules qui ont une valeur supérieure à 0. Et nous devons supprimer les $, car la mise en forme conditionnelle ne doit pas s’appliquer en référence absolue. Enfin, dans le format, nous sélectionnons une couleur de remplissage et une couleur de police identiques. Voici alors le résultat :

Pour mettre à jour notre planning, nul besoin de retoucher des formes graphiques à la souris. Nous pouvons saisir un chiffre, et automatiquement, la cellule se colore. La touche SUPPR a l’effet inverse.

Pour conclure cette première partie, nous pouvons rappeler aux utilisateurs d’Excel 2007 et 2010 qu’il existe des options plus simples de mise en forme conditionnelles pour arriver à ce résultat. Mais la saisie de cette formule fonctionne bien avec Excel 2003 et sa mise en forme conditionnelle moins riche.

Maintenant, nous allons utiliser les sous-totaux multiples afin de « consolider notre planning ». Sélectionnons une cellule du planning, et allons dans le ruban Données, sur le choix « Données – Plan – Sous-total ». Nous allons alors cocher toutes les semaines, pour effectuer le sous-total de chaque semaine, à chaque changement de direction (on commence par le niveau le plus général).

Nous obtenons alors une ligne supplémentaire. Grâce à la mise en forme conditionnelle, cette ligne de consolidation est déjà colorée, et apparaît sous la forme d’une ligne de planning.

Nous allons maintenant effectuer le sous-total au niveau du Projet, en veillant à décocher « Remplacer les sous-totaux existant ».

Et répétons l’opération pour le dernier niveau de consolidation, le Chantier. Nous pourrons alors constater que les lignes de sous-totaux insérées ont un petit souci de mise en forme : les cellules non-colorées sont à 0. Il faudra alors à nouveau effectuer une mise en forme conditionnelle du style : si la valeur est 0, alors couleur de police = blanc. Le résultat est alors le suivant :

Nous voici maintenant avec notre planning consolidé. Si nous cliquons sur le niveau 4 de consolidation, nous obtenons :

Voilà, notre exemple de planning est ainsi créé. Avec les filtres automatiques, nous pouvons ne pas afficher les lignes totaux inutiles, pour plus de visibilité.

Top
1 2 3 5 Page 1 of 5

Contactez-nous contact@expert-data.fr

EXPERT data, solutions pour TPE/PME et grands comptes