How to edit all queries/connections from one delimiter to another?

[Sam Hamels]

In my Excel file, I have imported many txt files with tab delimiters manually (Data tab -> Import data -> From Txt).
Each txt file is imported into its own separate sheet. The delimiters are recognized and the data within each txt file is neatly imported into separate columns, as shown in the screenshot below (screenshot A).

I have received the following code from John_W on this forum, to edit all my connections from txt files to csv files.
I needed this code because I sometimes need to switch between txt's and csv's being imported (filenames and pathnames remain the same for each connection).

The problem is that, after switching from txt's to csv's by running this code, the files are now imported into a single column.
I.e. the delimiters are no longer recognized and used to separate the data into columns (see screenshot B)

What would a macro look like that changes the delimiter settings for every connection/query that has previously been set up?
I.e. changing it from Tab delimiters (current setting, because txt with tab delimiters files were originally imported) to Comma delimiters.
I think such a macro might have to use the "TextConnection.TextFileTabDelimiter" and "TextConnection.TextFileCommaDelimiter" properties, but I've played around with those and can't get any code I write myself to work...

Thanks!


Code I got from John_W:

Public Sub Change_Connections()

    Dim wbConnection As WorkbookConnection
    Dim p As Long
    
    For Each wbConnection In ThisWorkbook.Connections
        If wbConnection.Type = xlConnectionTypeTEXT Then
            p = InStrRev(wbConnection.TextConnection.Connection, ".txt", -1, vbTextCompare)
            If p > 0 Then wbConnection.TextConnection.Connection = Left(wbConnection.TextConnection.Connection, p - 1) & ".csv"
        End If
    Next

End Sub

(Screenshot A)

(Screenshot B)

 

[Sam Hamels]

My question has been answered elsewhere by John_W

https://www.mrexcel.com/forum/excel...-txt-file-no-longer-exists-2.html#post5285194


Here is his code, which works for these purposes:

Public Sub Change_QT_Connections()

    Dim ws As Worksheet, qt As QueryTable
    
    For Each ws In ThisWorkbook.Worksheets
        For Each qt In ws.QueryTables
            If qt.WorkbookConnection.Type = xlConnectionTypeTEXT Then
                With qt.WorkbookConnection.TextConnection
                    If InStrRev(.Connection, ".txt", -1, vbTextCompare) > 0 Then
                        'Change .txt connection to .csv and use comma delimiter
                        .Connection = Left(.Connection, InStrRev(.Connection, ".txt", -1, vbTextCompare) - 1) & ".csv"
                        qt.TextFileCommaDelimiter = True
                    ElseIf InStrRev(.Connection, ".csv", -1, vbTextCompare) > 0 Then
                        'Change .csv connection to .txt and use tab delimiter
                        .Connection = Left(.Connection, InStrRev(.Connection, ".csv", -1, vbTextCompare) - 1) & ".txt"
                        qt.TextFileTabDelimiter = True
                    End If
                End With
            End If
        Next
    Next

End Sub