Hi Guys,

i am using code from here:

https://www.excelcampus.com/vba/powe...ly-all-tables/

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

  '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

End Sub
Can anybody explain why to create connection to created query and confirm that connection name is hidden in excel workbook memoery?

Query is just query in PQ. But why to add connection to it?

Best,
Jacek