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

 

[Norie]

Sam

Where do all these files reside?

 

[ranman256]

why would you delete the data you imported yesterday? I thought you were keeping it all in separate sheets.
This does not solve the error message.

but you can try,
instead of clicking the REFRESH ALL button, do it with a macro and turn off the warnings:

sub MyRefresh()
Application.DisplayAlerts = False
         ActiveWorkbook.RefreshAll
Application.DisplayAlerts = true
end sub

 

[Sam Hamels]

The point of my Excel file is to collect data from many different .txt files and do some analysis on them.
The .txt files are generated by another specialized piece of software that I use. Whenever I use that software, it generates the .txt files as output.
When I use that software throughout the day, sometimes the output does not include a particular .txt file, but I still want my Excel file (which analyzes the collection of .txt files) to work properly.

I am very unfamiliar with VBA code.
The code you suggested looks promising, but doesn't yet seem to include the behavior I want, namely: "If you cannot find the .txt file for a particular data input, simply delete the content of all the cells in the worksheet where you would normally paste the updated values of the .txt file."

Any suggestions on how I might translate that into code?


Many thanks!

 

[Sam Hamels]

Or, alternatively:

"If .txt file cannot be found, create empty .txt file with the same name and load data from that .txt file instead"

I guess that should result in the same situation: the worksheet where the updated values would otherwise be shown is now simply blank (which is what I want).

No idea how to write that in VBA though...

 

[Sam Hamels]

The .txt files are all located in a single folder, if that's what you mean.

 

[John_w]

This can be done with a custom VBA class which handles the BeforeRefresh event of the QueryTable object and checks whether the file associated with the QueryTable exists. If the file doesn't exist, the handler clears the existing data and cancels the refresh.

Three pieces of code are needed in the VBA project.

1. Add a class module and rename it from the default Class1 to CQueryTable and put this code in it:

Option Explicit

Private WithEvents mQueryTable As QueryTable


Public Sub InitHandler(QT As QueryTable)
    Set mQueryTable = QT
End Sub


Private Sub mQueryTable_BeforeRefresh(Cancel As Boolean)
    
    Dim p As Long
    
    Debug.Print "BeforeRefresh: " & mQueryTable.Destination.Worksheet.Name; " "; mQueryTable.Destination.Address; " "; mQueryTable.ResultRange.Address
    
    'If the text file associated with this QueryTable doesn't exist then clear the existing data and cancel the refresh
        
    p = InStr(mQueryTable.Connection, ";")
    If p > 0 Then
        If Dir(Mid(mQueryTable.Connection, p + 1)) = vbNullString Then
            mQueryTable.ResultRange.Cells.ClearContents
            Cancel = True
        End If
    End If
    
End Sub


Private Sub mQueryTable_AfterRefresh(ByVal Success As Boolean)
    'Info only - not used
    Debug.Print "AfterRefresh: " & Success; " "; mQueryTable.Destination.Worksheet.Name; " "; mQueryTable.Destination.Address; " "; mQueryTable.ResultRange.Address
End Sub

Important - you must rename the class module as CQueryTable. Do this in the class module's properties window (VBA editor: View menu -> Properties Window) and change the (Name) property value.

2. Add a standard module (Module1) and put this code in it:

Option Explicit

Dim QueryTableEventObjects As Collection


Public Sub Create_QueryTable_Event_Objects()

    Dim ws As Worksheet
    Dim QT As QueryTable
    Dim pQueryTable As CQueryTable
        
    If QueryTableEventObjects Is Nothing Then Set QueryTableEventObjects = New Collection
    
    For Each ws In ThisWorkbook.Worksheets
        For Each QT In ws.QueryTables
            Set pQueryTable = New CQueryTable
            pQueryTable.InitHandler QT
            QueryTableEventObjects.Add pQueryTable
        Next
    Next
    
End Sub

3. Add the following code to the ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    Create_QueryTable_Event_Objects
End Sub

Save the workbook as a macro-enabled workbook (.xlsm) or binary workbook (.xlsb) and close and reopen it. Test by deleting/renaming any of the text files and refresh all or one of the queries via the Excel UI.

 

[Sam Hamels]

Dear John,


This works exactly the way I want it to, thank you so much!


Kind regards,
Sam

 

[Sam Hamels]

Hi John,


I have a small follow-up question relating to this old post.

I have an Excel file with several sheets. In each sheet, I have imported a .txt file.
If I look into the 'Properties' of a particular 'Connection', I can see that it is looking for a particular .txt file in a particular folder.
If I were to change the values in the .txt file itself, save it, and then click 'Refresh All' in Excel, the correct (new) values would be pulled into Excel (in the respective sheet where that particular .txt was originally imported). The code you suggested before still works flawlessly.

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?


Kind regards,
Sam

 

[sandy666]

try PowerQuery (Get&Transform) with From Folder option