How to automatically update all pathnames for .txt files imported into an Excel file?

[Sam Hamels]

I use "Data" -> "From Text" (as shown in the figure below) to import data from many different .txt files into my Excel file.
All these .txt files are in a single folder on my computer. Let's say this folder has the pathname "C:\Users\sam\folder1"
If the data in the .txt files is changed, I can simply click "Refresh All" (also shown in the figure below) to update the data in my Excel file.

Elsewhere on my computer, I have other folders with similar .txt files (same file names but different data values in the files themselves).
I would like to be able to simply make a copy of my current Excel file and adjust all the pathnames referring to the different .txt files, so that they refer to the files in another folder. For example, changing the pathname for a particular textfile (named "textfile1") from "C:\Users\sam\folder1\textfile1.txt" to "C:\Users\sam\folder2\textfile1.txt", and doing this for all the text files that are imported into my Excel file.

Ideally, I would simply have a cell somewhere in my Excel file in which I fill in the portion of the pathname up to the filename itself (in my example the value in this cell would be "C:\Users\sam\folder1" if I want all the text files from this folder to be pulled into my Excel file. I could then very easily adjust the value in this cell to automatically pull the data from a bunch of .txt files in another folder into my Excel file.

If anyone knows how to do this I would be eternally grateful!
I have (almost) no knowledge of VBA code, which I assume will be part of the solution. So if someone can suggest some code I can hopefully just copy-paste it

Thanks!

 

[PatOBrien198]

This is complicated by the fact that your multiple text import queries could have different setting for things like delimiter, fixed width, starting row to import...

If they are all simple text file - import to single column then something like the following might work...

Sub QueryNewLocation()
Dim ws As Worksheet
Dim wb As Workbook
Dim qt As QueryTable
Dim sFile As String
Dim sNewFolder As String
Set wb = ActiveWorkbook
sNewFolder = Range("A1").Value  ' change this to be where the new folder is entered

' if the folder name doesn't end with a '\', then add one...
If Right(sNewFolder, 1) <> "\" Then
    sNewFolder = sNewFolder & "\"
End If
'loop thru all worksheets
For Each ws In wb.Worksheets
    'loop thru all queries on the worksheet
    For Each qt In ws.QueryTables
        sFile = Mid(qt.Connection, InStrRev(qt.Connection, "\") + 1)
        'Check to see if the new folder/ file is found - exit if not found
        If Dir(sNewFolder & sFile) = "" Then
            MsgBox sNewFolder & sFile & " NOT FOUND.." & vbCrLf & vbCrLf & "Exiting..."
            Exit Sub
        End If
        ' change the location of the file being importe
        qt.Connection = "TEXT;" & sNewFolder & sFile
        'without the following line, a dialog box is opened and you have to re-select the file to be imported - however...
        ' however, the original import options might be maintained - but still a pain.
        qt.TextFilePromptOnRefresh = False
        qt.Refresh
    Next
Next
MsgBox "Done", vbExclamation
End Sub

 

[John_w]

A more basic version, without the file checking and data refresh. This expects the new folder path to be specified in cell B2 of the first sheet.

Public Sub Change_QueryTables_Connection_Folder()

    Dim ws As Worksheet
    Dim QT As QueryTable
    Dim parts As Variant, fileName As String, newFolderPath As String
    
    newFolderPath = ThisWorkbook.Worksheets(1).Range("B2").Value
    
    If Right(newFolderPath, 1) <> "\" Then newFolderPath = newFolderPath & "\"
    
    For Each ws In ThisWorkbook.Worksheets
        For Each QT In ws.QueryTables
            parts = Split(QT.Connection, ";")
            fileName = Mid(QT.Connection, InStrRev(QT.Connection, "\") + 1)
            QT.Connection = parts(0) & ";" & newFolderPath & fileName
        Next
    Next

End Sub

 

[Sam Hamels]

Thanks a lot!
John's macro works flawlessly.

I only have one remaining issue, which is the fact that the .txt files are now imported with different settings.
If some code could be included in the macro to also take account of my preferred settings, that would be awesome.
I use the same setting for every .txt file I import.
To guide you through these, I show my importing process step by step with screenshots below.


Step 1: I select "Delimited" (this is selected by default), check the box "My data has headers" and hit "Next >"

Step 2: In the "Delimiters" section, I uncheck the "Tab" box, check the "Semicolon" box and hit "Next >"

Step 3: In the "Column data format" section I select General (this is selected by default) and hit "Finish"


https://image.ibb.co/kmwhPx/step3.png


Step 4: I select "Existing Worksheet" value "=$A$1" (these are selected by default) and hit "Properties..."


https://image.ibb.co/cc48xH/step4.png



Step 5: I uncheck the box "Prompt for file name on refresh" and hit OK


https://image.ibb.co/hAdqBc/step5.png


Hopefully this is a clear enough explanation of which settings I use, so it can be included in the macro code.


Thanks!
Sam

 

[John_w]

I only have one remaining issue, which is the fact that the .txt files are now imported with different settings.

My code doesn't (or shouldn't) change the import settings. If you're saying that you previously imported text files which had comma separators, but now the new text files have semi-colon separators then simply open a new workbook and do a manual import, following the Text Import Wizard steps, for each .txt file. This will create the text file query settings, embedded in the sheet(s).

Then add my code to the workbook and save it as .xlsm or .xlsb. Runnng my macro will allow you to change the folder location (specified in cell B2 of the first sheet) of the .txt files and refresh (re-import) the data.

 

[Sam Hamels]

The .txt files always have semi-colon seperators and this does not change if they are in another folder.


To illustrate my settings-problem, let's say I create a brand new Excel file and do the following:

I copy the same .txt file to two different folders, to test the functionality of your code (which is to change the pathname of the .txt file that is being imported into Excel to a different folder). I just change some of the first datapoints in one of the two files (leaving the seperators untouched) in order to be able to see the values changing in my Excel file when I use your code.

In my Excel file, I first import one of the .txt files as described in my previous post (basically telling the Text Import Wizard that "my data has headers" and that the seperator is a semi-colon).

Then I change the value in cell "B2" of the first sheet to the folder in which I have put the copy of the .txt file with the slightly altered datapoints.
Then I run the macro with your code effectively changing the pathname of the imported .txt file to the new pathname, and hit the "Refresh all" button.
Now I can see that the pathname of the imported .txt file is indeed correctly updated (i.e. your code works) because the first few datapoints from the .txt file that I see in Excel are now the ones of the copy of the .txt file in the different folder.

However, all the data is now in a single column (indicating that the the "the seperator is a semi-colon setting" was lost in the process)...

So I guess that, after using your code, the settings go back to their defaults? I guess the code will somehow have to include at least to re-apply the "seperators are semi-colons" setting which I selected in the Text Import Wizard.

I unfortunately have no idea how to translate that to VBA code :-/

 

[Sam Hamels]

Never mind! I have solved my issue by changing the fact that my .txt files always have tabs as separators instead of semi-colons.
Since this type is separator is apparently the default value in the Text Import Wizard, this avoids the need to change the separator setting to semi-colon every time the .txt file is uploaded from a different folder. Now everything works the way I intended.

I only have one remaining question for John.

Your code changes the pathname for every .txt file I import into my Excel file.
This is perfect for 90% of the .txt files I import into my Excel file.
However, for a small amount of .txt files, I would like to change the pathname to a slightly different folder.

What would your code look like if it were to target a specific .txt file import and change only the pathname for that one?
I envisage pasting that "txt-file specific pathname adjuster code" behind the "adjust all .txt files pathnames" code that you already provided.
The "specific txt file" part would then ideally refer to a different cell (e.g. B3 instead of B2, in your example), where I enter the pathname that the specific .txt file should be changed to.

 

[John_w]

I have reproduced the issue with every record being imported into a single column when the data fields are separated by semi-colons and my macro is run and the data is refreshed. You are correct that the text import query is reverting to its default settings, when the QT.Connection line is executed, despite the 'Save Query Definition' box being ticked. The fix is to reset TextFileSemicolonDelimiter to True, as shown in the code below. This means you can keep the semi-colon separators in your .txt files.

To specify a different folder (B3) for a specific .txt file, whilst still using B2 for other .txt files try this:

Public Sub Change_QueryTables_Connection_Folder2()

    Dim ws As Worksheet
    Dim QT As QueryTable
    Dim parts As Variant, fileName As String, newFolderPath As String, newFolderPath2 As String
    
    newFolderPath = ThisWorkbook.Worksheets(1).Range("B2").Value
    newFolderPath2 = ThisWorkbook.Worksheets(1).Range("B3").Value
    
    If Right(newFolderPath, 1) <> "\" Then newFolderPath = newFolderPath & "\"
    If Right(newFolderPath2, 1) <> "\" Then newFolderPath2 = newFolderPath2 & "\"

    For Each ws In ThisWorkbook.Worksheets
        For Each QT In ws.QueryTables
            parts = Split(QT.Connection, ";")
            fileName = Mid(QT.Connection, InStrRev(QT.Connection, "\") + 1)
            If fileName = "specific file.txt" Then
                QT.Connection = parts(0) & ";" & newFolderPath2 & fileName
            Else
                QT.Connection = parts(0) & ";" & newFolderPath & fileName
            End If
            QT.TextFileSemicolonDelimiter = True
        Next
    Next

End Sub

 

[Sam Hamels]

Thanks for all the kind help John, I have now achieved 100% of what I wanted!
Makes my day!

 

[Sam Hamels]

Dear John,


Since you suggested the code in your last post in April, I have enjoyed tremendously the efficient refreshing of my text files into Excel!
Thanks again for that. It was really helpful and I have been using it on a daily basis ever since!

I now face a slightly different variation of the same problem for which I would like to ask your advice.
The basic situation is the same: I have a bunch of text files with data in them. I want to view these within Excel in order to perform calculations on the data. I therefore import the text files into Excel by using the "From Text" button under the "Data" tab. If the data within one of the text files changes, I can click 'Refresh All' under the Data tab in order to update the imported data. I use a separate worksheet for each imported text file. So far so good.

Originally, all my text files were in a single folder, and the name (or location) of this folder could sometimes change. When that happened, I needed to update the 'pathnames' of all the (queries of the) imported text files efficiently (this was my original problem back in April). Your suggested VBA code worked perfectly for exactly that. It gives me a cell within the Excel file where I can simply edit the pathname for all the text files at once. The text files were all in the same folder anyway.
In your latest post, you updated the code with an additional feature to deal with a specific text file (with a specific name) needing a slightly different pathname (because it was in a slightly different folder). For that specific file, you created a second specific reference to another cell (that would contain the separate pathname for that specific text file) in your code.

Now, I again have a collection of text files. Let's say they are called 'text file 1.txt', 'text file 2.txt and 'text file 3.txt', but now I would like to have separate cells showing (and allowing me to edit) the pathname of each individual text file that I have imported into Excel. In other words, I don't want to update all text files with a single pathname that is written in a single cell. Rather, I want to have separate cells with pathnames in them that I can edit, one for each text file (so three in my example). Whenever necessary, I should be able to edit those cells with updated pathnames, and then use a single macro to refresh the pathnames of all text files, given the new pathnames in each 'pathname cell'.

One caveat is that the text files are often identical in name. So this can happen:
'Worksheet 1' shows the imported data from 'text file 1.txt' located in one folder
'Worksheet 2' shows the imported data from 'text file 1.txt' located in another folder
'Worksheet 3' shows the imported data from 'text file 1.txt' located in yet another folder
And so forth. This means that the code cannot use a differentiation in the names of the text files in order to differentiate which 'data import' (also called a 'connection' in Excel) should be updated with which pathname. Rather, I differentiate the different iterations of 'text file 1.txt' by having them in separate worksheets (with different names) within my Excel file. My ideal end-result is therefore something that looks like this:

Any idea on how to edit your original code in order to do that?


Thanks in advance!
Kind regards,
Sam