Use cell value (date) to import text file

Question

I'm trying to import a text file into an Excel workbook using a cell value to determine which text file to import. The directory that houses the text file will have other text files with the date prepended to the filename. (e.g. "2018-03-06-FILENAME.txt")

I want the user to be able to enter a date into a predefined cell, and be able to run a macro that checks the value of that cell and then imports the file that matches that value. I think I'm running into a problem with my code where it sees the date code, and not the value that the filenames have. Not sure how to get around that? (The user must be able to enter the date in a human-friendly way, not "43164".)

Sub Import()

Dim currentPath As String
Dim newPath As String
Dim currentFile As String
Dim currentDate As String

currentPath = "\\network\folder\"
newPath = "\\network\folder\Processed\"

' Get the first file

    currentDate = Sheet1.Range("A1").Value
    currentFile = Dir(currentPath & currentDate & "*.txt")
    Do While currentFile <> ""

' Clear previous data

    Sheet2.Activate
    ActiveSheet.UsedRange.Clear
    Range("A1").Select

' Process text file

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & currentPath & currentFile, _
        Destination:=Range("$A$1"))
        .Name = "Data"
        .FieldNames = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileColumnDataTypes = Array(3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With

    ActiveSheet.QueryTables(1).Delete

' Move file into Processed folder

    Name currentPath & currentFile As newPath & currentFile

' Get the next file

    currentFile = Dir
    Loop

End Sub

Answer 1

Try

Format$(Sheet1.Range("A1").Value,"yyyy-mm-dd")

Although, according to your example should there be a -FILENAME bit in your code?

You want

2018-03-06-FILENAME.txt

But give only:

currentPath & currentDate & ".txt"

So, need currentPath & "-" & currentDate & "-" & FILENAME & ".txt"

Assuming FILENAME is a variable.

Answer 2

currentDate = Sheet1.Range("A1").Value would indeed give the sequential date Excel uses to store dates I.E.(43261)

Use currentDate = Format(Sheet1.Range("A1").text, "YYYY-MM-DD") & "FILENAME.txt" instead.

You can also check what is stored in the variable by using debug.print currentdate, then using Ctrl+G for console to show printed values after macro executes.

I strongly recommend using instr function to find the dates within the filename instead of comparing them directly, unless your file dates aren't unique. Also remember to use TextCompare mode for none-case sensitive comparisons.

Example: If instr(1, CURRENT FILENAME STRING, currentDate, vbTextCompare) then 'if filename is found then code goes here. end if