remove last comma in array

[promoboy]

I have this code to get all my tables in my worksheets and i need to remove the last comma of the string to use the string in power query table combine.

sub TableNames ()

Dim MyArray() As String
Dim ArraySize As Integer
Dim Tbl As ListObject
Dim tables
ArraySize = 0

ReDim MyArray(0 To 0)

For Each ws In ActiveWorkbook.Worksheets
    For Each Tbl In ws.ListObjects
        ReDim Preserve MyArray(ArraySize) As String
        MyArray(UBound(MyArray)) = Tbl.Name & ","
        ArraySize = ArraySize + 1
    Next Tbl
    
Next ws

tables = Join(MyArray, vbCrLf)
MsgBox tables

End Sub

  ActiveWorkbook.Queries.Add Name:="Totaal", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Table.Combine({""" & tables & """})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(Source, each ([Activiteit code] = ""Total""))," & Chr(13) & "" & Chr(10) & "    #""Removed Other Columns"" = Table.SelectColumns(#""Filtered Rows"",{""Totaal euro"", ""Contract"", ""PO"", ""Datum"", ""Meetstaat"", ""Locatie"", ""Order"", ""Referentie uitvoerder""})," & Chr(13) & "" & Chr(10) & "    #""Extracted D" & _
        "ate"" = Table.TransformColumns(#""Removed Other Columns"",{{""Datum"", DateTime.Date, type date}})," & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"" = Table.ReorderColumns(#""Extracted Date"",{""Contract"", ""PO"", ""Datum"", ""Meetstaat"", ""Locatie"", ""Order"", ""Referentie uitvoerder"", ""Totaal euro""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"""

 

[FormR]

Hi, instead of concatenating the comma here..

MyArray(UBound(MyArray)) = tbl.Name & ","

You could do it in the Join() instead - ie..

tables = Join(MyArray, "," & vbCrLf)

But would it not be simpler to build up the string as you go rather that use an array? Something like..

Sub TableNames()
Dim ws As Worksheet
Dim tbl As ListObject
Dim tables As String

For Each ws In ActiveWorkbook.Worksheets
    For Each tbl In ws.ListObjects
        tables = tables & "," & vbCrLf & tbl.Name
    Next tbl
Next ws
    
tables = Mid(tables, 3)

MsgBox tables

End Sub

And does the query really need the carriage return line feeds?

if not - change these lines to..
tables = tables & "," & tbl.Name
and
tables = Mid(tables, 2)

 

[promoboy]

Hi, instead of concatenating the comma here..

MyArray(UBound(MyArray)) = tbl.Name & ","

You could do it in the Join() instead - ie..

tables = Join(MyArray, "," & vbCrLf)

But would it not be simpler to build up the string as you go rather that use an array? Something like..

Sub TableNames()
Dim ws As Worksheet
Dim tbl As ListObject
Dim tables As String

For Each ws In ActiveWorkbook.Worksheets
    For Each tbl In ws.ListObjects
        tables = tables & "," & vbCrLf & tbl.Name
    Next tbl
Next ws
   
tables = Mid(tables, 3)

MsgBox tables

End Sub

And does the query really need the carriage return line feeds?

if not - change these lines to..
tables = tables & "," & tbl.Name
and
tables = Mid(tables, 2)

Thank you for the quick reply

i changed it with your code and the output is perfect but when i try to use it in the code to get the power query tables i get this error

 

[FormR]

when i try to use it in the code to get the power query tables i get this error

Hi, sorry, but I'm not sure about that part.

What does the query look like if you create it manually and how does that compare with what your code creates?

 

[promoboy]

Hi, sorry, but I'm not sure about that part.

What does the query look like if you create it manually and how does that compare with what your code creates?

when i manually make the query it shows = Table.Combine({Meetstaat, Meetstaat_123456, Meetstaat_123457, Meetstaat_123458})

 

[FormR]

If you go to the advanced editor - what do you see there? And how does it compare to what your code creates?

 

[promoboy]

If you go to the advanced editor - what do you see there? And how does it compare to what your code creates?

let
    Source = Table.Combine({Meetstaat, Meetstaat_123456, Meetstaat_123457, Meetstaat_123458}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Activiteit code] = "Total")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Totaal euro", "Contract", "PO", "Datum", "Meetstaat", "Locatie", "Order"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Contract", "PO", "Datum", "Meetstaat", "Locatie", "Order", "Totaal euro"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Reordered Columns",{"Contract", "PO", "Datum", "Meetstaat", "Locatie", "Order", "Totaal euro"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Datum", type date}})
in
    #"Changed Type"

 

[FormR]

And how does it compare to what your code creates?

?

i.e. what does this portion of your code return..? is it different?

"let" & Chr(13) & "" & Chr(10) & "    Source = Table.Combine({""" & tables & """})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(Source, each ([Activiteit code] = ""Total""))," & Chr(13) & "" & Chr(10) & "    #""Removed Other Columns"" = Table.SelectColumns(#""Filtered Rows"",{""Totaal euro"", ""Contract"", ""PO"", ""Datum"", ""Meetstaat"", ""Locatie"", ""Order"", ""Referentie uitvoerder""})," & Chr(13) & "" & Chr(10) & "    #""Extracted D" & _
        "ate"" = Table.TransformColumns(#""Removed Other Columns"",{{""Datum"", DateTime.Date, type date}})," & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"" = Table.ReorderColumns(#""Extracted Date"",{""Contract"", ""PO"", ""Datum"", ""Meetstaat"", ""Locatie"", ""Order"", ""Referentie uitvoerder"", ""Totaal euro""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"""

 

[promoboy]

?

i.e. what does this portion of your code return..? is it different?

"let" & Chr(13) & "" & Chr(10) & "    Source = Table.Combine({""" & tables & """})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(Source, each ([Activiteit code] = ""Total""))," & Chr(13) & "" & Chr(10) & "    #""Removed Other Columns"" = Table.SelectColumns(#""Filtered Rows"",{""Totaal euro"", ""Contract"", ""PO"", ""Datum"", ""Meetstaat"", ""Locatie"", ""Order"", ""Referentie uitvoerder""})," & Chr(13) & "" & Chr(10) & "    #""Extracted D" & _
        "ate"" = Table.TransformColumns(#""Removed Other Columns"",{{""Datum"", DateTime.Date, type date}})," & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"" = Table.ReorderColumns(#""Extracted Date"",{""Contract"", ""PO"", ""Datum"", ""Meetstaat"", ""Locatie"", ""Order"", ""Referentie uitvoerder"", ""Totaal euro""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"""

when i put Source = Table.Combine({Meetstaat, Meetstaat_123456, Meetstaat_123457, Meetstaat_123458}) in excel vba instead of Source = Table.Combine({""" & tables & """})
all runs fine from vba
but my tables are always different ...

 

[FormR]

Source = Table.Combine({""" & tables & """})

You don't need the extra quotes around the list of tables.

Try like this.

Sub tablenames()

Dim ws As Worksheet
Dim tbl As ListObject
Dim tables As String

For Each ws In ActiveWorkbook.Worksheets
    For Each tbl In ws.ListObjects
        tables = tables & "," & tbl.Name
    Next tbl
Next ws
    
tables = Mid(tables, 2)

ActiveWorkbook.Queries.Add Name:="Totaal", Formula:="let Source = Table.Combine({" & tables & "}) in Source"

End Sub