I want to automate some PowerQuery data transformation so as to import it, at the click of a button, into a workbook in which I'm doing a whole lot of other stuff.
This is how I've gone about it... I:
What I got is this:
Sub ImportPQ()
ExecuteExcel4Macro _
"(""Contractuels"",""let" & Chr(10) & " Source = Csv.Document(File.Contents(""/Users/ari/Nextcloud Nice/CMI/Contractuels.csv""), [Delimiter = "";"", Columns = 15, QuoteStyle = QuoteStyle.None])," & Chr(10) & " Headers = Table.PromoteHeaders(Source, [PromoteAllScalars = true])," & Chr(10) & " EstablishedColumnTypes = Table.TransformColumnTypes(Headers, {{""Nom"", type text}, {""Prenom"", type text}, {""Nom" & _
"_JF"", type text}, {""Date_naissance"", type date}, {""mail_ouvert"", type text}, {""Statut"", type text}, {""Entree_grade"", type date}, {""Echelon"", Int64.Type}, {""Date_echelon"", type date}, {""Rne"", type text}, {""EtabNom"", type text}, {""EtabBassin"", type text}, {""EtabVille"", type text}, {""nbre_heure"", type text}, {""Type_nomination"", type text}}, ""f" & _
"r-FR"")," & Chr(10) & " ChangedTypeNbHours = Table.TransformColumnTypes(EstablishedColumnTypes, {{""nbre_heure"", type number}})," & Chr(10) & " NbContracts = Table.AddColumn(ChangedTypeNbHours, ""REP_SUP"", each if [Type_nomination] = ""REP"" or [Type_nomination] = ""SUP"" then 1 else 0, Int64.Type)," & Chr(10) & " GroupedTable = Table.Group(NbContracts, {""Nom"", ""Prenom"", ""Nom_JF"", ""Date_naissanc" & _
"e"", ""mail_ouvert"", ""Entree_grade"", ""Statut"", ""Echelon"", ""Date_echelon""}," & Chr(10) & " {" & Chr(10) & " {" & Chr(10) & " ""Affectation""," & Chr(10) & " each" & Chr(10) & " let" & Chr(10) & " SommeAffectations = List.Sum ([REP_SUP])" & Chr(10) & " in" & Chr(10) & " if SommeAffectations > 1 then ""Multiple"" else if SommeAffectations = 1 then ""Unique"" else ""Sans""," & Chr(10) & " type text" & Chr(10) & " }," & Chr(10) & " {" & Chr(10) & " " & _
" ""Affectation_principale""," & Chr(10) & " each" & Chr(10) & " let" & Chr(10) & " RepSup = Table.SelectRows (_, each [Type_nomination] = ""REP"" or [Type_nomination] = ""SUP"")," & Chr(10) & " AffPrinc = Table.Max (RepSup, ""nbre_heure"")" & Chr(10) & " in" & Chr(10) & " AffPrinc" & Chr(10) & " }" & Chr(10) & " }" & Chr(10) & " )," & Chr(10) & " ExpandedAffPrinc = Table.ExpandRecordColumn(GroupedTable, ""Affectation_principale""," & _
" {""Rne"", ""EtabNom"", ""EtabBassin"", ""EtabVille"", ""nbre_heure""}, {""Rne"", ""EtabNom"", ""EtabBassin"", ""EtabVille"", ""nbre_heure""})," & Chr(10) & " DetectionCx = Table.AddColumn(ExpandedAffPrinc, ""Cx"", each if [Entree_grade] >= #date((if Date.From(DateTime.LocalNow()) >= #date (Date.Year(DateTime.LocalNow()), 9, 1) then Date.Year(DateTime.LocalNow()) else Date.Year(" & _
"DateTime.LocalNow()) - 1), 7, 1) then ""C0"" else if [Entree_grade] < #date((if Date.From(DateTime.LocalNow()) >= #date (Date.Year(DateTime.LocalNow()), 9, 1) then Date.Year(DateTime.LocalNow()) else Date.Year(DateTime.LocalNow()) - 1), 7, 1) and [Entree_grade] >= #date((if Date.From(DateTime.LocalNow()) >= #date (Date.Year(DateTime.LocalNow()), 9, 1) then Date.Year" & _
"(DateTime.LocalNow()) - 1 else Date.Year(DateTime.LocalNow()) - 2), 7, 1) then ""C1"" else if (Duration.Days(DateTime.Date(DateTime.LocalNow())-[Date_echelon]) / 365) > 5 and [Statut]<> ""CDI"" then ""CDIsable"" else """", type text)," & Chr(10) & " ReorderCx = Table.ReorderColumns(DetectionCx, {""Nom"", ""Prenom"", ""Nom_JF"", ""Date_naissance"", ""mail_ouvert"", ""Entree_grade" & _
""", ""Cx"", ""Statut"", ""Echelon"", ""Date_echelon"", ""Affectation"", ""Rne"", ""EtabNom"", ""EtabBassin"", ""EtabVille"", ""nbre_heure""})" & Chr(10) & "in" & Chr(10) & " ReorderCx"")" & _
""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Contractuels;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Contractuels]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.RefreshPeriod = False
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_ExternalData_1"
.Refresh BackgroundQuery:=False
End With
End Sub
And this is where everything went haywire: I got an error on ExecuteExcel4Macro, so I tried to modify the VBA code according to what I read here: How to automate a power query in VBA?
Private Sub ImportPQ()
Dim qry As WorkbookQuery, qryTxt As String
qryTxt = _
"(""Contractuels"",""let" & Chr(10) & " Source = Csv.Document(File.Contents(""/Users/ari/Nextcloud Nice/CMI/Contractuels.csv""), [Delimiter = "";"", Columns = 15, QuoteStyle = QuoteStyle.None])," & Chr(10) & " Headers = Table.PromoteHeaders(Source, [PromoteAllScalars = true])," & Chr(10) & " EstablishedColumnTypes = Table.TransformColumnTypes(Headers, {{""Nom"", type text}, {""Prenom"", type text}, {""Nom" & _
"_JF"", type text}, {""Date_naissance"", type date}, {""mail_ouvert"", type text}, {""Statut"", type text}, {""Entree_grade"", type date}, {""Echelon"", Int64.Type}, {""Date_echelon"", type date}, {""Rne"", type text}, {""EtabNom"", type text}, {""EtabBassin"", type text}, {""EtabVille"", type text}, {""nbre_heure"", type text}, {""Type_nomination"", type text}}, ""f" & _
"r-FR"")," & Chr(10) & " ChangedTypeNbHours = Table.TransformColumnTypes(EstablishedColumnTypes, {{""nbre_heure"", type number}})," & Chr(10) & " NbContracts = Table.AddColumn(ChangedTypeNbHours, ""REP_SUP"", each if [Type_nomination] = ""REP"" or [Type_nomination] = ""SUP"" then 1 else 0, Int64.Type)," & Chr(10) & " GroupedTable = Table.Group(NbContracts, {""Nom"", ""Prenom"", ""Nom_JF"", ""Date_naissanc" & _
"e"", ""mail_ouvert"", ""Entree_grade"", ""Statut"", ""Echelon"", ""Date_echelon""}," & Chr(10) & " {" & Chr(10) & " {" & Chr(10) & " ""Affectation""," & Chr(10) & " each" & Chr(10) & " let" & Chr(10) & " SommeAffectations = List.Sum ([REP_SUP])" & Chr(10) & " in" & Chr(10) & " if SommeAffectations > 1 then ""Multiple"" else if SommeAffectations = 1 then ""Unique"" else ""Sans""," & Chr(10) & " type text" & Chr(10) & " }," & Chr(10) & " {" & Chr(10) & " " & _
" ""Affectation_principale""," & Chr(10) & " each" & Chr(10) & " let" & Chr(10) & " RepSup = Table.SelectRows (_, each [Type_nomination] = ""REP"" or [Type_nomination] = ""SUP"")," & Chr(10) & " AffPrinc = Table.Max (RepSup, ""nbre_heure"")" & Chr(10) & " in" & Chr(10) & " AffPrinc" & Chr(10) & " }" & Chr(10) & " }" & Chr(10) & " )," & Chr(10) & " ExpandedAffPrinc = Table.ExpandRecordColumn(GroupedTable, ""Affectation_principale""," & _
" {""Rne"", ""EtabNom"", ""EtabBassin"", ""EtabVille"", ""nbre_heure""}, {""Rne"", ""EtabNom"", ""EtabBassin"", ""EtabVille"", ""nbre_heure""})," & Chr(10) & " DetectionCx = Table.AddColumn(ExpandedAffPrinc, ""Cx"", each if [Entree_grade] >= #date((if Date.From(DateTime.LocalNow()) >= #date (Date.Year(DateTime.LocalNow()), 9, 1) then Date.Year(DateTime.LocalNow()) else Date.Year(" & _
"DateTime.LocalNow()) - 1), 7, 1) then ""C0"" else if [Entree_grade] < #date((if Date.From(DateTime.LocalNow()) >= #date (Date.Year(DateTime.LocalNow()), 9, 1) then Date.Year(DateTime.LocalNow()) else Date.Year(DateTime.LocalNow()) - 1), 7, 1) and [Entree_grade] >= #date((if Date.From(DateTime.LocalNow()) >= #date (Date.Year(DateTime.LocalNow()), 9, 1) then Date.Year" & _
"(DateTime.LocalNow()) - 1 else Date.Year(DateTime.LocalNow()) - 2), 7, 1) then ""C1"" else if (Duration.Days(DateTime.Date(DateTime.LocalNow())-[Date_echelon]) / 365) > 5 and [Statut]<> ""CDI"" then ""CDIsable"" else """", type text)," & Chr(10) & " ReorderCx = Table.ReorderColumns(DetectionCx, {""Nom"", ""Prenom"", ""Nom_JF"", ""Date_naissance"", ""mail_ouvert"", ""Entree_grade" & _
""", ""Cx"", ""Statut"", ""Echelon"", ""Date_echelon"", ""Affectation"", ""Rne"", ""EtabNom"", ""EtabBassin"", ""EtabVille"", ""nbre_heure""})" & Chr(10) & "in" & Chr(10) & " ReorderCx"")" & _
""
For Each q In ThisWorkbook.Queries
q.Delete
Next q
Set qry = ThisWorkbook.Queries.Add("Contractuels", qryTxt)
Sheet3.Activate
With Sheet3.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Contractuels;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Contractuels]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.RefreshPeriod = False
.PreserveColumnInfo = True
.ListObject.DisplayName = "Contractuels"
.Refresh BackgroundQuery:=False
End With
End Sub
Now, I get an error "The name 'Source' wasn't recognized. Make sure it is spelled correctly" (Run-time error 1004) (but I got other others before, which I haven't saved exactly [but there was something akin to Method .Refresh unrecognized about the last line of the script], and Excel crashed after several tries). Also, when I have a look at the 'Queries and Connections' window, there are queries piling up, and I cannot delete them, even manually (even though it seemed I requested the deletion of all queries in my VBA code)
As you can see, I am in way over my head... Any help will therefore be greatly appreciated!
Your query text is wrong. It should just be:
qryTxt = _
"let" & Chr(10) & " Source = Csv.Document(File.Contents(""/Users/ari/Nextcloud Nice/CMI/Contractuels.csv""), [Delimiter = "";"", Columns = 15, QuoteStyle = QuoteStyle.None])," & Chr(10) & " Headers = Table.PromoteHeaders(Source, [PromoteAllScalars = true])," & Chr(10) & " EstablishedColumnTypes = Table.TransformColumnTypes(Headers, {{""Nom"", type text}, {""Prenom"", type text}, {""Nom" & _
"_JF"", type text}, {""Date_naissance"", type date}, {""mail_ouvert"", type text}, {""Statut"", type text}, {""Entree_grade"", type date}, {""Echelon"", Int64.Type}, {""Date_echelon"", type date}, {""Rne"", type text}, {""EtabNom"", type text}, {""EtabBassin"", type text}, {""EtabVille"", type text}, {""nbre_heure"", type text}, {""Type_nomination"", type text}}, ""f" & _
"r-FR"")," & Chr(10) & " ChangedTypeNbHours = Table.TransformColumnTypes(EstablishedColumnTypes, {{""nbre_heure"", type number}})," & Chr(10) & " NbContracts = Table.AddColumn(ChangedTypeNbHours, ""REP_SUP"", each if [Type_nomination] = ""REP"" or [Type_nomination] = ""SUP"" then 1 else 0, Int64.Type)," & Chr(10) & " GroupedTable = Table.Group(NbContracts, {""Nom"", ""Prenom"", ""Nom_JF"", ""Date_naissanc" & _
"e"", ""mail_ouvert"", ""Entree_grade"", ""Statut"", ""Echelon"", ""Date_echelon""}," & Chr(10) & " {" & Chr(10) & " {" & Chr(10) & " ""Affectation""," & Chr(10) & " each" & Chr(10) & " let" & Chr(10) & " SommeAffectations = List.Sum ([REP_SUP])" & Chr(10) & " in" & Chr(10) & " if SommeAffectations > 1 then ""Multiple"" else if SommeAffectations = 1 then ""Unique"" else ""Sans""," & Chr(10) & " type text" & Chr(10) & " }," & Chr(10) & " {" & Chr(10) & " " & _
" ""Affectation_principale""," & Chr(10) & " each" & Chr(10) & " let" & Chr(10) & " RepSup = Table.SelectRows (_, each [Type_nomination] = ""REP"" or [Type_nomination] = ""SUP"")," & Chr(10) & " AffPrinc = Table.Max (RepSup, ""nbre_heure"")" & Chr(10) & " in" & Chr(10) & " AffPrinc" & Chr(10) & " }" & Chr(10) & " }" & Chr(10) & " )," & Chr(10) & " ExpandedAffPrinc = Table.ExpandRecordColumn(GroupedTable, ""Affectation_principale""," & _
" {""Rne"", ""EtabNom"", ""EtabBassin"", ""EtabVille"", ""nbre_heure""}, {""Rne"", ""EtabNom"", ""EtabBassin"", ""EtabVille"", ""nbre_heure""})," & Chr(10) & " DetectionCx = Table.AddColumn(ExpandedAffPrinc, ""Cx"", each if [Entree_grade] >= #date((if Date.From(DateTime.LocalNow()) >= #date (Date.Year(DateTime.LocalNow()), 9, 1) then Date.Year(DateTime.LocalNow()) else Date.Year(" & _
"DateTime.LocalNow()) - 1), 7, 1) then ""C0"" else if [Entree_grade] < #date((if Date.From(DateTime.LocalNow()) >= #date (Date.Year(DateTime.LocalNow()), 9, 1) then Date.Year(DateTime.LocalNow()) else Date.Year(DateTime.LocalNow()) - 1), 7, 1) and [Entree_grade] >= #date((if Date.From(DateTime.LocalNow()) >= #date (Date.Year(DateTime.LocalNow()), 9, 1) then Date.Year" & _
"(DateTime.LocalNow()) - 1 else Date.Year(DateTime.LocalNow()) - 2), 7, 1) then ""C1"" else if (Duration.Days(DateTime.Date(DateTime.LocalNow())-[Date_echelon]) / 365) > 5 and [Statut]<> ""CDI"" then ""CDIsable"" else """", type text)," & Chr(10) & " ReorderCx = Table.ReorderColumns(DetectionCx, {""Nom"", ""Prenom"", ""Nom_JF"", ""Date_naissance"", ""mail_ouvert"", ""Entree_grade" & _
""", ""Cx"", ""Statut"", ""Echelon"", ""Date_echelon"", ""Affectation"", ""Rne"", ""EtabNom"", ""EtabBassin"", ""EtabVille"", ""nbre_heure""})" & Chr(10) & "in" & Chr(10) & " ReorderCx"