Manipulations avancées excel Fermé

Question

Bonjour,

Je sais pas trop ou m'adresser, j'ai besoin d'utiliser des fonctions avancées d'excel (ou vba?) pour me faciliter le boulot:

On me génère a peu pres tout les 15 jours des fichiers excel.(ces fichiers sont déposés dans le meme dossier, et nommés export_jj-mm-aaaa.xls)

Ces fichiers contiennent les noms d'équipement réseaux (environ 180) et les ports(de 1 a 48) sur lesquelles des machines ont été vues pendant les 15 derniers jours.

Pour savoir quel port n'as pas été utilisé depuis plus de 6 mois je dois regarder quels ports ont été utilisés sur les 6 derniers mois (une 12aine de fichiers)
et en déduire quels ports n'ont pas été utilisés.

Il faudrai donc une sorte de formule (ou code) qui aye chercher les deux collones qui m'interessent sur tout les fichiers du repertoire ou sont déposés le fichiers,

que la colonne du nom des équipement soit filtrée sans doublon dans une nouvelle colonne,

qu'en face de chaque equipement, j'ai le nombre d'occurence de chaque port...

rien que ca...

Sauriez vous ou je peut m'adresser pour ce type de demandes ?

Merci de votre aide.

Utilisateur anonyme · Answer

Bonjour,

Aurais-tu un fichier Excel pour comprendre comment il est structuré. Comment est identifié l'utilsation d'un équipement sur un port, une croix, une date,... ? Les équipements sont sur la colonne A et les ports sur la ligne 1 ?

Je pense que c'est  réalisable avec un fichier excel qui va lire les données des autres fichers excel qui sont dans le répertoire, "si j'ai bien compris". Ca demande, cependant, du temps de programmation.

agi67.fr

Raymond PENTIER · Answer

Bonjour.

"tout les 15 jours des fichiers excel.(ces fichiers sont déposés dans le meme dossier, et nommés export_jj-mm-aaaa.xls)" 
"utilisés sur les 6 derniers mois (une 12aine de fichiers)"

Si j'ai bien lu, il y a par semestre environ douze fichiers qui portent tous la date de leur création dans leur nom ?
Mais chaque fichier peut comporter combien de feuilles au maximum ?

tchernosplif · Answer

Bonjour, 

Dans la mesure ou les fichiers "sources" gardent toujours la même structure et  ont seulement le paramètre "date" qui change dans le nom. Alors la formule INDIRECT vous permettra de récupérer vos données. Il faudra bien sur prévoir 12*? colonnes et renseigner les 12 dates pour actualiser le fichier. 
Le filtrage des doublons n'est pas obligatoire, j'imagine que la formule SOMMEPROD ferait les comptes pour les 8640 combinaisons.

hugoodu13 · Answer

Merci de l'interet que vous portez à mon probleme!

Pour etre plus précis sur la structure des fichier générés par l'export,

sur a peu pres 5000 lignes j'ai la correspondances des ports en colone Equipements réseau en colonne G et ports en colone H.


J'ai réussit à utiliser SOMMEPROD ainsi les ports étant vu zero fois sont libérable !

Mais j n'arrive pas a faire chercher les valeurs dynamiquement dans les fichier.

N'est il pas possible de dire a excel d'aller chercher les valeurs de tout les fichiers export*.xls de récupérer leur colones G et H et de les mettre à la suite dans la colonne A et B ?

Ainsi je pourrai faire le bilan sur l'année en collant juste les export de l'année dans le dossier en question.

Ou faire le bilan sur 3 mois en collant juste les 6 fichiers export ?

Merci à tous.

eriiic · Answer

Bonjour, 

A mon avis plutôt que de démarrer une usine à gaz tu devrais rassembler toutes tes données dans un fichier unique, quitte à ajouter une colonne date. 
Un copier-coller tous les 15 jours ça devrait être dans les limites du possible... 
(bon c'est vrai, la 1ère fois arme toi de courage, il va y en avoir 12 à faire)
Ensuite tout devient extrêmement simple. 

eric

Utilisateur anonyme · Answer

Bonjour

Je t'envoie cette macro à insérer dans le module macro de ton fichier synthèse:

Sub ReseauPort()
Path = "d:\test\ccm\fichiers\" ' chemin où se trouve les fichiers
ChDir Path
i = 1
j = 1
Var = Dir("*.*") ' premier classeur

Do Until Var = ""
        For i = 1 To 20 ' remplacer 20 par le nombre de lignes maximum qu'un fichier peu comporté

            Cells(i, j).Select
            colg = "='d:\test\ccm\fichiers\[" & Var & "]Feuil1'!" & "R" & i & "C7" 'on incrémente la ligne, colonne 7= G
            ActiveCell.FormulaR1C1 = colg
            Cells(i, j + 1).Select
            colh = "='d:\test\ccm\fichiers\[" & Var & "]Feuil1'!" & "R" & i & "C8" 'on incrémente la ligne, colonne 7= H
             ActiveCell.FormulaR1C1 = colh
   
 Next i
j = j + 2 ' permet d'incrémenter les colonnes

Var = Dir 'changement de dossier

Loop

End Sub

Principe: La macro va chercher dans le répertoire d:\test\ccm\fichiers (à adapter pour ton ordinateur) tous les fichiers, les uns après les autres.
Elle colle dans le fichier synthèse la formule qui permet d'aller chercher dans le classeur la feuille feuil1 (nom à changer si les onglets des feuilles ont un nom particulier) la première donnée en G1 puis H1. La ligne est incrémentée de 20 donc on prend les valeurs des 20 premières lignes.
On change de classeur et les données vont être récupérées de la même manière. La valeur j permet de décaler sur le classeur synthèse de la colonne A et B à C et D etc.
Je n'ai pas trouvé d'instruction pour connaître le nombre de lignes à explorer dans le classeur qu'on traite, c'est la raison pour laquelle j'ai mis un chiffre 20 à remplacer peut être par tes 5000 lignes dont tu parlais hier
On aurait pu aussi passer par des commandes SQL . je maîtrise moins.

hugodu13 · Answer

hey!! ! c'est excellent ca !! Merci Agi67 tu m'as redonné un grand soufle d'espoir !!!!

il me reste quand meme des problemes,

-la seule feuille présente par fichier à le meme nom que le fichier.
mais j'ai testé de nommer Feuil1 et ca marche !!

-c'est extremement long, le probleme c'est que les formules que j'utilise pour déterminer qu'un port n'as pas été vu occupé sont super lourdes!
et du coup a chaque fois qu'une cellule est modifiée, excel fait bosser mon CPU a fond.

Une idée pour que peut importe le nom de la feuille ?
Une idée pour accelerer le processus ? coller toute une colonne d'un coup ? arreter les fonctions d'excel pendant la copie des données ?

Encore un grand merci à tous!

Maxime · Answer

Just my two cents : il n'était pas plus facile de faire un petit script d'importation de l'excel dans une base ? 

Après un chti query a exécuter et le patron il est tout content !

Moi c'est comme ça que j'aurais fait parce que le boulot répétitif me saoûle, mais comme je disais, c'est juste une façon de voir.

hugodu13 · Answer

je crois pas pouvoir améliorer mes fonctions plus qu'elle ne le sont malheureusement :(

180equipements réseaux disposant de soit de 48 ports 100mbps soit 24 ports Gbps et en plus les fichiers générés nomment de 3 facons les ports...

ca fait quand meme énormissime comme données à traiter en meme temps :(

avec  application.screenupdating=false 
ca boost le processus clairement  lorsque les données sont extraites de petits fichiers, mais avec mes fichiers finaux ca repart en motée de CPU et plus qu' a attendre :(

J'aurai jamais fait bosser mon i7 autant ^^

Je vais peut être m'orienter vers la solution proposée par maxime, mais là je met les pieds dans l'inconnu...

D'autres idées pour accelerer tout ca ?

michel_m · Answer

Bonjour Hugo, 
Je t'avais demandé des renseignements mais pas de réponses... mais je ne veux pas dérangé! 
enfin, si tu mets enfin un extrait de ton classeur source, m'autoriseras tu à te proposer une solution avec des classeurs restant fermés et SQL ? 

histoire de rester constructif et de faire peut-^tre avancer le schmilblic , ci dessous une petite fonction bricolée qui teste la syntaxe de tes fichiers et leur date  

Function tester_anciennete(fichier As String, nbre_mois As Byte) As Boolean 
Dim ladate As Date, separe 
    tester_anciennete = False 
    If Not fichier Like "*##-##-####*" Then Exit Function 
    separe = Split(Mid(fichier, 8, 10), "-") 
    ladate = DateSerial(separe(2), separe(1), separe(0)) 
    If DateDiff("m", ladate, Date, vbMonday) <= nbre_mois Then tester_anciennete = True 
End Function

mais encore une fois, je ne veux pas dérangé. 
Michel

hugodu13 · Answer

Bonjour ,

Voici deux exemples:
http://www.cijoint.fr/cjlink.php?file=cj201012/cijZ7uTGaW.zip
- fichier d'export (modifié pour des raison de confidentialités)
- le fichier que j'ai crée pour obtenir les ports libérables

@eriiic (premier post)
Prendre la peine de coller 24 colones réparties sur 12 fichiers à chaque fois que l'on dois libérer des ports n'est pas insurmontable,
mais j'esperai pouvoir automatiser tout ca de A à Z. 

@michel_m désolé de ne pas t'avoir répondu, j'étais trop pris d'interet pour le code d'AGI67 ! :) 
Pour répondre à ton premier post, ce qui m'interesse c'est de connaitre les ports libres sur chaque équipement.

Un gros Merci pour ton code! Je pense que ca va m'être indispensable pour ne prendre en compte que les exports des 6 derniers mois !!

Pour l'instant je me refocalise sur la lourdeur des calculs :(

Si je dois ouvrir le fichier en utilisant un cluster de calcul ca va pas être pratique , et pas dit qu'excel répartisse le calcul sur tout les noeuds :p
(j'en ai parlé à mes collègues, ils se foutent de ma gueule)

Le principe qu'eriiic évoquais du bouton qui lance les calcul à la demande pourrai aussi me convenir, je vais tester, merci eriiic

Pour a solution Sql il faut mettre en place un serveur dédié ?


Merci a tous!

hugodu13 · Answer

Merci,

Les fichiers originaux sont bien au format aaaa-mm-jj version excel 2003.

C'est en enregistrant le fichier une fois les infos confidentielles retirées que je l'ai mis en format 2007.

il n'y a pas tout les  "noms de ports" possibles des fichiers originaux.

les noms des équipements sont confidentiels ils n'ont rien a voir avec ce que j'ai mis.

Je dispose également des export  au format csv valeurs séparées par des virgules.

Pour l'instant je me suis focalisé sur le format excel, j'esperai pouvoir en tirer un résultat correcte.

ADO ca me parle pas du tout ^^

Désolé de ne pas pouvoir donner plus d'info, c'est sur que de toute facon il faudra que je mette de ma patte pour ce projet.

Je vais essayer de fouiner coté ADO voir si je comprends quelquechose :)

Utilisateur anonyme · Answer

Bonsoir hugodu13

J'ai regardé ton fichier excel et effectivement c'est un peu une usine à gaz. Je pense que les fichiers csv seraient plus facilement exploitables car les données sont en fichier texte. Il faudrait seulement un programme pour manipuler les données. la virgule normalement est le marqueur entre les données.
 
En ce qui concerne ADO, ça permet d'accéder aux fichiers excel fermés. Si tu n'as jamais travaillé avec, ce n'est pas évident. Il faut déclarer une variable de connexion souvent appelé cn. Cette variable permet d'ouvrir le conduit vers le fichier excel avec des instructions ;
With Cn
        .Provider = "MSDASQL"
        .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
            "DBQ=" & Fichier & "; ReadOnly=False;"
        .Open
    End With
Ensuite il faut lancer une requête type SQL ppour récupérer les données.

Il est important que dans VBE tu ailles dans le menu outils/références../ et valider Microsoft Office 12.0 Object library, sinon il te manque cette bibliothèque et ton programme ne fonctionne pas.


Je te donne le lien vers un site qui explique correctement le processus https://silkyroad.developpez.com/VBA/ClasseursFermes/

J'essaierai de regarder en passant par les fichiers csv, si tu en as un à me procurer avec une dizaine de lignes de données ça suffit. Je ne te promets rien car mon temps en ce moment m'est compté. 

Agi67.fr

hugodu13 · Answer

Merci, 

si j'ai bien compris le but est d' utiliser des requetes sql au sein d'excel pour rappatrier les données,

mais la lourdeur de mes matricielles sera toujours présent ou des requetes sql pourront aussi résoudre ca ?

On s'éloigne du monde connu pour moi :x

hugodu13 · Answer

Merci michel, c'est vraiment un gros boulot que tu as fait là, chapeau et merci !

Je suis dessus, j'essai de l'adapter à la réelle liste des ports

Enfin j'essaie de comprendre ton boulot ^^

Je vous fait un retour très rapidement ;)

hugodu13 · Answer

je comprends pas pourquoi je n'arrive pas à l'adapter à mes fichiers,

lorsque ca bosse sur mes fichiers j'obtiens une erreur d'execution:

"erreur definie par l'application ou par l'objet" si je fais deboguage j'arrive sur 
dico_ports.Remove (T_port(cptr))

J'ai cru comprendre que c'est qu'une des valeurs "port" du fichiers d'export n'est pas référencé sur l'onglet"réferences",

J'ai donc fait un filtrage sans doublon de la colone port de mon fichier d'export et collé ca dans l'onglet references, mais c'est pareil Oo.

J'ai donc entrepris de créer toutes les possibilités imaginables de ports dans l'onglet références. et malgré ca pareil !

Le truc bien chiant dans mes fichiers d'export c'est qu'il y a un espace après le nom du port. et le F est en majuscule Fa0/1.


Je vais aussi avoir un probleme, 
Fa0/1 pour moi c'est pareil que Fa1/0/1, c'est le premier port 100mbps  d'un equipement.

Mais dans les résultats j'aurai Fa1/0/1 libre meme si Fa0/1 est occupé; 

C'est pur ca que mes matricielles etaient extremement lourdes.

Je vérifiais toutes ces conditions avant de déterminer qu'un port étais libre ou non.

si, Fa0/1 n'apparati pas, que Fa1/0/1 n'apparait pas non plus, que Fa2/0/1 n'apparait pas non plus Alors Fa0/1 est libre.

Je continue de m'arracher les cheveux, je vous tiens au jus :)

gbinforme · Answer

bonjour

si, Fa0/1 n'apparati pas, que Fa1/0/1 n'apparait pas non plus, que Fa2/0/1 n'apparait pas non plus Alors Fa0/1 est libre. 

Avec Fa0/1 en A1 et tes ports en colonne B, cette formule n'est-elle pas suffisante ?

=SI(NB.SI($B$1:$B$6;A1)+NB.SI($B$1:$B$6;GAUCHE(A1;2)&"??"&DROITE(A1;3))=0;"libre";"présent")

Utilisateur anonyme · Answer

Bonjour hugodu13

Je vois que vous avez bien bossé ce w.e avec michel_m. J'ai travaillé avec les fichiers csv, je te propose le fichier suivant:
http://www.cijoint.fr/cjlink.php?file=cj201012/cij3beEXT5.xlsm 

il lit les fichiers et récupère les données.

La macro est dans le fichier.
J'ai regardé le programme de michel_m  qui est très bien conçu.  Je suis d'accord avec lui cn n'est qu'une variable et c'est vrai que j'ai raisonné avec 2007 et 2003 pose moins de problème(pour moi)  pour récupérer  les données avec ADO.
Cordialement

agi67

hugodu13 · Answer

Bonjour,

Voici un fichier d'export, 
http://www.cijoint.fr/cjlink.php?file=cj201012/cijC1qyrQY.zip 
j'ai laissé uniquement la colone des ports:

Il y a un espace avant et après le nom du ports:

Les ports FastEthernet 100mbps sont notés Fa0/1 sur un équipement seul

Sit deux équipements sont stackés les ports seront notés Fa1/0/1 (désigne le port Fa0/1 du premier équipement, Fa2/0/1 désigne le port Fa0/1 du deuxieme equipement)

Les ports Gigabit ethernet sont notés soit en Gi0/1 soit en G0/1 
ou  ils sont stackés soit Gi1/0/1 ou G1/0/1 (comme pour fastethernet)

Je viens de remarquer qu'une des lignes du fichier export est notée "FastEthernet0/12"....

pfff en gros le logiciel qui génère ces exports communique avec les switch 
et en fonction du switch, de sa version firmware, de son modèle, il va nommer le port différament ...

Je désespere un peu, ca alourdis encore plus la procédure :(

Sachant que si j'arrive générer les liste complete de toutes les possibilités de ports générable par l'exportation, le résultats en ports libres sera illisible...

Ou alors il faudrai que je prenne le temps de référencer les 180 équipements à savoir "équipement nommé bidule ne peut avoir que 24 ports FastEthernet et deux ports Giga" > donc ne pas afficher qu'il a le port Gi1/0/3, Gi2/0/3, G1/0/3,G2/0/3 etc... de libre.

@gbinforme: non, loin d'être suffisant malheureusement, il faut établir la correspondance entre l'équipement et le port.merci

Je vais me pendre et j'y reviens :)

hugodu13 · Answer

Merci pour tout, je vais m'y atteler, référencer le nombre de port et le type de port et la facon dont le texte est généré pour cet équipement.

Ca risque de me prendre un sacré moment,

Une fois terminé je pense que ca marchera nickel :)

Je vous donne des nouvelles très bientot.

Manipulations avancées excel

20 réponses

Newsletters