combine all tables from a workbook with power query

[promoboy]

I found a working code to connect all tables from my workbook on Excel campus
i recorded a macro to tweak the needed data into one list but need a string or something to have the table names from all the connected tables into the combined query.
the tables are always different and also the names

here the code i use

this fraze "Meetstaat_6065, Meetstaat_6064" in this code Source = Table.Combine({Meetstaat_6065, Meetstaat_6064}) i need to have automatic all table names from the workbook

Option Explicit

Sub Add_Connection_All_Tables()
'Creates Connection Only Queries to all tables in the active workbook.

Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject
Dim sName As String
Dim sFormula As String
Dim wq As WorkbookQuery
Dim bExists As Boolean
Dim vbAnswer As VbMsgBoxResult
Dim vbDataModel As VbMsgBoxResult
Dim i As Long
Dim dStart As Double
Dim dTime As Double
Dim tables As String

  'Display message box to prompt user to run the macro
  vbAnswer = MsgBox("Do you want to run the macro to create connections for all Tables in this workbook?", vbYesNo, "Power Query Connect All Tables Macro")

  If vbAnswer = vbYes Then
 
    'Prompt user for Data Model option
   'vbDataModel = MsgBox("Do you want to add the data to the Data Model?", vbYesNo + vbDefaultButton2, "Power Query Connect All Tables Macro")

    'Set variables
    dStart = Timer
    Set wb = ActiveWorkbook
    
    'Loop sheets and tables
    For Each ws In ActiveWorkbook.Worksheets
      For Each lo In ws.ListObjects
        
        sName = lo.Name
        sFormula = "Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]"
        
        'Check if query exists
        bExists = False
        For Each wq In wb.Queries
          If InStr(1, wq.Formula, sFormula) > 0 Then
            bExists = True
          End If
        Next wq
        
        'Add query if it does not exist
        If bExists = False Then
        
          'Add query
          wb.Queries.Add Name:=sName, _
                         Formula:="let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
          'Add connection
          wb.Connections.Add2 Name:="Query - " & sName, _
                              Description:="Connection to the '" & sName & "' query in the workbook.", _
                              ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & sName & ";Extended Properties=""""", _
                              CommandText:="SELECT * FROM [" & sName & "]", _
                              lCmdtype:=2, _
                              CreateModelConnection:=False, _
                              ImportRelationships:=False
                              
          'Add to Data Model
          'If vbDataModel = vbYes Then
            'wb.Connections.Add2 Name:="Query - " & sName, _
                              '  Description:="Connection to the '" & sName & "' query in the workbook.", _
                              '  ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & sName & ";Extended Properties=", _
                              '  CommandText:="" & sName & "", _
                              '  lCmdtype:=6, _
                              '  CreateModelConnection:=True, _
                              '  ImportRelationships:=False
          'End If
          
          'Count connections
          i = i + 1
          
        End If
      Next lo
    Next ws
 
  'Calc run time
  dTime = Timer - dStart
 
  'MsgBox i & " connections have been created in " & Format(dTime, "0.0") & " seconds.", vbOKOnly, "Process Complete"

  End If
 
.Add Name:="Totaal", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Table.Combine({Meetstaat_6065, Meetstaat_6064})," & 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"""
    ActiveWorkbook.Worksheets.Add
    ActiveSheet.Name = "Totaal"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Totaal;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Totaal]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Totaal"
        .Refresh BackgroundQuery:=False
    End With
    Range("Totaal[[#Headers],[Referentie uitvoerder]]").Select
    ActiveSheet.ListObjects("Totaal").ShowTotals = True
End Sub