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!

 

[John_w]

Hi Sam,

In the following 2 macros I have used your idea of listing the worksheet names and corresponding folder paths, but I have used a separate sheet named "Index".

The first macro, Create_Index_Sheet, creates the Index sheet, adding it to the workbook if it doesn't exist, with the worksheet names in column A starting at A2 and the corresponding folder path of the query table associated with that sheet in column B starting at B2. Column C contains the destination cell of the query table and is for information only. Run this macro to create the Index sheet for the first time, or to update it from the current queries in the workbook, for example after you have manually added a new text import.

The second macro, Change_QueryTables_Connection_Folder_Using_Index_Sheet, replaces my last macro in post #8 . It loops through the sheet names in column A of the Index sheet and changes the folder path of the current query table associated with that sheet to the folder path specified in column B.

Both macros should work with 1 or more query tables (text import) on each sheet.

Public Sub Create_Index_Sheet()

    Dim indexSheet As Worksheet, r As Long
    Dim ws As Worksheet
    Dim QT As QueryTable
    Dim parts As Variant, folderPath As String
    
    Set indexSheet = GetSheet("Index")
    If indexSheet Is Nothing Then
        Set indexSheet = ThisWorkbook.Worksheets.Add(before:=ThisWorkbook.Worksheets(1))
        indexSheet.Name = "Index"
        indexSheet.Range("A1:C1").Value = Array("Worksheet name", "Path of text file", "Destination cell")
    Else
        With indexSheet
            .Range("A2", .Cells(.Rows.Count, "C").End(xlUp)).ClearContents
        End With
    End If
    
    r = 2
        
    For Each ws In ThisWorkbook.Worksheets
        For Each QT In ws.QueryTables
            parts = Split(QT.Connection, ";")
            folderPath = Left(parts(1), InStrRev(parts(1), "\"))
            indexSheet.Cells(r, "A").Value = ws.Name
            indexSheet.Cells(r, "B").Value = folderPath
            indexSheet.Cells(r, "C").Value = QT.Destination.Address(False, False)
            r = r + 1
        Next
    Next

    MsgBox "Created Index sheet"
    
End Sub


Public Sub Change_QueryTables_Connection_Folder_Using_Index_Sheet()

    Dim indexSheet As Worksheet
    Dim lastRow As Long, r As Long, n As Long
    Dim ws As Worksheet
    Dim QT As QueryTable
    Dim parts As Variant, fileName As String
    
    Set indexSheet = GetSheet("Index")
    If indexSheet Is Nothing Then
        MsgBox "The Index sheet doesn't exist"
        Exit Sub
    End If
   
    With indexSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    r = 2
    While r <= lastRow
        Set ws = ThisWorkbook.Worksheets(indexSheet.Cells(r, "A").Value)
        For n = 1 To ws.QueryTables.Count
            Set QT = ws.QueryTables(n)
            parts = Split(QT.Connection, ";")
            fileName = Mid(QT.Connection, InStrRev(QT.Connection, "\") + 1)
            If Right(indexSheet.Cells(r, "B").Value, 1) <> "\" Then indexSheet.Cells(r, "B").Value = indexSheet.Cells(r, "B").Value & "\"
            QT.Connection = parts(0) & ";" & indexSheet.Cells(r, "B").Value & fileName
            QT.TextFileSemicolonDelimiter = True
            r = r + 1
        Next
    Wend
    
    MsgBox "Updated all queries with folder paths from Index sheet"
    
End Sub


Private Function GetSheet(sheetName As String)
    Set GetSheet = Nothing
    On Error Resume Next
    Set GetSheet = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
End Function

 

[Sam Hamels]

Awesome... works flawlessly.
Thanks!!

 

[Sam Hamels]

Hi John,


Thanks again. I can now freely edit the pathnames in the 'Index Sheet' to update the text files in each other worksheet.
However, two things sometime happen in practice:

1) I want to ignore one or several of the sheets that would normally contain data from a specific text file. This can happen when the folder(s) mentioned in the given pathname on the 'Index sheet' do not exist.

2) Same situation, but instead of the folder(s) in the pathname not existing, the text file is not in it.

If either the folder(s) given in one of the pathnames cannot be found, or the text file that is supposed to be found under that pathname cannot be found, I would like to simply have the existing data in those specific sheets (from an earlier text file) be deleted. Instead, I now just get error messages when I click 'Refresh All' (under the 'Data' tab). These say "Excel cannot find the text file..."


So, for example:

Index sheet contains three pathnames, each for a separate text file (A, B and C), for which the data is imported each in a separate worksheet (A, B and C)
Let's say that I have updated all three pathnames in the Index sheet. For 'A' I have inserted the new pathname (which in fact exists and correctly refers to a folder with text file A in it)
For B and C, I simply delete the pathnames in the Index Sheet, because for B I don't have the folder at the moment, and for C I do have the folder but text file C is not in it at the moment.
When I subsequently run the Change_QueryTables_Connection_Folder_Using_Index_Sheet macro, and then click 'Refresh All', I don't want error messages about B and C, but instead I need Excel to ignore these 'errors' and instead delete the existing data from previous text files B and C that might have been imported to sheets B and C (in order to avoid confusing the data from previous text files B and C that might have been imported being confused for 'updated data').

Any change that this functionality can be easily added with some additional code?


Kind regards,
Sam

 

[Sam Hamels]

Hi John,


Any chance that the Index created by your macro 'Create_Index_Sheet' could also have a column showing the text file that is looked for (imported), for every given sheet?
This way I could not only change the pathname easily (for every sheet / imported text file separately), but also change the names of the text files that are imported themselves.
This can be especially handy if the names of the text files have changed slightly...

I assume that your second macro 'Change_QueryTables_Connection_Folder_Using_Index_Sheet' might also need some changes so that it not only updates the pathnames but also the names of the text files being imported to every sheet.

My own attempts at changing the code in order to achieve this additional functionality have unfortunately not worked out :-/
It would however give a big boost to my work flow...

Your original macro's are already a huge help by the way, I'm using them on a daily basis!




Kind regards,
Sam

 

[John_w]

Hi Sam,

For your second request, updating the Create_Index_Sheet macro to add a column (column C) with the file name of the existing queries, try this:

Public Sub Create_Index_Sheet()

    Dim indexSheet As Worksheet, r As Long
    Dim ws As Worksheet
    Dim QT As QueryTable
    Dim parts As Variant
    
    Set indexSheet = GetSheet("Index")
    If indexSheet Is Nothing Then
        Set indexSheet = ThisWorkbook.Worksheets.Add(before:=ThisWorkbook.Worksheets(1))
        indexSheet.Name = "Index"
        indexSheet.Range("A1:D1").Value = Array("Worksheet name", "Path of text file", "Text file", "Destination cell")
    Else
        With indexSheet
            .Range("A2:D" & .Cells(.Rows.Count, "A").End(xlUp).Row).ClearContents
        End With
    End If
    
    r = 2
        
    For Each ws In ThisWorkbook.Worksheets
        For Each QT In ws.QueryTables
            parts = Split(QT.Connection, ";")
            indexSheet.Cells(r, "A").Value = ws.Name
            indexSheet.Cells(r, "B").Value = Left(parts(1), InStrRev(parts(1), "\"))
            indexSheet.Cells(r, "C").Value = Mid(parts(1), InStrRev(parts(1), "\") + 1)
            indexSheet.Cells(r, "D").Value = QT.Destination.Address(False, False)
            r = r + 1
        Next
    Next

    MsgBox "Created Index sheet from existing text queries"
    
End Sub

Hi John,


Thanks again. I can now freely edit the pathnames in the 'Index Sheet' to update the text files in each other worksheet.
However, two things sometime happen in practice:

1) I want to ignore one or several of the sheets that would normally contain data from a specific text file. This can happen when the folder(s) mentioned in the given pathname on the 'Index sheet' do not exist.

2) Same situation, but instead of the folder(s) in the pathname not existing, the text file is not in it.

If either the folder(s) given in one of the pathnames cannot be found, or the text file that is supposed to be found under that pathname cannot be found, I would like to simply have the existing data in those specific sheets (from an earlier text file) be deleted. Instead, I now just get error messages when I click 'Refresh All' (under the 'Data' tab). These say "Excel cannot find the text file..."


So, for example:

Index sheet contains three pathnames, each for a separate text file (A, B and C), for which the data is imported each in a separate worksheet (A, B and C)
Let's say that I have updated all three pathnames in the Index sheet. For 'A' I have inserted the new pathname (which in fact exists and correctly refers to a folder with text file A in it)
For B and C, I simply delete the pathnames in the Index Sheet, because for B I don't have the folder at the moment, and for C I do have the folder but text file C is not in it at the moment.
When I subsequently run the Change_QueryTables_Connection_Folder_Using_Index_Sheet macro, and then click 'Refresh All', I don't want error messages about B and C, but instead I need Excel to ignore these 'errors' and instead delete the existing data from previous text files B and C that might have been imported to sheets B and C (in order to avoid confusing the data from previous text files B and C that might have been imported being confused for 'updated data').

Any change that this functionality can be easily added with some additional code?


Kind regards,
Sam

For this request, where you want the text data to be either refreshed or deleted, it seems easier to have a macro which either updates each text query's connection setting and refreshes its data, or deletes its current data, according to the settings on the Index sheet, i.e. column A the worksheet name, column B the folder path, and column C the text file name. The Index sheet is the master which shows the source folder and text file for each query worksheet, or what you want them to be, after running the new macro below, Update_and_Refresh_Using_Index_Sheet.

This means the Change_QueryTables_Connection_Folder_Using_Index_Sheet macro is no longer needed, I think, because everything is done by the new macro. You can still do a manual 'Refresh All', as long as you know that all the query connections in the text file query sheets refer to text files which exist, i.e. after running Update_and_Refresh_Using_Index_Sheet.

Public Sub Update_and_Refresh_Using_Index_Sheet()

    Dim indexSheet As Worksheet
    Dim lastRow As Long, r As Long, n As Long
    Dim ws As Worksheet
    Dim QT As QueryTable
    
    Set indexSheet = GetSheet("Index")
    If indexSheet Is Nothing Then
        MsgBox "The Index sheet doesn't exist"
        Exit Sub
    End If
   
    With indexSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    r = 2
    While r <= lastRow
        Set ws = ThisWorkbook.Worksheets(indexSheet.Cells(r, "A").Value)
        For n = 1 To ws.QueryTables.Count
            Set QT = ws.QueryTables(n)
            If Not IsEmpty(indexSheet.Cells(r, "B").Value) And Not IsEmpty(indexSheet.Cells(r, "C").Value) Then
                If Right(indexSheet.Cells(r, "B").Value, 1) <> "\" Then indexSheet.Cells(r, "B").Value = indexSheet.Cells(r, "B").Value & "\"
                QT.Connection = "TEXT;" & indexSheet.Cells(r, "B").Value & indexSheet.Cells(r, "C").Value
                If Dir(indexSheet.Cells(r, "B").Value & indexSheet.Cells(r, "C").Value) <> vbNullString Then
                    QT.Refresh BackgroundQuery:=False
                Else
                    QT.ResultRange.ClearContents
                End If
            End If
            QT.TextFileSemicolonDelimiter = True
            r = r + 1
        Next
    Wend
    
    MsgBox "Updated and refreshed all queries according to Index sheet"
    
End Sub

 

[John_w]

A bug fix to Update_and_Refresh_Using_Index_Sheet - it didn't delete the data when either the path (column B) or file name (column C) or both are blank.

Public Sub Update_and_Refresh_Using_Index_Sheet()

    Dim indexSheet As Worksheet
    Dim lastRow As Long, r As Long, n As Long
    Dim ws As Worksheet
    Dim QT As QueryTable
    
    Set indexSheet = GetSheet("Index")
    If indexSheet Is Nothing Then
        MsgBox "The Index sheet doesn't exist"
        Exit Sub
    End If
   
    With indexSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    r = 2
    While r <= lastRow
        Set ws = ThisWorkbook.Worksheets(indexSheet.Cells(r, "A").Value)
        For n = 1 To ws.QueryTables.Count
            Set QT = ws.QueryTables(n)
            If Not IsEmpty(indexSheet.Cells(r, "B").Value) And Not IsEmpty(indexSheet.Cells(r, "C").Value) Then
                If Right(indexSheet.Cells(r, "B").Value, 1) <> "\" Then indexSheet.Cells(r, "B").Value = indexSheet.Cells(r, "B").Value & "\"
                QT.Connection = "TEXT;" & indexSheet.Cells(r, "B").Value & indexSheet.Cells(r, "C").Value
                If Dir(indexSheet.Cells(r, "B").Value & indexSheet.Cells(r, "C").Value) <> vbNullString Then
                    QT.Refresh BackgroundQuery:=False
                Else
                    QT.ResultRange.ClearContents
                End If
            Else
                QT.ResultRange.ClearContents
            End If
            QT.TextFileSemicolonDelimiter = True
            r = r + 1
        Next
    Wend
    
    MsgBox "Updated and refreshed all queries according to Index sheet"
    
End Sub

 

[Sam Hamels]

You're a life saver John!
This really helps me do my job so much easier.
I can't thank you enough!


Cheers,
Sam

 

[John_w]

Pleased to help! I hope you can learn something from the code.