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

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
49
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:

Code:
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)
y1gULPS.png



(Screenshot B)
g6u4E2T.png
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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:

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top