Excel 2013 Power Query et VBA

Le 30/03/2021 à 19:23

Bonjour à tous,

Grâce à un membre du forum, qui se reconnaîtra, j'ai découvert Power Query qui me permet de fusionner et transformer des données venant de plusieurs fichiers. Idéal pour le boulot (révolutionnaire j'ai dit ! ) !

Sur mon ordi perso (Office 365), j'ai monté une macro (par l'enregistreur) qui permet de réaliser tout ça en un tour de clic, dans le but d'aider des collègues qui maîtrisent moins Excel et aussi pour que ce soit plus rapide. J'étais plein de doux rêves !

Quand tout à coup...

Il s'est avéré que la macro ne fonctionne pas avec Excel 2013, j'ai des messages d'erreurs, il ne comprend pas Power Query.
Du coup je me suis dit que j'allais faire le même enregistrement mais depuis Excel 2013. Mais tout ne s'enregistre pas, la partie de fusion des fichiers notamment.

Sub Assemblerdonnees()
'
' Assemblerdonnees Macro
'

'

    Selection.AutoFilter
    ActiveSheet.ListObjects("donnéesbrutes").Resize Range("$A$1:$X$101")
    Range("donnéesbrutes[[#Headers],[donnéesbrutes]]").Select
    ActiveSheet.ListObjects("donnéesbrutes").Resize Range("$A$1:$X$102")
    Range("donnéesbrutes[[#Headers],[Années]]").Select
End Sub

De ce que je constate, la VBA et Power Query ne sont probablement pas compatible sous Excel 2013. Peut être avez-vous un avis là-dessus ou une solution ?

Je découvre tout juste Power Query, peut-être est-il possible d'enregistrer une requête pour qu'elle soit plus simple à utiliser et l'appliquer depuis une VBA ?

La VBA serait vraiment l'idéal, en un seul clic le résultat pourrait être obtenu.

Merci pour votre lecture !

Le 31/03/2021 à 09:12

Bonjour,

Tu as certainement utilisé l'objet WorkbookQuery (VBA) qui a été introduit avec Excel 2016.

Non compatible donc avec Excel 2010 ou Excel 2013.

Cdlt.

Le 31/03/2021 à 10:42

Merci pour cette info @Jean-Eric, mais il ne me semble pas que l'enregistreur ait utilisé cet objet. J'ai mis le code enregistré depuis Office365 ci-dessous.

En le testant sur Excel, dès le premier bloc j'ai un message "Erreur d'exécution 438 : propriété ou méthode non gérée par cet objet"

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveWorkbook.Queries.Add Name:="Fichiers sources", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Folder.Files(""C:\Users\User\Desktop\assemblage données\Fichiers sources"")," & Chr(13) & "" & Chr(10) & "    #""Fichiers masqués filtrés1"" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true)," & Chr(13) & "" & Chr(10) & "    #""Appeler une fonction personnalisée1"" = Table.AddColumn(#""Fichiers masqués filtrés1"", ""Transformer le fichier"", each #""Transformer le fichier""([Content]))," & Chr(13) & "" & Chr(10) & " " & _
        "   #""Colonnes renommées1"" = Table.RenameColumns(#""Appeler une fonction personnalisée1"", {""Name"", ""Source.Name""})," & Chr(13) & "" & Chr(10) & "    #""Autres colonnes supprimées1"" = Table.SelectColumns(#""Colonnes renommées1"", {""Source.Name"", ""Transformer le fichier""})," & Chr(13) & "" & Chr(10) & "    #""Colonne de tables développée1"" = Table.ExpandTableColumn(#""Autres colonnes supprimées1"", ""Transforme" & _
        "r le fichier"", Table.ColumnNames(#""Transformer le fichier""(#""Exemple de fichier"")))," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""Colonne de tables développée1"",{{""Source.Name"", type text}, {""Pacage"", Int64.Type}, {""Dénomination"", type text}, {""Code commune"", Int64.Type}, {""Commune"", type text}, {""Type exploitant"", type text}, {""Nombre a" & _
        "ssociés"", Int64.Type}, {"" Code mesure"", type text}, {""Région"", type text}, {""Dispositif"", type text}, {""Montant par unité"", type number}, {""Unité"", type text}, {""Etat"", type text}, {""Montant calculé engagement comptable"", type number}, {""Quantité à engager pour 5 ans"", type number}, {""Montant à engager - total 5 ans"", type number}, {""Quantit" & _
        "é à engager durée réduite"", type number}, {""Nombre années durée réduite"", Int64.Type}, {""Montant à engager durée réduite"", type number}, {""Plan de financement validé"", type text}, {""Financeur"", type text}, {""Taux financeur"", type number}, {""Montant financeur"", type number}, {""Montant financeur déjà engagé campagne courante"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Fr" & _
        "actionner la colonne par position"" = Table.SplitColumn(#""Type modifié"", ""Source.Name"", Splitter.SplitTextByPositions({0, 9, 13}), {""Source.Name.1"", ""Source.Name.2"", ""Source.Name.3""})," & Chr(13) & "" & Chr(10) & "    #""Type modifié1"" = Table.TransformColumnTypes(#""Fractionner la colonne par position"",{{""Source.Name.1"", type text}, {""Source.Name.2"", Int64.Type}, {""Source.Nam" & _
        "e.3"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Colonnes supprimées"" = Table.RemoveColumns(#""Type modifié1"",{""Source.Name.1"", ""Source.Name.3""})," & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"" = Table.RenameColumns(#""Colonnes supprimées"",{{""Source.Name.2"", ""Année""}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"""
    ActiveWorkbook.Queries.Add Name:="Exemple de fichier", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Folder.Files(""C:\Users\User\Desktop\assemblage données\Fichiers sources"")," & Chr(13) & "" & Chr(10) & "    Navigation1 = Source{0}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Navigation1"
    ActiveWorkbook.Queries.Add Name:="Paramètre1", Formula:= _
        "#""Exemple de fichier"" meta [IsParameterQuery=true, BinaryIdentifier=#""Exemple de fichier"", Type=""Binary"", IsParameterQueryRequired=true]"
    ActiveWorkbook.Queries.Add Name:="Transformer l'exemple de fichier", Formula _
        := _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Paramètre1,[Delimiter="";"", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""En-têtes promus"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""En-têtes promus"""
    ActiveWorkbook.Queries.Add Name:="Transformer le fichier", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = (Paramètre1) => let" & Chr(13) & "" & Chr(10) & "        Source = Csv.Document(Paramètre1,[Delimiter="";"", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "        #""En-têtes promus"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & "    in" & Chr(13) & "" & Chr(10) & "        #""En-têtes promus""" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Fichiers sources"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Fichiers sources]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Fichiers_sources"
        .Refresh BackgroundQuery:=False
    End With
    Workbooks("Assemblerdonnees - Copie.xlsx").Connections.Add2 _
        "Requête - Exemple de fichier", _
        "Connexion à la requête « Exemple de fichier » dans le classeur.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Exemple de fichier"";Extended Properties=""""" _
        , "SELECT * FROM [Exemple de fichier]", 2
    Workbooks("Assemblerdonnees - Copie.xlsx").Connections.Add2 _
        "Requête - Paramètre1", _
        "Connexion à la requête « Paramètre1 » dans le classeur.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Paramètre1;Extended Properties=""""" _
        , "SELECT * FROM [Paramètre1]", 2
    Workbooks("Assemblerdonnees - Copie.xlsx").Connections.Add2 _
        "Requête - Transformer l'exemple de fichier", _
        "Connexion à la requête « Transformer l'exemple de fichier » dans le classeur." _
        , _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transformer l'exemple de fichier"";Extended Properties=""""" _
        , "SELECT * FROM [Transformer l'exemple de fichier]", 2
    Workbooks("Assemblerdonnees - Copie.xlsx").Connections.Add2 _
        "Requête - Transformer le fichier", _
        "Connexion à la requête « Transformer le fichier » dans le classeur.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transformer le fichier"";Extended Properties=""""" _
        , "SELECT * FROM [Transformer le fichier]", 2
    Application.CommandBars("Queries and Connections").Visible = False
End Sub

Le 31/03/2021 à 10:47

Re,

Un peu de lecture pour confirmer mes propos.

Queries.Add

Cdlt.

Le 31/03/2021 à 11:03

Merci @Jean-Eric pour ta réponse. Je suis novice et je cherchais linéairement "WorkbookQuery"

J'imagine qu'il n'y a aucune alternative à ces objets composés de la sorte, sinon tu m'aurais donnée une réponse...

Et bien je n'ai plus qu'à faire un tuto !

Merci encore et bonne journée !

Rechercher des sujets similaires à "2013 power query vba"