VBA Open .txt file with a string

[ItalianPlatinum]

I have a VBA code that works like a charm that displays a dialog box and allows a user to select a file. I am trying to enhance it to force the file to be opened based off a set criteria. For example, the file format is AAA_20200529_0513. So a file will be saved daily with AAA_ then the date in YYYYMMDD then _ and time in HHMM. I really only care about the date it was saved and the file name so. AAAA_YYYYMMDD is met. So anyway to force the file that is opened to meet that criteria, and if it doesnt exist to kick out an error message?

Sub OpenCopyTXT()
Dim Fname As String
With Application.FileDialog(3)
.InitialFileName = "C:\Windows"
.Filters.Add "Text Files Only", "*.txt"
If .Show = -1 Then Fname = .SelectedItems(1)
End With
Workbooks.OpenText Fname
End Sub

 

[ItalianPlatinum]

Glad to help.
Being a person who still struggles every day to express himself clearly, I will be the last one to blame you.

ok sorry to bother/nag. finding one issue. The location of the file is only reading from my most recent folder I have used. it doesn't seem to be referencing the sPath. for example if i just open a blank excel and save it in the folder where the file is it finds it perfectly. but if i dont do that it is for some reason looking at my last used folder so it is kicking out an error file can be found then i need to debug

 

[GWteB]

In what way did you amend my post #15 code? The sPath variable needs to be a proper path to an existing folder and the string has to end with a backslash charachter.

 

[ItalianPlatinum]

In what way did you amend my post #15 code? The sPath variable needs to be a proper path to an existing folder and the string has to end with a backslash charachter.

That is what I have and if i save my VBA (file) into where the file is located it works:

Sub OpenCopyTXT_r2()
Dim sPath As String
Dim sPartial As String
Dim sFullName As String

sPath = "D:\Me\Documents\My Files\" ' <<<<< change accordingly

sPartial = "AAA_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
sFullName = Dir(sPath & sPartial)
If Len(sFullName) > 0 Then
Workbooks.OpenText sFullName
Else
MsgBox "File not found.", vbExclamation
End If
End Sub

 

[GWteB]

Apparently I had lost focus ? . This version of the code is expected to work in all circumstances ...

Sub OpenCopyTXT_r3()
    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String
   
    sPath = "D:\Me\Documents\My Files\"      ' <<<<< change accordingly
   
    sPartial = "AAA_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName
    Else
        MsgBox "File not found.", vbExclamation
    End If
End Sub

 

[ItalianPlatinum]

Apparently I had lost focus ? . This version of the code is expected to work in all circumstances ...

Sub OpenCopyTXT_r3()
    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String
  
    sPath = "D:\Me\Documents\My Files\"      ' <<<<< change accordingly
  
    sPartial = "AAA_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName
    Else
        MsgBox "File not found.", vbExclamation
    End If
End Sub

SUCCESS

 

[GWteB]

Thanks ?