create new queries, ignore existing

[david763]

Hello, I am trying to create new queries based on the values in a range of cells using VBA.
So far I have got the creation bit working (which I thought would be the hardest part), however I can't seem to skip an iteration if the query already exists.

I have a entries in a range of cells which will grow over time. The contents of the cells hint to the location of the source file for the query and its name (eg "C 2024-01-31" indicates a specific folder to grab the file from and will be the name of the resultant query also). I have managed to create the part of the macro which locates the file and imports it.

However, if I were to re-run the code again I get 'query already exists' error.

Over time as the list of cells grows, I want to create more queries based on new entries. Obviously I don't want to create queries if they already exist.

I should add that there other queries which already exist in the workbook for other purposes which have no relevance to the range of cells - so I need to check based on my range of cells and not vice versa...

Can someone help me with VBA to run through the list of cells, create the query if it doesn't exist, BUT ignore that value if the query already exists and move to the next item in the list..?

Thanks, David

 

[Joe4]

What kind of queries are you talking about exactly? Are you using Power Query?
I think it would be extremely helpful if you post some sample data and your current VBA code so we can get a better picture of exactly what you are doing.

 

[david763]

Hi Joe4, please see code below.

To note, if NONE of the queries presently exist, this code works exactly as intended.

The issue creeps in when one already exists. It still tries to re-create it despite me trying to skip to the next cell entry...

Appreciate any help ironing out this bug...

Sub CreateNewQueries()
    Dim queryNameRange As Range
    Dim cell As Range
    Dim queryName As String
    
    ' Range of cells containing information for required queries 
    Set queryNameRange = Range("folder_extract") ' 
    
    ' Loop through each query name and check if it already exists 
    For Each cell In queryNameRange
        
        f_folder = cell.Value ‘this is the folder containing the target workbook 
        f_dest = "C " & Left(f_folder, 10) ‘this is the name that will be assigned to the resultant query and worksheet 
        f_dest_name = WorksheetFunction.Substitute(WorksheetFunction.Substitute(f_dest, " ", "_"), "-", "_") ‘this is the name of the resultant table 
        f_path = "G:\data archive\" & f_folder & "\target data file.xlsx" ‘ 
        
        queryName = f_dest
        
        If QueryExistsInActiveWorkbook(queryName) Then
            ' If query already exists 
            MsgBox "Query '" & queryName & "' already exists.", vbOKOnly ‘just for testing, will delete when code proven 
        Else
            ' If query doesn't exist, create it 
            MsgBox "Creating query '" & queryName & "'...", vbOKOnly ‘just for testing, will delete when code proven 
        
            ActiveWorkbook.Queries.Add Name:=f_dest, Formula:= _
                "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""" & f_path & """), null, true)," & Chr(13) & "" & Chr(10) & "    page_Sheet = Source{[Item=""page"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(page_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Organi" & _
                "sation"", type text}, {""Number"", type text}, {""Type"", type text}, {""Business Unit"", type text}, {""Name"", type text}, {""Description"", type text}, {""Start Date"", type date}, {""End D" & _
                "ate"", type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
            ActiveWorkbook.Worksheets.Add
            With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
                "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & f_dest & """;Extended Properties=""""" _
                , Destination:=Range("$A$1")).QueryTable
                .CommandType = xlCmdSql
                .CommandText = Array("SELECT * FROM [" & f_dest & "]")
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = f_dest_name
                .Refresh BackgroundQuery:=False
            End With
            Range("" & f_dest_name & "[[#Headers],[Organisation]]").Select
            ActiveSheet.Name = f_dest
            
        End If
    Next cell
    
End Sub

Function QueryExistsInActiveWorkbook(queryName As String) As Boolean
    Dim qt As QueryTable
    For Each qt In ThisWorkbook.Sheets(1).QueryTables
        If qt.Name = queryName Then
            QueryExistsInActiveWorkbook = True
            Exit Function
        End If
    Next qt
    QueryExistsInActiveWorkbook = False
End Function

 

[Joe4]

I have never worked with queries in Excel VBA before, but if your code is not working as expected, I would suggest stepping into the code, then proceeding through it one line at a time using the F8 key. Then you can see what is happening at every step along the way. If you hover over any variable as you are doing that, it will show you the value of that variable at that time.

Often times when you do this, you will see it is not progressing like you think it should, and many times it is because a value is not what you think it is at a particular point in time.