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"""

 

[promoboy]

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

Ok that did the trick

thank you very much