Compiler en une seule feuille les 44 feuilles d'un tableau Excel

Bonjour

Je suis un nouveau venu

J'ai pratiquement aucune connaissance des macro excel

Je fais face à un problème pratique

J'ai un fichier excel qui contient 44 feuillesque je veux compiler en un seul tableau "USER_VERSION1" - ce sont des personnes qui ont suivi une formation.

Je cherche à éviter à le faire à la main. Et comme j'ai d'autres tableaux à traiter, avec des centaines de noms, je préférerais automatiser ce traitement.

Chaque feuille comprends un titre en ligne 1

L'intitulé de 8-10 colonnes en ligne 2: No Given Names Surname Gender Email Organization Country RIC REC, nationality, etc.

Les valeurs pour chacune de ces colonnes (enregistrements) démarrent à partir de la ligne 3

La compilation doit rajouter une colonne (colonne 1). Pour chaque feuille cette colonne se remplit avec le titre qui est en ligne 1 puis avec les valeurs des autres colonnes (colonnes 2 à 9-11)

Et ainsi de suite, feuille après feuille.

Merci de votre aide et de vos explications, pour que je puisse ensuite adapter cette macro.

Brice

bonjour

Sur des fiches papier tu faisais une fiche par cession de formation. Cette manière ne permet pas de travailler vite et bien avec Excel.

ne garde qu'un unique onglet (supprime les 44 onglets qui ne servent à rien)

insère une colonne A dans laquelle tu mets

User PUMP2015 System Administration in English ,25 July to 29July, Nairobi Kenya , Venue IMTR

à chaque ligne de ce stage

idem pour les autres stages

tu as une table de données bien organisée,

tu peux faire des tris, filtres, TCD pour extraire, visualiser, synthétiser, faire des graphiques....

la saisie se fait directement dans cet onglet, durant des années

note : il est souhaitable de mettre la date dans une colonne DATE séparée (au format jj/mm/aa que Excel peut comprendre.

bon travail

Bonjour

tu dis 8-10 ... or, je compte 9 colonnes

le nombre de colonnes est-il variable ?

je souscris bien sûr totalement à l'avis de jmd ... sauf si ces feuilles sont remplies séparément par un tiers et que tu aies ensuite à compiler, est-ce la cas ici ?

Tout d'abord merci beaucoup Steelson et JMD de votre réponse, notamment pour votre promptitude

Pour la petite histoire, j'ai hérité de ce tableau et de ceux à venir. Il s'agit au total de 1800 personnes formées dans 5 -6 centres régionaux de formation, en provenance de 45 pays africains.

Je vais mettre en place d'autres formations sur le même principe mais je dois analyser d'abord les informations apportées par ces tableaux: pays d'origine, nombre de jours de formation par étudiant, nombre de jour de formation délivré par institution, etc..

Steelson,

le nombre de colonne ne varie pas dans le cas du tableau que j'ai transmis transmis. Toutefois, dans les autres tableaux excel, certaines colonnes dont dupliquées.

JMD,

C'est précisément ce que je cherche à faire en compilant les données en une seule feuille, optimiser la saisie et les analyses à venir.

Encore merci - et par avance - pour votre aide en la matière

Brice

Bonsoir BriceMontFraix, Steelson, jmd, le forum,

Un essai..... ....... Ctrl + e pour lancer la macro.....

Cordialement,

Bonsoir à tous

Une approche VBA incluant PowerQuery

Le titre est en dernière colonne.

On peut rompre le lien avec la requête après la fin du traitement.

En début de traitement, le code homogénéise la colonne No des divers tableaux et supprime les espaces avant/après les titres pour également homogénéiser et permettre la synthèse par PowerQuery

Le titre est ajouté dans une nouvelle colonne avant de générer la requête de synthèse PowerQuery

re à tous

"compiler" 44 ou 100 onglets ?

ça signifie :

1- soit qu'il y a eu saisie d'une formation dans un onglet

2- soit qu'il y a eu saisie dans un fichier individuel pour chaque formation, avec report dans un onglet de ton fichier à toi

cas 1 : il FAUT saisir directement dans l'onglet unique

cas 2 : ne pas reporter des fichiers multiples dans des onglet ! simplement utiliser Power Query directement sur cet ensemble de 44 fichiers

en aucun cas il n'y a besoin de VBA..

en aucun cas il n'y a besoin de créer 44 onglets.

alors la question est : qui, quand, comment, dans quels fichiers sont saisies les données ?

Jmd,

J'ai en effet hérité ce tableau des fournisseurs des formations, qui se sont déployées dans le cadre d'un programme maintenant terminé.

Dans le fichier que je vous ai transmis, il s'agit de 44 formations pour administrateurs de stations météorologiques qui se se sont étalées de 2015 à 2018 dans divers centres en Afrique.

Je dispose d'autres tableaux pour d'autres usages de ces stations, mais ils sont bien plus simples, de 5 à 10 onglets. J'avais donc commencé à procéder par "copier coller". Ce qui au final est très long...

Je pense que ces fournisseurs ont procédé ainsi , une formation = un onglet, afin de créer des tableaux pour un rapport.

Pour ma part, j'aurai plutôt saisi dans un onglet unique.

Xorsankukai

78chris

Merci mille fois pour cette "compil" qui va accélérer mon travail d'analyse. Le tableau obtenu est ce que je voulais. Je vais maintenant

-REPARTIR SUR PLUSIEURS COLONNES les informations apportées par le titre : intitulé de la formation, date début date fin, langue de la formation, centre de formation.

Par exemple: User PUMP2015 System Administration in English , 25July to 29July, Vaccoas Mauritius , Venue MMS veut dire comme vous l'avez sans doute compris:

Formation des usagers à l'administration de stations PUMA2015. Cette formation a eu lieu du 25 au 29 juillet (2016) au MMS (Mauritius Meteorological Centre) de Vaccoas.

La combinaison des divers facteurs du tableau va me permettre par exemple de définir combien de personnes dans combien de pays a été formé combien de jours ou semaines, quelles ont été les formations délivrées par centre de formation, combien de jours de formation au total ont été délivré par centre et ou par pays.

Elle va contribuer à développer la base de données que nous constituons sur les bénéficiaires

Les personnes formées seront sans doute recontactées pour de nouvelles formations, en fonction de leurs disponibilités.

-ANALYSER VOTRE MACRO ET LE CODE pour l'adapter aux autres fichiers de ce type que j'ai reçu.

Notez que je suis usager d'EXCEL depuis 1987 mais je découvre avec vos réponses que j'en suis resté à de bien pauvres niveaux d'usages, tableur, alors qu'il offre d'autres fonctionnalités ou outils dont notamment POWER QUERY.

Je vais tenter passer du temps à explorer ces fonctionnalités et passer aussi du temps sur le forum pour en découvrir d'autres facettes, qui pourraient m'aider dans mes tâches (gestion de projet).

Dans tous les cas, merci mille fois d'avoir répondu à mon appel.

Brice

Bonjour

Merci du retour.

Pense aux Tableaux Croisés Dynamiques pour analyser : c'est simple et efficace.

Bonjour,

@ Chris,

Je me suis amusé à cet exercice, de combiner VBA et Power Query (nécessité de posséder Excel 2016 cependant ).

Il y a des choses intéressantes.

Ton exemple montre parfaitement ce que l'on peut imaginer pour répondre à certaines questions.

2 questions :

Comment ferais-tu pour supprimer simplement :

1 - la requête Synthèse

2 - la connexion Connexion

Et puis, une autre, une dernière question :

Comment renommer Connexion ou la nommer à la création ?

A te relire.

Bonne journée

Nota : VBA est mort, enterré, mais Microsoft crée encore des modèles d'objet (tel que WorkbookQuery) :...

Bonjour à tous

@Jean-Eric

Oui, j'ai utilisé 2016 sur ce coup...

J'ai planché sur tes questions et faut pas trop compter sur l'aide en ligne MS !

Pour la dernière question aucun idée de comment nommer la connexion mais pour renommer je propose

Sub NomConnect()
    For Each Cx In ActiveWorkbook.Connections
        If InStr(Cx.OLEDBConnection.CommandText, "Synthese") Then NCx = Cx.Name: Exit For
    Next
    ActiveWorkbook.Connections(NCx).Name = "MaSynthese"
End Sub

Bon faut pas avoir 36 Synthèses

Pour le ménage : a priori si on commence par supprimer la requête, le tableau garde une connexion qui ne marche plus (et dire qu'il perd le lien dès que je touche une virgule d'une requête, du moins sur 2010 )

Donc faut casser le lien, puis supprimer la requête, puis supprimer la connexion

Je propose

Sub RAZ()
    For Each Cx In ActiveWorkbook.Connections
        If InStr(Cx.OLEDBConnection.CommandText, "Synthese") Then NCx = Cx.Name: Exit For
    Next
    Worksheets("Synthese").ListObjects("Synthese").Unlink
    ActiveWorkbook.Queries("Synthese").Delete
    ActiveWorkbook.Connections(NCx).Delete
End Sub

ou si on a nommé la connexion

Sub RAZ()
    Worksheets("Synthese").ListObjects("Synthese").Unlink
    ActiveWorkbook.Queries("Synthese").Delete
    ActiveWorkbook.Connections("MaSynthese").Delete
End Sub

Mais je ne prétends pas que ce soit l'optimum...

Ce fil correspond au 1er VBA que j'ai fait sur PQ...

Re,

Merci pour les réponses.

Pour nommer à la création (renommer) une connexion, j'opterai pour :

Public Sub RenameWorkbookConnection()
Dim qt As QueryTable, prefix As String
    Set qt = ActiveSheet.ListObjects(1).QueryTable
    prefix = "Power-Query - "
    With qt.WorkbookConnection
        .Name = prefix & "A définir"
        .Description = "A définir"
    End With
    'Suppression pour info.
    'qt.WorkbookConnection.Delete
End Sub

J'ai adapté ton classeur en ce sens [procédure PQ()].

Je continue mes investigations !...

Cdlt.

Et pourtant ...

Jean-Eric a écrit :

Nota : VBA est mort, enterré, mais Microsoft crée encore des modèles d'objet (tel que WorkbookQuery) :...

Bonjour,

@ Steelson,

Le nota était un clin d'œil à notre ami jmd !...

Dans Excel 2016, Microsoft a inclus l'enregistrement de macros et le support du modèle objet (WorkbookQuery, etc...).

On peut "maintenant" écrire du code dans VBA (C#, PowerShell et autres langages), et automatiser la création et l'actualisation des requêtes.

On ne pas se priver si l'occasion se présente d'utiliser ces "nouvelles" fonctionnalités.

Bon, maintenant les occasions vont se faire rares.

Cdlt.