Execute macro with lastest .txt file

[JTL9161]

I have a macro that first opens a directory window where I store daily .txt files. I double click on the latest file and the marco imports it into Excel and continues. Been trying to find code that would at the start of the macro open the directory and import the latest .txt file.

Here's what I have now:

Dim FName As String
Dim x As Variant
Dim Path As String
Dim MyFile As String, Cmd As String

ChDrive "L"
ChDir "L:\1A-PHARMACY PB\DAILY REPORTS\01 - JANUARY\FSPRIME"
FName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If FName = "False" Then Exit Sub
ChDir "L:\1A-PHARMACY PB\DAILY REPORTS"
ActiveWorkbook.Worksheets(1).Activate

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FName, _
Destination:=Range("$A$1"))
.Name = "Sheet1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Thank you,
James

 

[Norie]

Do you mean that you want the code to find the latest file without user intervention?

If so how can the file be identified?

By it's name? By it's last modified date? Date created?

 

[JTL9161]

Yes. Find the latest file (by last modified date) and import it.

 

[Norie]

You could try using this function.

Function GetLastModified(objFolder As Object, Optional strExt = "*") As String
Dim objFile As Object
Dim objLastFile As Object

    For Each objFile In objFolder.Files
    
        If LCase(objFile.Name) Like LCase("*." & strExt) Then
            If objLastFile Is Nothing Then
                Set objLastFile = objFile
            Else
                If objLastFile.DateLastModified < objFile.DateLastModified Then
                    Set objLastFile = objFile
                End If
            End If
        End If
        
    Next objFile
    
    GetLastModified = objLastFile.Path
    
End Function

It requires you to pass it a Folder object and you have the option to specify a file extension, e.g. txt.

So you could use it something like this.

Dim FSO As Object
Dim objFld As Object
Dim strPath As String
Dim FName As String

    strPath = "L:\1A-PHARMACY PB\DAILY REPORTS\01 - JANUARY\FSPRIME"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set objFld = FSO.GetFolder(strPath, "txt")
    
    FName = GetLastModified(objFld)


    ' rest of code to import text file.

 

[JTL9161]

sorry..you lost me a little. Do I add both above codes to the start of my macro? When I do I get "expected end sub" error after my DIM's.

 

[Norie]

No, the first code is a stand-alone function that is separate from your code - it could even go in it's own module.

You would then replace this part of the original code,

ChDrive "L"
ChDir "L:\1A-PHARMACY PB\DAILY REPORTS\01 - JANUARY\FSPRIME"
FName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If FName = "False" Then Exit Sub
ChDir "L:\1A-PHARMACY PB\DAILY REPORTS"

with this code.

Dim FSO As Object
Dim objFld As Object
Dim strPath As String
Dim FName As String

    strPath = "L:\1A-PHARMACY PB\DAILY REPORTS\01 - JANUARY\FSPRIME"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set objFld = FSO.GetFolder(strPath, "txt")
    
    FName = GetLastModified(objFld)


    ' rest of code to import text file.

 

[JTL9161]

I have never used a FUNCTION before. I've added the code but how do I start it or access it to start running the rest of macro?

 

[Norie]

James

You don't run the function

You run the code you have with the adjustments I suggested

If you do that the function should be called and return the latest file in the specified directory.

 

[JTL9161]

Just got around to trying this. I made the revision as you said and now I am getting a Run Time Error 450 "wrong number of arguments or invalid property assignment" for this line of code: Set objFld = FSO.GetFolder(strPath, "txt")

 

[Norie]

Oops, that's a typo.

Should be this, no idea where the ',"txt"' part came from

    Set objFld = FSO.GetFolder(strPath)