Aide sur Power Query

Le 07/07/2019 à 19:09

Bonjour,

Je rencontre un problème sur PowerQuery : lorsque j'importe des lignes avec conditions d'une autre feuille, j'aimerais qu'elles se sauvegardent or ce n'est pas le cas. En effet, lorsque la feuille source est modifiée, la feuille où j'importe est aussi modifiée. Par exemple, nous avons :

  • Feuille Entrée (Source)
  • Feuille B2, Psy, Méd (où j'importe à partir de la feuille Entrée)
  • Conditions : Seules les lignes qui contiennent la lettre V aux colonnes "Tests" et "Avis CRE" sont importées.

Du coup j'actualise une fois : ça marche.

Mais étant donné que j'utilise une macro qui supprime les lignes comportant la lettre V aux colonnes "Tests" et "Avis CRE" dans la feuille Entrée, à la deuxième actualisation après avoir utilisé la macro, la feuille B2, Psy, Méd devient vide. Et il est là mon soucis, je voudrais uniquement importer des lignes avec une/des conditions en parcourant certaines colonnes d'une feuille à l'autre, mais je n'y arrive pas avec PowerQuery. Je mettrais en pièce jointe mon fichier (il faudra juste modifier la source pour les requêtes).

Je n'arrive pas à comprendre, ça fait une semaine que je bidouille Excel et à chaque fois je rencontre un problème c'est juste insupportable et je commence à perdre espoir avec ce logiciel. Si vous avez une alternative à PowerQuery, telle qu'une macro, ça ne me pose aucun soucis.

Merci aux personnes qui voudront m'aider, car je désespère vraiment.

10test-copie.xlsx (377.78 Ko)
Le 07/07/2019 à 20:24

Bonjour

PowerQuery effectue des requêtes : il est donc normal que l'actualisation restitue le dernier état, comme le fait aussi un TCD

Si tu veux garder les lignes il faut copier le résultat dans un onglet, sans lien avec la requête après chaque utilisation...

Le 07/07/2019 à 20:39
78chris a écrit :

Bonjour

PowerQuery effectue des requêtes : il est donc normal que l'actualisation restitue le dernier état, comme le fait aussi un TCD

Si tu veux garder les lignes il faut copier le résultat dans un onglet, sans lien avec la requête après chaque utilisation...

Merci de ta réponse.

Le problème est que j'aimerais le faire pour plusieurs feuilles et d'automatiser la chose au maximum. Aurais-tu un modèle de code VBA qui puisse le faire sans PowerQuery ?

Le 07/07/2019 à 22:00

RE

Ton fichier joint pointe sur des fichiers externes donc on ne peux visualiser le déroulement.

Si le but est juste de récupérer les lignes contenant un simple filtre Entrée puis une copie des lignes filtrées suffit, inutile d'utiliser PowerQuery.

L'enregistreur de macros te donnera l’essentiel du code qu'on pourra t'aider à améliorer.

Le 08/07/2019 à 14:41

bonjour

salut Chris

ne vaut-il pas mieux faire une requête Power Query, puis éventuellement créer une courte macro (donc fiable) qui actualise la requête ?

certains le font pour les TCD

note : j'actualise TOUJOURS en manuel

comme Microsoft nous y incite (ils n'ont pas fourni l'automatisme, il y a une raison)

amitiés

Le 08/07/2019 à 16:10

Salut JMD

Là le demandeur veut en quelque sorte archiver des listes dont la source disparait donc qu'une requête ne peut retrouver.

Du coup un filtre suffit avant l'archivage...

Le 08/07/2019 à 19:46

re

d'où viennent les données d'origine ?

pourquoi sont-elles écrasées ? (c'est rarissime de nos jours où le prix des disques durs est si bas)

Le 08/07/2019 à 20:35

RE

Alors merci pour vos réponses. J'ai donc utiliser l'enregistreur de macro et en fouillant un peu sur Internet, j'ai réussi à assembler un truc fonctionnel mais pas opti et très redondant (c'est un bon début vu que je ne connais rien du langage VBA).

Si vous avez des questions par rapport au code, n'hésitez pas car je serais ravi de pouvoir l'optimiser même s'il n'est pas trop long à exécuter.

8final.txt (7.55 Ko)
Le 08/07/2019 à 21:18

Bonjour

Pour le 1er cas

 Dim rng As Range
 Dim lastline As Long
 'Pour sélectionner l'onglet
 ThisWorkbook.Worksheets("Entrée").Activate
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=16, Criteria1:="V"
 rng.AutoFilter Field:=18, Criteria1:="V"

 lastline = Sheets("B2, Méd, Psy").Cells(Rows.Count, 1).End(xlUp).Row + 1
 'Copier la sélection filtrée
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 rng.Copy Destination:=Sheets("B2, Méd, Psy").Cells(lastline, 1)
 'Supprimer les lignes
 rng.Rows.EntireRow.Delete
'Désactiver le filtre
 ActiveSheet.ShowAllData

Tu peux refaire de même pour les autres feuilles en recommençant à Set rng=ActiveSheet.Range("A1").CurrentRegion

Le 09/07/2019 à 22:04
78chris a écrit :

Bonjour

Pour le 1er cas

 Dim rng As Range
 Dim lastline As Long
 'Pour sélectionner l'onglet
 ThisWorkbook.Worksheets("Entrée").Activate
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=16, Criteria1:="V"
 rng.AutoFilter Field:=18, Criteria1:="V"

 lastline = Sheets("B2, Méd, Psy").Cells(Rows.Count, 1).End(xlUp).Row + 1
 'Copier la sélection filtrée
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 rng.Copy Destination:=Sheets("B2, Méd, Psy").Cells(lastline, 1)
 'Supprimer les lignes
 rng.Rows.EntireRow.Delete
'Désactiver le filtre
 ActiveSheet.ShowAllData

Tu peux refaire de même pour les autres feuilles en recommençant à Set rng=ActiveSheet.Range("A1").CurrentRegion

J'obtiens une erreur :/.

J'ai réussi à optimiser mon code un peu et j'ai résolu un problème majeur. Cependant je bloque sur quelque chose de très simple : il me faudrait un moyen de sélectionner la première ligne vide dans une feuille spécifique et d'y coller ce que j'ai copié avec Selection.Copy. Il me manque que cette partie et après je ne vous embête plus c'est promis

Je mettrais le code en pièce jointe, et merci encore.

6code.txt (1.43 Ko)
Le 09/07/2019 à 22:31

RE

Takk a écrit :

J'obtiens une erreur :/.

Où ? Laquelle ?

Ce genre de commentaire n'apporte aucune piste.

Si tu n'as pas une ligne vide sous la ligne de titre cela fonctionne...

Je 'ai testé sur ton fichier dont j'ai rempli au moins une colonne de la ligne sous le titre et cela fonctionne

Je t'ai donné le moyen d'optimiser en ne changeant pas d'onglet à tout bout de champ puisque copy... destination évite cela...

Et même si tu gardes ton code, la réponse est dans celui que j'ai posté

Edit : En plus si tes onglets cibles sont sous forme de tableau structuré et respectent la règle (pas de ligne vide), il suffit, pour ajouter en fin de tableau, d'utiliser

With Worksheets("Nom onglet").Listobjects(1).Range.
    .ListRows.Add
    y = .ListRows.Count
    rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
 End With
Le 09/07/2019 à 22:54
78chris a écrit :

RE

Takk a écrit :

J'obtiens une erreur :/.

Où ? Laquelle ?

Ce genre de commentaire n'apporte aucune piste.

Si tu n'as pas une ligne vide sous la ligne de titre cela fonctionne...

Je 'ai testé sur ton fichier dont j'ai rempli au moins une colonne de la ligne sous le titre et cela fonctionne

Je t'ai donné le moyen d'optimiser en ne changeant pas d'onglet à tout bout de champ puisque copy... destination évite cela...

Et même si tu gardes ton code, la réponse est dans celui que j'ai posté

J'ai rien dis, ça marche parfaitement autant pour moi. Et c'est plus rapide merci. Par contre, je rencontre un soucis avec ton code que j'ai réussi à résoudre avec la condition de la feuille vide après le filtre (voir mon code).

En effet si je relance ton code une deuxième fois, il me copie colle ce qui reste dans la feuille Entrée après le premier run du code, ce que je ne veux pas faire car le code risque d’être lancé plusieurs fois.

EDIT : J'ai réussi à régler le soucis (voir le code ci joint)

2code.txt (888.00 Octets)
Le 09/07/2019 à 23:09

Je rencontre encore un soucis et c'est celui la que je n'arrive pas à corriger. Lorsque j'applique le code pour deux colonnes différentes et que je copie colle vers une même feuille, il me supprime ce que j'ai collé au premier run.

Je te laisse voir dans le code en pièce jointe et le tester. Tu remarqueras qu'il copiera uniquement les lignes avec "NV" dans la colonne "Avis CRE", parce qu'il remplace les lignes avec "NV" dans la colonne Tests et car le code se finit par le traitement de la colonne Avis CRE.

Il me faudrait donc soit un moyen d'associer les deux avec un opérateur OU (j'aimerais éviter les filtres avancées parce que j'ai essayé et ça fait planté le code) ou bien de copier coller par la suite en sélectionnant la première colonne vide.

Merci encore pour ton aide c'est vraiment la dernière chose qui bloque à mon avis.

3code.txt (2.37 Ko)
Le 09/07/2019 à 23:29

RE

Regarde mon edit du post précédent où on utilise les tableaux

Sub Test()
 Dim rng As Range
 Dim lastline As Long
 'Pour sélectionner l'onglet
 ThisWorkbook.Worksheets("Entrée").Activate
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=16, Criteria1:="V"
 rng.AutoFilter Field:=18, Criteria1:="V"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 If rng.SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
 'Copier la sélection filtrée
 With Worksheets("B2, Méd, Psy").ListObjects(1)
    .ListRows.Add
    y = .ListRows.Count
    rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
 End With
   'Supprimer les lignes
   rng.Rows.EntireRow.Delete
   'Désactiver le filtre
   End If
   ActiveSheet.ShowAllData

  Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=16, Criteria1:="NV"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 If rng.SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
 'Copier la sélection filtrée
 With Worksheets("NV").ListObjects(1)
    .ListRows.Add
    y = .ListRows.Count
    rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
 End With
   'Supprimer les lignes
   rng.Rows.EntireRow.Delete
   End If
   ActiveSheet.ShowAllData

  Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=18, Criteria1:="NV"
 'Tester si la feuille est vide
   Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 If rng.SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
 'Copier la sélection filtrée
 With Worksheets("NV").ListObjects(1)
    .ListRows.Add
    y = .ListRows.Count
    rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
 End With
   'Supprimer les lignes
   rng.Rows.EntireRow.Delete
   End If
   ActiveSheet.ShowAllData

End Sub
Le 10/07/2019 à 10:29

Re,

Je n'avais pas vu ton edit sorry.

Ça fonctionne à merveille merci, mais le problème que j'ai résolu vient de réapparaitre : lorsque je relance une deuxième fois le programme ne fonctionne pas (il fait n'importe quoi pour préciser). C'est peut-être le fait que mes données sont devenues en forme de tableau et que la ligne de code correspondant au test pour savoir si la feuille est vide ne fonctionne plus ? Je vais essayer de résoudre le problème.

Merci encore Chris

Le 10/07/2019 à 11:02

RE

Tu devrais avoir une erreur car ton test ne fonctionne pas (tableau ou pas) si tu lances 2 fois de suite

J'ai ajouté une variable Atraiter qui teste autrement : adapter selon le contexte

Sinon explique ce qui se passe

Sub Test()
 Dim rng As Range
 Dim lastline As Long
 Dim Atraiter As Long
 'Pour sélectionner l'onglet
 ThisWorkbook.Worksheets("Entrée").Activate
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=16, Criteria1:="V"
 rng.AutoFilter Field:=18, Criteria1:="V"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(16).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée
 With Worksheets("B2, Méd, Psy").ListObjects(1)
    .ListRows.Add
    y = .ListRows.Count
    rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
 End With
   'Supprimer les lignes
   rng.Rows.EntireRow.Delete
   'Désactiver le filtre
   End If
   ActiveSheet.ShowAllData

  Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=16, Criteria1:="NV"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(16).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée
 With Worksheets("NV").ListObjects(1)
    .ListRows.Add
    y = .ListRows.Count
    rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
 End With
   'Supprimer les lignes
   rng.Rows.EntireRow.Delete
   End If
   ActiveSheet.ShowAllData

  Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=18, Criteria1:="NV"
 'Tester si la feuille est vide
   Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(18).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée
 With Worksheets("NV").ListObjects(1)
    .ListRows.Add
    y = .ListRows.Count
    rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
 End With
   'Supprimer les lignes
   rng.Rows.EntireRow.Delete
   End If
   ActiveSheet.ShowAllData

End Sub
Le 10/07/2019 à 11:16

Ça fonctionne à merveille !! Je vais terminer mon code avec ton modèle, en espérant que je ne rencontre pas un autre soucis. Si ce n'est pas le cas, je mettrais le sujet en résolu.

Mille merci encore Chris

Le 10/07/2019 à 11:40

Hé bien j'ai pas de chance (ou peut-être de mon incompétence) , je rencontre une erreur d’exécution "1004" : Erreur définie par l'application ou par l'objet.

C'est au niveau de cette ligne là : Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count) pour la feuille B2, Méd, Psy. Merci encore pour ton aide.

Sub Test()
 Dim rng As Range
 Dim lastline As Long
 Dim Atraiter As Long
 'Pour sélectionner l'onglet source
 ThisWorkbook.Worksheets("Entrée").Activate
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=16, Criteria1:="V"
 rng.AutoFilter Field:=18, Criteria1:="V"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(16).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée vers la feuille cible
   With Worksheets("B2, Méd, Psy").ListObjects(1)
      .ListRows.Add
      y = .ListRows.Count
      rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
   End With
  'Supprimer les lignes importées
   rng.Rows.EntireRow.Delete
  End If
  'Désactiver le filtre
  ActiveSheet.ShowAllData

 'Pour sélectionner l'onglet source
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=16, Criteria1:="NV"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(16).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée vers la feuille cible
   With Worksheets("NV").ListObjects(1)
      .ListRows.Add
      y = .ListRows.Count
      rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
   End With
  'Supprimer les lignes importées
   rng.Rows.EntireRow.Delete
  End If
  'Désactiver le filtre
  ActiveSheet.ShowAllData

  'Pour sélectionner l'onglet source
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=18, Criteria1:="NV"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(16).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée vers la feuille cible
   With Worksheets("NV").ListObjects(1)
      .ListRows.Add
      y = .ListRows.Count
      rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
   End With
  'Supprimer les lignes importées
   rng.Rows.EntireRow.Delete
  End If
  'Désactiver le filtre
  ActiveSheet.ShowAllData

 'Pour sélectionner l'onglet source
 ThisWorkbook.Worksheets("B2, Méd, Psy").Activate
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=24, Criteria1:="V"
 rng.AutoFilter Field:=25, Criteria1:="V"
 rng.AutoFilter Field:=26, Criteria1:="V"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count) [b][u]------> ICI [/u][/b]
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(16).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée vers la feuille cible
   With Worksheets("V").ListObjects(1)
      .ListRows.Add
      y = .ListRows.Count
      rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
   End With
  'Supprimer les lignes importées
   rng.Rows.EntireRow.Delete
  End If
  'Désactiver le filtre
  ActiveSheet.ShowAllData

   'Pour sélectionner l'onglet source
 ThisWorkbook.Worksheets("B2, Méd, Psy").Activate
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=24, Criteria1:="NV"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(16).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée vers la feuille cible
   With Worksheets("NV").ListObjects(1)
      .ListRows.Add
      y = .ListRows.Count
      rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
   End With
  'Supprimer les lignes importées
   rng.Rows.EntireRow.Delete
  End If
  'Désactiver le filtre
  ActiveSheet.ShowAllData

 'Pour sélectionner l'onglet source
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=25, Criteria1:="NV"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(16).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée vers la feuille cible
   With Worksheets("NV").ListObjects(1)
      .ListRows.Add
      y = .ListRows.Count
      rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
   End With
  'Supprimer les lignes importées
   rng.Rows.EntireRow.Delete
  End If
  'Désactiver le filtre
  ActiveSheet.ShowAllData

 'Pour sélectionner l'onglet source
 Set rng = ActiveSheet.Range("A1").CurrentRegion
 rng.Select
 'Filtrer la sélection
 rng.AutoFilter Field:=26, Criteria1:="NV"
 'Tester si la feuille est vide
 Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
 Atraiter = Application.WorksheetFunction.Subtotal(103, Range(rng.Columns(16).Address))
 If Atraiter > 0 Then
 'Copier la sélection filtrée vers la feuille cible
   With Worksheets("NV").ListObjects(1)
      .ListRows.Add
      y = .ListRows.Count
      rng.Copy Destination:=.ListColumns(1).DataBodyRange.Cells(y, 1)
   End With
  'Supprimer les lignes importées
   rng.Rows.EntireRow.Delete
  End If
  'Désactiver le filtre
  ActiveSheet.ShowAllData

End Sub
Le 10/07/2019 à 11:41

Hé bien j'ai rien dis, il marche parfaitement. Merci encore Chris ! Sans toi je pense que je n'aurais jamais réussi ! J

Le 10/07/2019 à 12:26

re

Utilise le mode débug

met un point d'arrêt sur la ligne "rng.Select" avant le PB et vérifie l'adresse de rng (mettre un espion rng.address)

Si rng est totalement vide de données, on ne peut resizer car sa hauteur est 1 et donc 0 e marche pas

A noter que pour tester ici ATraiter doit se référer à l'une des colonne de filtre et non 16 (j'avais préciser la nécessité d'adapter au contexte)

Mais de façon générale si tu filtres des Tableaux structurés il faudrait un code adapté aux tableaux et plus utiliser ActiveSheet.Range("A1").CurrentRegion

Le plus simple serait donc de mettre le 1er tableau aussi en tableau structuré pour tout gérer de même...

Rechercher des sujets similaires à "aide power query"