Issue managing ListObjects and Queries

[devon_a_b]

--EDIT--

I believe I found the issue, it seems that I was attempting to unlist the query after deleting without referencing the proper worksheet for the ListObject. Must have got it from some example code I looked at and I didn't fully understand the functionality. However, if anyone has tips to make this a little cleaner I would greatly appreciate it.


I'm fairly new to coding in VBA but I have some experience with Python and very limited C++ experience, so I understand some basic concepts. I tried building this macro to manage a workbook that allows me to view specific trailers, gathering the data from a spreadsheet that's exported from a Microsoft Access Database. It works well, if I run it about 3 times...but for the first couple of times I get a 'subscript out of range' error.


The following is my code. I know it's probably not the prettiest. I tried piecing it together with google-fu and recording/reviewing macros. Any assistance is appreciated.

Sub Setup()

Dim q1_exists As Boolean
Dim q2_exists As Boolean
Dim old_exists As Boolean
Dim table_ As String




    ' Cycle through worksheets to check for "old"
    ' If "old" exists delete it
    
    For o = 1 To Worksheets.Count
        If Worksheets(o).Name = "old" Then
        old_exists = True
        End If
    Next o
    
    If old_exists Then
        Sheets("old").Delete
    End If

    ' Cycle through worksheets to check for "current"
    ' If "current" exists change name to "old"
        
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = "current" Then
        Sheets("current").Name = "old"
        End If
    Next i
    
    ' Check if query "rec_" exists
    
    For q2 = 1 To ActiveWorkbook.Queries.Count
        If ActiveWorkbook.Queries(q2).Name = "rec_" Then
            q2_exists = True
            
        End If
    Next q2
    
    ' If "rec_" exists delete it
    
    If q2_exists Then
        ActiveWorkbook.Queries("rec_").Delete
        ActiveSheet.ListObjects("rec_").Unlist
        'ActiveSheet.ListObjects("rec_").Name = "old"
        
        
        
    End If
    
    


' Refresh initial query
'
    ActiveWorkbook.Connections("Query - z").Refresh


' Create new worksheet and export table data to new worksheet
    
    ' Check if query "rec" already exits
    
    For q = 1 To ActiveWorkbook.Queries.Count
        If ActiveWorkbook.Queries(q).Name = "rec" Then
        
            q1_exists = True
            
        End If
    Next q
        

        
    ' If "rec" does not exist create query named rec
    
    If Not q1_exists Then
        
        ActiveWorkbook.Queries.Add Name:="rec", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""z""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Trailer"", type text}, {""SCAC"", type text}, {""Status"", Int64.Type}, {""Text20"", type text}, {""Comment"", type text}, {""Update Time"", type datetime}, {""Yard Loc"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        ActiveWorkbook.Worksheets.Add
        
        ActiveSheet.Name = "current"
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=rec;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [rec]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "rec"
        .Refresh BackgroundQuery:=False
    End With
    
    table_ = "rec"
    
    
        
            
    ' If "rec" exists create query named rec_
            
    Else
        
        ActiveWorkbook.Queries.Add Name:="rec_", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""z""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Trailer"", type text}, {""SCAC"", type text}, {""Status"", Int64.Type}, {""Text20"", type text}, {""Comment"", type text}, {""Update Time"", type datetime}, {""Yard Loc"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        ActiveWorkbook.Worksheets.Add
        
        ActiveSheet.Name = "current"
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=rec_;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [rec_]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "rec_"
        .Refresh BackgroundQuery:=False
    End With
        
    End If
    
        
    
    
   
    








End Sub