Power Query Manipulation des cellules nommées

[phil555]

Bonjour à tous,

J'ai environ 200 classeurs réparties dans différents dossiers (Client), tous ces classeurs contiennent environ 550 cellules nommées réparties dans les feuilles de ces classeurs. (Voir fichier exemple joint Calcul_aides)

Je cherche un moyen pour créer un tableau de bord à partir de tous ces classeurs Excel (Voir fichier joint "Tableau de bord")

Power query semble être la solution à mon problème mais :

- Je décrouvre Power query, j'apprends vite mais je débute...

- Mes cellules nommées n'étant pas ordonnées dans un tableau, Power query ne semble pas savoir les exploiter.

Je pourrais créer une macro dans chaque classeurs afin d'extraire tous les noms et valeurs des cellules nommées mais cela m'oblige de mettre à jour, avec cette nouvelle macro, tous mes classeurs un à un.

Je ne cherche pas une solution toute faite mais vous me rendriez un très grand service si l'un ou l'une d'entre vous pouvait me donner quelques pistes pour avancer

Merci !!!

 

[merinos-BernardEtang]

Bonjour,

C'est malheureusement ce qui arrive quand on emploie Excel comme un systeme de gestion.

Tu peux creer une macro(dans ton fichier personel ) qui crée une page cachée dans le fichier ouvert avec tes champs nommés et leur noms.

J'en ai une qui fait cela. tu obtien 3 colonnes: le nom de la cellule (avec un lien vers celle ci) , sa valeur (via un lien) et la cellule liée.

Sub Create_Named_Ranges_List()

Dim rnOmrade As Range
Dim nNamn As Name
Dim lnNamn As Long, lnAntal As Long
Dim Y As Range

'************************************
' first verif is there are active names
'************************************
lnAntal = 0
For Each nNamn In ActiveWorkbook.Names
     lnAntal = lnAntal + 1
Next nNamn
If lnAntal = 0 Then
   MsgBox "Could not find any names", vbInformation, "Create Namelist"
   Exit Sub
End If


Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Sheets("Namelist").Delete
ActiveWorkbook.Sheets.Add

On Error GoTo 0

'********************************
'       set columns names       *
'********************************
With ActiveSheet
       .Name = "Namelist"
       .Cells(1, 1).Value = "Name:"
       .Cells(1, 2).Value = "Value:"
       .Cells(1, 3).Value = "Refer to:"
       .Cells(1, 4).Value = "cell1:"
       .Cells(1, 5).Value = "cell2:"
       .Cells(1, 6).Value = "Start:"
       .Cells(1, 7).Value = "End:"
       With .Range("A1:G1")
              .Font.Bold = True
              .Font.ColorIndex = 10
              .Font.Size = 10
       End With
End With


lnNamn = 2
For Each nNamn In ActiveWorkbook.Names
       If nNamn.Name Like "*!Print_*" Then GoTo Fortsatt
       ActiveSheet.Cells(lnNamn, 1).Value = nNamn.Name
       ActiveSheet.Hyperlinks.Add _
                                Anchor:=ActiveSheet.Cells(lnNamn, 1), _
                                Address:="", _
                                SubAddress:=nNamn.Name
       ActiveSheet.Cells(lnNamn, 3).Value = "'" & nNamn.RefersTo
       ActiveSheet.Cells(lnNamn, 3).InsertIndent 1
       On Error Resume Next
       Set rnOmrade = nNamn.RefersToRange
       If rnOmrade.Cells.Count > 1 Then
              ActiveSheet.Cells(lnNamn, 2).Value = "Nothing"
       Else
              With ActiveSheet.Cells(lnNamn, 2)
                   .Value = nNamn.Value
                   .NumberFormat = rnOmrade.NumberFormat
              End With
       End If
       ActiveSheet.Cells(lnNamn, 4) = nNamn.RefersToRange.Address(False, False)
       ActiveSheet.Cells(lnNamn, 6).FormulaR1C1 = "=IFERROR(MID(RC[-2],2,15)/1,IFERROR(MID(rc[-2],3,15)/1,""column""))"
       ActiveSheet.Cells(lnNamn, 7).FormulaR1C1 = "=IFERROR(MID(RC[-2],2,15)/1,IFERROR(MID(rc[-2],3,15)/1,""""))"
'       ActiveSheet.Cells(lnNamn, 8).FormulaR1C1 = "=IFERROR(MID(RC[-2],2,15)/1,IFERROR(MID(rc[-2],3,15)/1,""column""))"
    
       On Error GoTo 0

       lnNamn = lnNamn + 1
Fortsatt:
Next nNamn

Set Y = ActiveSheet.Range("D2:D" & ActiveSheet.Range("D65536").End(xlUp).Row)
Y.TextToColumns Destination:=ActiveSheet.Range("D2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))

Columns("A:C").EntireColumn.AutoFit
Columns("B").HorizontalAlignment = xlCenter
With Application
       .Calculation = xlCalculationAutomatic
       .ScreenUpdating = True
End With
'instruction eventuelle pour deleter un named range:
'nNamn.delete

End Sub  'Create_Named_Ranges_List

Puis PowerQuery peut lire tous ces fichiers transformés.



PS: cette macro a été copié mais je ne connais plus la source. probablement trouvée via une question sur ce forum.
Je l'ai probablement modifiée mais c'est avant 2017...

 

[chris]

Bonjour

Si les cellules nommées sont présentes et de même nom dans tous les classeurs ce doit être faisable mais il faudrait à minima 2 exemples remplis car avec du vide et pas mal de noms renvoyant une erreur, on ne peut rien voir

En plus tu as des plages de n cellules pour certaines colonnes du T de B : AUTO par exemple
On va gérer gérer comment ?

 

[sylvanu]

Bonjour Phill,
Si dans tous vos fichiers les données sont toujours au même endroit, et si tous vos fichiers sont dans le même dossier, il est possible de les ouvrir les uns après les autres et de picorer les données.
La macro est alors unique dans le fichier de synthèse. Cependant avec 200 classeurs cela peut être long.
Si c'est faisable avec PowerQuerry, ce serait top. ( mais je n'y connais rien )
Si à défaut de réponse sur PQ et que cette solution basique vous sied, revenez, on en reparlera.

 

[Robert]

Bonjour Phil, bonjour le forum,

Ce n'est pas exactement ce que tu demandes mais... Le code ci-dessous permet de récupérer la liste des noms du fichier que tu ouvres via la boîte de dialogue. Une fois les noms récupérés, le fichier source est automatiquement fermé. Code commenté :

Sub Macro1()
Dim CD As Workbook 'déclare la variable CD (Classseur Destination)
Dim OD As Worksheet 'déclare la variable OD (Onglet Destination)
Dim BO As FileDialog 'déclare la variable BO (Boîte Ouvrir)
Dim CS As Workbook 'déclare la variable CS (Classseur Source)
Dim OT As Worksheet 'déclare la variable OL (Onglet Temporaire)
Dim DL As Integer 'déclare la variable Dl (Dernière Ligne)
Dim I As Integer 'déclare la variable I (Incrément)
Dim NC As Byte 'déclare la variable NC (Nombre de Crochets)
Dim LN As Variant 'déclare la variable LN (Liste des Noms)

Set CD = ThisWorkbook 'définit le classeur destination CD
Set OD = CD.Worksheets("Feuil2") 'définit l'onglet destination CD
OD.Range("A1").CurrentRegion.ClearContents 'efface les anciennes valeurs
On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligne suivante)
OD.Range("Tableau1").Delete 'supprime le tableau structuré "Tableau1" (génère une erreur s'il n'existe pas)
On Error GoTo 0 'annule la gestion des derreurs
Set BO = Application.FileDialog(msoFileDialogFilePicker) 'définit la boîte d'ouverture BO
With BO 'prend en compte BO
    .AllowMultiSelect = False 'n'autorise la sélection que d'un seul fichier
    .Show 'affiche
    'si un élément sélectionné, ouvre le classeur sélectionné
    If BO.SelectedItems.Count > 0 Then Application.Workbooks.Open (BO.SelectedItems(1))
    Set CS = ActiveWorkbook 'définit la classeur source
    CS.Sheets.Add Before:=CS.Sheets(1) 'ajoute un onglet vierge ne premiere position
    Set OT = ActiveSheet 'définitl'onglet temporaire PT
    OT.Cells(1, 1).ListNames 'copie dans A1 de l'onglet OT la liste des noms du classeur source
End With 'fin de la prise en compte de BO
OD.Activate 'active l'onglet OD
OD.Range("A1").Value = BO.SelectedItems(1) 'écrit le nom du casseur source dans A1
OD.Range("A2").Value = "Nom" 'écrit "Nom" dans A2
OD.Range("B2").Value = "Adresse" 'écrit "Adresse" dans B2
OT.Cells(1, 1).CurrentRegion.Copy OD.Range("A3") 'copie la liste des nom de l'ongelt temporaire OT dans A3 de l'onglet OD
DL = OD.Cells(Application.Rows.Count, "A").End(xlUp).Row 'définit la dernière ligne éditée DL de la colonne A de l'onglet OD
OD.Columns("A:B").AutoFit 'ajustement des colonnes A et B
OD.ListObjects.Add(xlSrcRange, Range("A2:B" & DL), , xlYes).Name = "Tableau1" 'Transformation de la plage en tableau structuré nommé "Tableau1"
CS.Close False 'ferme le fichier source sans enregistrer (l'onglet temporaire disparaît...)
MsgBox "Liste des noms récupérée !" 'message
End Sub

 

[phil555]

Merci pour toutes vos réponses !
@merinos et @Robert je garde vos VBA sous le coude, merci !!

Bonjour

Si les cellules nommées sont présentes et de même nom dans tous les classeurs ce doit être faisable mais il faudrait à minima 2 exemples remplis car avec du vide et pas mal de noms renvoyant une erreur, on ne peut rien voir

En plus tu as des plages de n cellules pour certaines colonnes du T de B : AUTO par exemple
On va gérer gérer comment ?

Bonjour @chris ,
Oui toutes les cellules ont le même nom dans tous mes classeurs, j'ai un fichier modèle que je duplique à chaque nouveau client

J'ai pas besoin de récupérer les valeurs des plages de cellules, elles me servent dans mes listes de choix ou dans mes formules, de même qu'il me semble qu'il y une ou deux liaisons externes qui ne servent plus d'ailleurs...

Je vais préparer quelques exemples anonymisés (Et déverrouillées car j'ai oublié d'enlever le verrouillage dans les fichiers joints...)

Bonjour Phill,
Si dans tous vos fichiers les données sont toujours au même endroit, et si tous vos fichiers sont dans le même dossier, il est possible de les ouvrir les uns après les autres et de picorer les données.
La macro est alors unique dans le fichier de synthèse. Cependant avec 200 classeurs cela peut être long.
Si c'est faisable avec PowerQuerry, ce serait top. ( mais je n'y connais rien )
Si à défaut de réponse sur PQ et que cette solution basique vous sied, revenez, on en reparlera.

Bonjour @sylvanu
Oui elles sont toujours au même endroit, mais l'intérêt de faire ça avec Power Query c'est de rendre mon tableau de bord dynamique, il se mettra à jour au fur et à mesure de la création de nouveaux classeurs

 

[chris]

RE

J'ai pas besoin de récupérer les valeurs des plages de cellules

Dans ce cas met à jour les en-têtes citées dans le T de B : j'utilise cette ligne pour identifier les noms à récupérer

 

[phil555]

RE

Dans ce cas met à jour les en-têtes citées dans le T de B : j'utilise cette ligne pour identifier les noms à récupérer

Voilà j'ai créé 2 fiches clients fictives, supprimer les macros de verrouillage et d'interdiction de copier coller, mis à jour le TdB avec en entêtes les cellules qui m'intéressent

Dans les fiches clients, j'ai aussi ajouté manuellement la feuille Extract_Cell, juste pour voir s'il n'y avait pas d'erreur dans les valeurs des cellules que je veux exploiter

Merci beaucoup !!

 

[goube]

Bonjour à tous,
Phil555, tu vois qu'on y vient.

Power Query + Cellules nomées

J'ai environ 200 feuilles de calcul classées dans différents dossiers (Client), toutes ces feuilles comporte plus de 550 cellules nommées réparties dans dive...

forum.excel-pratique.com

Cordialement.

 

[chris]

Bonjour

Modifier le chemin d'accès puis Données, Actualiser Tout : résultat sur feuil2

 

[phil555]

Je n'ai eu le temps de le tester qu'aujourd'hui et c'est PARFAIT !!!
C'est exactement ce dont j'avais besoin, un grand merci

Le temps d'exécution est un peu long, une dizaine de minutes pour 20 classeurs, j'ose pas imaginer avec les 200 ....

Je vais modifier le filtre des fichiers sources car j'ai beaucoup d'autre classeurs dans mes dossiers client qui n'ont pas besoin d'être exploiter

Réduire un peu le nombre de cellules nommées à récupérer

Mettre tout ça en forme

@chris je reviendrai peut être vers toi si je suis bloqué mais en attendant un grand merci !!

 

[phil555]

Salut @chris

J'ai réussi à modifier les filtres des fichiers sources pour écarter les fichiers dont je n'avais pas besoin.

Par contre j'ai beau chercher je ne trouve pas comment réduire la liste des cellules nommées à récupérer

J'ai tenter de masquer, filtrer, ou supprimer des noms de cellules dans le tableur de la Feuil1 mais ca ne fonctionne pas. J'ai bien tenter de modifier les requête dans PQ mais je n'ai pas assez de connaissances pour savoir ce que je dois modifier ....

Peux tu me dire ce que je dois modifier pour réduire cette liste ?

D'avance merci !!

 

[chris]

Bonjour
En feuil1 du tableau de bord c'est juste l'ordre des colonnes.

Mais si tu modifies cette liste, je peux utiliser la liste raccourcie pour filtrer les sources.

Éventuellement garder la liste en l'état mais ajouter une colonne avec des 0 ou des 1 selon que l'on garde ou pas

Poste un nouvelle liste afin que je modifie la requête

 

[phil555]

Bonjour @chris
Voici la nouvelle liste
Serait il possible que tu intègres la possibilité de choisir les cellules nommées à récupérer par le biais des 0 / 1 dans la liste en feuil1
Je pourrai ainsi modifier (sans te mettre à contribution) la liste des cellules

Merci encore !!

 

[chris]

Bonjour