' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    Adjust Properties of all Query Tables in the Active Workbooks
'   Module:         modQTProperties
'   Author:         Robert Mundigl
'   Copyright:      © 2020 by Robert Mundigl, Clearly and Simply, www.clearlyandsimply.com. All rights reserved.
'   Last edit:      26th of May 2020
'   Purpose:        Loop through all Query Tables on all Worksheets and adjust selected properties and the table style
' ----------------------------------------------------------------------------------------------------------------------------------
Option Explicit

'--------------------------------------------------------------------------------------------------
Sub ChangeQueryTableProperties()
' Looping through all Query Tables of the active workbook and adjusing selected properties
' like the autofitting of column widhts and the table style

' Query Table properties and their default values:

' .RowNumbers = False                     ' True = add row numbers as the first column (starting at 0)
' .FillAdjacentFormulas = False           ' True = formulas to the right of it are automatically updated whenever the query table is refreshed
' .PreserveFormatting = True              ' True = apply AutoFormat style
' .RefreshOnFileOpen = False              ' True = table is automatically updated each time the workbook is opened
' .BackgroundQuery = True                 ' True = queries for the query table are performed asynchronously (in the background)
' .RefreshStyle = xlInsertDeleteCells     ' Sets the way rows on the specified worksheet are added or deleted
' .SavePassword = False                   ' True = password information in an ODBC connection string is saved with the specified query
' .SaveData = True                        ' True = data is saved with the workbook
' .AdjustColumnWidth = True               ' True = the column widths are automatically adjusted for the best fit each time you refresh
' .RefreshPeriod = 0                      ' Sets the number of minutes between automatic refreshes (set 0 to disably automatic refreshes)
' .PreserveColumnInfo = True              ' True = column sorting, filtering, and layout information is preserved
'--------------------------------------------------------------------------------------------------
Dim wsCurrentSheet As Worksheet
Dim loTable As ListObject
Dim qtQueryTable As QueryTable

    ' Loop through all worksheets of the active workbook
    For Each wsCurrentSheet In ThisWorkbook.Worksheets
        
        ' Loop through all list objects of the current worksheets
        For Each loTable In wsCurrentSheet.ListObjects

            ' Check if there is a query for the table
            Set qtQueryTable = Nothing
            
            On Error Resume Next
            Set qtQueryTable = loTable.QueryTable
            On Error GoTo 0
            
            If Not (qtQueryTable Is Nothing) Then
                ' Change the settings of the Query Table
                With qtQueryTable
                    ' Set the AdjustColumnWidth property to False (no autofit of column widths)
                    .AdjustColumnWidth = False
                    ' Use the workbook's standard Table Style for the query table
                    .ListObject.TableStyle = ActiveWorkbook.DefaultTableStyle
                End With
            End If
        
        Next loTable
        
    Next wsCurrentSheet

    ' Clean up
    Set loTable = Nothing
    Set wsCurrentSheet = Nothing
    Set qtQueryTable = Nothing

End Sub