power query with VBA

[svennn]

Hello,

I want to do a power query automatic and i want to change the path of the source file.

I have recorded a macro and want to change it a bit, so the path is variable.
I still have a problem with my VBA.

Sub Openen_CSV()
'
' Openen_CSV Macro
'

'

Dim Path As Variant


    Sheets("Lijst Bestanden").Select
    Path = Range("C2").Value
    
    ActiveWorkbook.Queries.Add Name:="Logging", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Bron = Table.FromColumns({Lines.FromBinary(File.Contents(""" & Path & """)), null, null, 1252)})," & Chr(13) & "" & Chr(10) & "    #""Kolom splitsen op scheidingsteken"" = Table.SplitColumn(Bron, ""Column1"", Splitter.SplitTextByDelimiter("","", QuoteStyle.None), {""Column1.1"", ""Column1.2"", ""Colu" & _
        "mn1.3"", ""Column1.4"", ""Column1.5"", ""Column1.6"", ""Column1.7"", ""Column1.8""})," & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd"" = Table.TransformColumnTypes(#""Kolom splitsen op scheidingsteken"",{{""Column1.1"", type text}, {""Column1.2"", type text}, {""Column1.3"", type text}, {""Column1.4"", type text}, {""Column1.5"", type text}, {""Column1.6"", type text}, {""Column1.7"", type" & _
        " text}, {""Column1.8"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Kolommen verwijderd"" = Table.RemoveColumns(#""Type gewijzigd"",{""Column1.1"", ""Column1.8""})," & Chr(13) & "" & Chr(10) & "    #""Headers met verhoogd niveau"" = Table.PromoteHeaders(#""Kolommen verwijderd"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd1"" = Table.TransformColumnTypes(#""Headers met verhoogd niveau"",{{""Time"", Int64.Type}" & _
        ", {""Water/Algemeen"", Int64.Type}, {""Water/Buffer"", Int64.Type}, {""Temperatuur/KKWW"", Int64.Type}, {""Temperatuur/KKW"", Int64.Type}, {""Temperatuur/FT"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Lege rijen verwijderd"" = Table.SelectRows(#""Type gewijzigd1"", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"""", null})))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Lege rijen verwijderd" & _
        """"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Logging;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Logging]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Logging"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[jkpieterse]

You recorded adding a new query and a new table tied to the query. What you must do instead is have the macro Update the existing query and then refresh:

Sub Openen_CSV()
'
' Openen_CSV Macro
'

'

    Dim Path As Variant
    Dim query2Edit As WorkbookQuery
    Set query2Edit = ActiveWorkbook.Queries("Logging")
    Path = WorkSheets("Lijst Bestanden").Range("C2").Value

    query2Edit.Formula = _
    "let" & Chr(13) & "" & Chr(10) & "    Bron = Table.FromColumns({Lines.FromBinary(File.Contents(""" & Path & """)), null, null, 1252)})," & Chr(13) & "" & Chr(10) & "    #""Kolom splitsen op scheidingsteken"" = Table.SplitColumn(Bron, ""Column1"", Splitter.SplitTextByDelimiter("","", QuoteStyle.None), {""Column1.1"", ""Column1.2"", ""Colu" & _
                         "mn1.3"", ""Column1.4"", ""Column1.5"", ""Column1.6"", ""Column1.7"", ""Column1.8""})," & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd"" = Table.TransformColumnTypes(#""Kolom splitsen op scheidingsteken"",{{""Column1.1"", type text}, {""Column1.2"", type text}, {""Column1.3"", type text}, {""Column1.4"", type text}, {""Column1.5"", type text}, {""Column1.6"", type text}, {""Column1.7"", type" & _
                         " text}, {""Column1.8"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Kolommen verwijderd"" = Table.RemoveColumns(#""Type gewijzigd"",{""Column1.1"", ""Column1.8""})," & Chr(13) & "" & Chr(10) & "    #""Headers met verhoogd niveau"" = Table.PromoteHeaders(#""Kolommen verwijderd"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Type gewijzigd1"" = Table.TransformColumnTypes(#""Headers met verhoogd niveau"",{{""Time"", Int64.Type}" & _
                         ", {""Water/Algemeen"", Int64.Type}, {""Water/Buffer"", Int64.Type}, {""Temperatuur/KKWW"", Int64.Type}, {""Temperatuur/KKW"", Int64.Type}, {""Temperatuur/FT"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Lege rijen verwijderd"" = Table.SelectRows(#""Type gewijzigd1"", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"""", null})))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Lege rijen verwijderd" & _
                         """"
    ActiveWorkbook.RefreshAll
End Sub

 

[svennn]

hello,

thank you for helping but a still get a error message that the name of the source isn't correct.

thanks!

 

[jkpieterse]

When -precisely- do you get that error?