How can I change dynamicly the file name in the file path?

Question

I want to import multiple TXT files into excel (into the same sheet - every file has only 6 rows). How can I do the files path change in every cycle (I will take it in a for cycle)?

Sub openfile()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\HarrsionDavid\Desktop\\source\customer.txt", _
        Destination:=Range("A1"))
        .Name = "customer.txt"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1250
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 9, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1:C3").Selection
    Selection.Delete Shift:=x1Up
    Range("A1:C3").Selection
    Selection.Delete Shift:=x1Up
End Sub

In this question (Import multiple text files into excel) there is an answer, but I need to change the file name only in the path, because the file names will be get from an other excel column. On Google and Stackoveflow I have no found anything.

Just wondering - I have noticed this `x1Up` problem in quite a lot of questions in StackOverflow. Where is it coming from initially? (To see what I mean, search the code for `x1Up`.) — Vityata, Jun 06 '18 at 09:47
@Olly - Thanks. I see this too often here and there and I really don't know where is it coming from? https://stackoverflow.com/questions/35513779/run-time-error-1004 and https://stackoverflow.com/questions/36102903/run-time-error-1004-excel-2013 — Vityata, Jun 06 '18 at 13:10

Olly · Accepted Answer · 2018-06-06T13:05:35.460

You can use a string variable for a file name, and append that to the hard coded filepath:

Sub openfile(ByVal sFileName As String)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\HarrsionDavid\Desktop\\source\" & sFileName, _
        Destination:=Range("A1"))
        .Name = "customer.txt"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1250
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 9, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1:C3").Selection
    Selection.Delete Shift:=xlUp
    Range("A1:C3").Selection
    Selection.Delete Shift:=xlUp
End Sub

Then call by passing the filename:

Sub TestOpenFile()
    openfile "customer.txt"
End Sub

score 1 · Answer 2 · answered Jun 06 '18 at 09:37

Write the paths in Range("A1:A5") and loop through them, passing them as a parameter to the Sub OpenFile.
Then in your code change the C:\Users\HarrsionDavid\Desktop\\source to the passed parameter.
Try to make your code better, by trying to avoid Select and Activate - How to avoid using Select in Excel VBA:

Option Explicit

Public Sub TestMe()

    Dim paths As Variant        
    paths = Range("A1:A5")
    Dim singlePath As Variant

    For Each singlePath In paths
        OpenFile (singlePath)
    Next singlePath

End Sub

Public Sub OpenFile(singlePath As String)

    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & singlePath, Destination:=Range("A1"))
        'more code...
    End With

End Sub

score 0 · Answer 3 · answered Jun 06 '18 at 09:48

Insert another code to create a basic loop, and change one line in your current code as per below:

Public Path As String
Public rng As Range
Sub Loop_Through_Files()
'ensure that public path is the first line in this module literally at the very top
'set this as your first set of data
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")
Repeat:
Path = rng.Value
Call openfile
Set rng = rng.Offset(1, 0)
If IsEmpty(rng.Value) Then ' checks if the cell is blank and ends macro, ensure that after the last path there is a blank cell
Else
GoTo Repeat
End If
End Sub

This is your code slightly modified, i replaced your path with the word path.

Sub openfile()
'ensure that public path is the first line in this module literally at the very top
  With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Path _
        , Destination:=Range("A1"))
        .Name = "customer.txt"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1250
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 9, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
  End With

  Range("A1:C3").Selection
  Selection.delete Shift:=x1Up
  Range("A1:C3").Selection
  Selection.delete Shift:=x1Up

End Sub

score 0 · Answer 4 · answered Jun 07 '18 at 07:55

Make a variable which will store the path of the files. If you take it the "opening code" in an if you can open every file what you want (if the filenames are in the first column in excel).

Sub openfile()

    Dim Con As String

    For i = 3 To 400

    Con = "TEXT;" & Cells(1,4).Value & "\" & Cells(i,1).Value

    With ActiveSheet.QueryTables.Add(Connection:= _
        Con _
        ,Destination:=Cells(i,2)
        .Name = Cells(i,1).Value
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    Next i

End Sub

How can I change dynamicly the file name in the file path?

4 Answers4