Execute macro with lastest .txt file

JTL9161 · Jan 24, 2019

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

JTL9161 · Jan 29, 2019

Me again. Now I am getting

Compile Error; Sub or Function not defined

[FONT=&quot]FName = GetLastModified(objFld)

[/FONT]

Norie · Jan 29, 2019

Here's the entire code, I've called the sub with the original code OriginalCode because I've no idea what you called it.

Code:

Option Explicit

Sub OriginalCode()
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)
    
    FName = GetLastModified(objFld, "txt")

    ' rest of code to import text file.
    
    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

End Sub


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

JTL9161 · Jan 30, 2019

Our saga continues.

Now I get error: Complie Error: Only comments may appear after End Sub, End Function, or End Property.

I highlights in yellow this code: Function GetLastModified(objFolder As Object, Optional strExt = "*") As String
and highlights the (1) in ActiveWorkbook.Worksheets(1).Activate in blue.

Thanks.

Norie · Jan 30, 2019

Eh, this code is not in the code I posted i post #12.

Code:

ActiveWorkbook.Worksheets(1).Activate

Try putting the code from that post in a new module and then running the sub named OriginalCode.

JTL9161 · Jan 30, 2019

ok so that work. So I guess my (hopefully final) question is after the END FUNCTION where do I put the rest of my macro? When I add it in after the END FUNCTION I get the same Only Comments error as above.

Norie · Jan 30, 2019

I'm confused, what other code do you have apart from that from the original post?

JTL9161 · Jan 30, 2019

Once your code completes and it imports the file that is chosen by your code I have the additional steps in the rest of the macro to format the text and update it. I do this daily. Your part was just to get the file into the spreadsheet which I did manually each time. After its in Excel I run the rest of the macro.

Norie · Jan 30, 2019

James

Is the code to format the imported data in a separate sub or was it part of the original code you posted?

JTL9161 · Jan 30, 2019

It's part of the original code. This was a all in one macro of importing (manually) and formatting the data and then importing it into a month to date spreadsheet. My code continues after

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

Norie · Jan 30, 2019

You should be able to put the formatting code after the code you posted above but before the End Sub in the code I posted.

Rich (BB code):

Option Explicit

Sub OriginalCode()
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)
    
    FName = GetLastModified(objFld, "txt")

    ' rest of code to import text file.
    
    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


' FORMATTING CODE GOES HERE

End Sub


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

Execute macro with lastest .txt file

JTL9161

Well-known Member

JTL9161

Well-known Member

Excel Facts

Norie

Well-known Member

JTL9161

Well-known Member

Norie

Well-known Member

JTL9161

Well-known Member

Norie

Well-known Member

JTL9161

Well-known Member

Norie

Well-known Member

JTL9161

Well-known Member

Norie

Well-known Member

Similar threads

Forum statistics

Share this page

Execute macro with lastest .txt file

Well-known Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock