Import Querytable vba - Champ variable

Version imprimable

Bonjour,

Je cherche à importer des données de manière automatique d'une base de donnée, en filtrant sur une "Clé".
Cette clé est variable, selon la valeur d'une cellule.

J'ai utilisé l'enregistreur de macro pour réaliser la tâche automatiquement, mais je rencontre des bugs au moment d'insérer la variable "Clé", présente en C3.

Serait-il possible de m'aider à intégrer cette donnée ?

Voici le code que j'ai pu enregistrer.

Merci par avance pour votre aide !

Nissaij

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Sub Macro1()
 
 
    Application.CutCopyMode = False
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=P:\POIDS\Miroir_Compilation_Ligne_sachets_T1.xlsx;DefaultDir=P:\POIDS;DriverId=1046;MaxBufferSize=2048;Page" _
        ), Array("Timeout=5;")), Destination:=Range("$A$10")).QueryTable
        .CommandText = Array( _
        "SELECT TEVO_1.Clé, TEVO_1.Date, TEVO_1.Equipe, TEVO_1.Code_article, TEVO_1.Lot, TEVO_1.Poid_cible, TEVO_1.Nb_UVC_Prod, TEVO_1.Tare, TEVO_1.Visa, TEVO_1.Heure, TEVO_1.Poids_Net_1, TEVO_1.Poids_Net_2" & Chr(13) & "" & Chr(10) & "F" _
        , _
        "ROM `P:\POIDS\Miroir_Compilation_Ligne_sachets_T1.xlsx`.TEVO_1 TEVO_1" & Chr(13) & "" & Chr(10) & "WHERE (TEVO_1.Clé='919103_83452')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_Lancer_la_requête_à_partir_de_Excel_Files"
        .Refresh BackgroundQuery:=False
    End With
End Sub

24/02/2023, 10h03
Raccourcix

Bonjour

Avec Excel 365 et depuis au moins Excel 2016, il faut utiliser Power Query (menu Données, obtenir des données) pour ce genre de tâche

Stéphane
27/02/2023, 11h31
Nissaij

Bonjour Stéphane,

Je viens de modifier dans le sens de votre remarque.

Tout semble OK en sélection manuelle.

J'aimerai intégrer une variable ici :

Code:

""Lignes filtrées"" = Table.SelectRows(#""Type modifié"", each ([Clé] = ""918402_85554""))"

La valeur de clé doit être récupérée dans la case E4 (donnée concaténée à partir d'un code article et d'un lot).

Est-il possible d'intégrer cette variable en automatique, pour ne pas avoir à faire la sélection manuellement ?

En vous remerciant,

Nissaij
27/02/2023, 12h18
78chris

Bonjour à tous

Il semble que tu codes la requête en VBA ce qui est inutile : une requête s'actualise par VBA éventuellement mai ne se crée pas par VBA

On peut utiliser une cellule nommée, Cle par exemple, avec cette syntaxe

Code:

[Clé] = Excel.CurrentWorkbook(){[Name="Cle"]}[Content](Column1]{0}

On peut également utiliser un paramètre POwerQuery et en modifier la valeur par VBA
27/02/2023, 13h54
Raccourcix

Bonjour

Une petite erreur de syntaxe dans la réponse de 78chris (que je salue au passage)

Code:

Excel.CurrentWorkbook(){[Name="Cle"]}[Content][Column1]{0}

ou encore toujours avec une cellule nommée "Clé"

Code:

Table.FirstValue(Excel.CurrentWorkbook(){[Name="Clé"]}[Content])

L'avantage de cette 2e syntaxe est qu'elle fonctionne sans déterminer le titre de la colonne dans le cas d'un tableau.

Stéphane
27/02/2023, 14h47
Nissaij

Bonjour,

J'ai bien compris vos remarques.

J'ai bien créé la requête à partir de PowerQuery, et non codé en VBA.
Les modifications se font donc directement dans Power Query si je comprend bien.

La cellule "Cle" est bien nommée, mais je ne vois pas où intégrer ces lignes de code.
Dans l'éditeur avancé ?
A quel niveau ?

Cordialement,

Nissaij
27/02/2023, 15h12
Nissaij

Edit de mon message précédent.

Je viens de comprendre ... :roll: :oops:

Je fais des essais et reviens vers vous.

Merci encore.

Bonjour,
Un petit exemple
Dans le code ci-dessous, j'ai filtré la colonne nommée "Logement" sur "Loft" et j'aimerais que cela soit filtré par rapport à ma sélection dans une liste déroulante placée dans une cellule nommée pConsult

Code:

1
2
3
4
5
6
7
8
let
    Source = Excel.CurrentWorkbook(){[Name="t_Data"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"#", type text}, {"Nom", type text}, {"Prénom", type text}, {"Sexe", type text}, {"SALAIRE", type number}, {"Date Naiss", type datetime}, {"Lieu Naiss", type text}, {"AGE", Int64.Type}, {"Pays Habitat", type text}, {"Date Entrée", type datetime}, {"CIVIL", type text}, {"ENFANTS", Int64.Type}, {"LOGEMENT", type text}, {"VOITURE", type text}}),
    FiltreLogement = Table.SelectRows(#"Type modifié", each ([LOGEMENT] = "Loft")),
    #"Autres colonnes supprimées" = Table.SelectColumns(FiltreLogement,{"#", "Nom", "Prénom", "ENFANTS", "VOITURE"})
in
    #"Autres colonnes supprimées"

Je dois donc remplacer dans l'étape FiltreLogement le texte "Loft" (en rouge dans le code) par Excel.CurrentWorkbook(){[Name="pConsult"]}[Content][Column1]{0}

Soit

Code:

1
2
3
4
5
6
7
let
    Source = Excel.CurrentWorkbook(){[Name="t_Data"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"#", type text}, {"Nom", type text}, {"Prénom", type text}, {"Sexe", type text}, {"SALAIRE", type number}, {"Date Naiss", type datetime}, {"Lieu Naiss", type text}, {"AGE", Int64.Type}, {"Pays Habitat", type text}, {"Date Entrée", type datetime}, {"CIVIL", type text}, {"ENFANTS", Int64.Type}, {"LOGEMENT", type text}, {"VOITURE", type text}}),
    FiltreLogement = Table.SelectRows(#"Type modifié", each ([LOGEMENT] = Excel.CurrentWorkbook(){[Name="pConsult"]}[Content][Column1]{0})),
    #"Autres colonnes supprimées" = Table.SelectColumns(FiltreLogement,{"#", "Nom", "Prénom", "ENFANTS", "VOITURE"})
in
    #"Autres colonnes supprimées"

et pour que ce soit dynamique lorsque je sélectionne un élément dans ma liste, j'ai placé ce code dans le module de la feuille où se trouve la liste déroulante et la table issue de la requête Power Query que j'ai renommée t_Consultation

Code:

1
2
3
4
5
6
7
8
9
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = ThisWorkbook.Names("pConsult").RefersToRange.Address Then
    Application.EnableEvents = False
    On Error Resume Next
    Range("t_Consultation").ListObject.QueryTable.Refresh BackgroundQuery:=False
    On Error GoTo 0
    Application.EnableEvents = True
  End If
End Sub