Importing data from .txt (what if .txt file no longer exists?)

[Sam Hamels]

In Excel 2016, I import data from .txt files by using the "From Text" under the "Data" tab in the top ribbon.

The .txt files consist of a header (e.g. "Apples") and a list of entries. Opening a .txt file in Notepad would look something like this:

Apples
5
4
8
9
...

The .txt files are automatically generated by another software program that I use.

Each dataset imported from a .txt file is put in a separate worksheet in my Excel file.

If the .txt files are updated, changing the data in the files, I can simply click "Refresh All" and the updated data is correctly imported into my Excel file.

However, for some .txt files, they may sometimes be deleted temporarily, and automatically created again at a later time.
During times when some .txt files are temporarily deleted, I still need to be able to "Refresh All" in Excel, updating the data from the .txt files that do still exist.

When this happens, Excel prompts an error message saying "Could not find file", which is logical since the .txt file it is looking for to update the values is indeed gone (albeit temporarily).

My question: If Excel, upon "refreshing" all the imported data, cannot find a particular .txt file because it has been deleted, I want it to simply delete the data imported from the previously existing .txt file and not prompt me an error message. Is this possible and if so, how?


Many thanks!
Sam

 

[Sam Hamels]

For my purposes, a macro with some code that changes all connections from .txt to .csv would be preferable
Problem is I don't have the faintest idea about which code to use in order to do that operation for all my connections

 

[John_w]

The issue I have now is that I have changed to .csv files instead of .txt files (the file names themselves are still the same).
However, all the 'Connections' relating to the many .txt files that I originally imported in the different sheets of my Excel file are still there, looking for .txt files. I have attached a screenshot to show what I mean.
Is there any way I can quickly edit all my 'Connections' so that they look for a file in the same folder and with the same name but with .csv in the end instead of .txt?

Sam, try this macro:

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

 

[Sam Hamels]

Hi John,


Thanks for this code. It works perfectly and really helps me out! Made my day.
Unfortunately it introduces a new issue. After running your macro and then hitting 'Refresh All', my .csv files are now each imported to their respective sheets, but now everything is in a single column (instead of the delimiters being used to put everything into separate columns).

What would I need to add at the end of your code, to re-establish the fact that all connections have the property of being imported into separate columns according to their delimiters?

Screenshots attached for clarification, with (A) how it is supposed to look and (B) how it looks now, after running your code.

(A)

(B)

 

[Sam Hamels]

To be clear, I need to switch between importing .txt files and .csv files every once and a while.
I've already edited a copy of your "Change_Connections" macro, swapping .txt and .csv, so it works in the opposite direction (changing all connections from .csv's back to .txt's).
In both directions (both after moving from .txt to .csv and after moving from .csv to .txt), I then encounter the issue that, upon hitting 'Refresh All', the delimiter is no longer recognized and and each file is imported into (only) the first column of its respective sheet. In the case of .txt files, the delimiter is 'Tabs', while in the case of .csv files, the delimiter is 'Comma'

 

[John_w]

I have seen this problem before of Excel losing the query's text connection properties when VBA changes its Connection string - I think it reverts to the default properties, which includes no delimiters.

Try this new macro which should work for .txt to .csv and .csv to .txt, setting the delimiters as appropriate.

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

 

[Sam Hamels]

Thanks John, this works!
You're a hero man.