VBA Open .txt file with a string

ItalianPlatinum

Board Regular
Joined
Mar 23, 2017
Messages
95
Office Version
2016, 2010
Platform
Windows
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,916
Office Version
2013
Platform
Windows
Not sure I fully understand what you are after but modifiy the filter like
VBA Code:
.Filters.Add "Text Files Only", "*202005*.txt"
to get the month of May only in the dialog box. Then
for the message you can modify the If Show statement like'
VBA Code:
If .Show = -1 Then
    Fname = .SelectedItems(1)
Else
   MsgBpx "No Files Found"
End If
To avoid changing the code when you want a different month, you could use an InputBox to initialize a variable for the date.
Dim dt As String
VBA Code:
dt = InputBox("Enter a date in yyyymm format.", "DATE TO FILTER")
.Filters.Add "Text Files Only", "* & dt & *.txt"
 
Last edited:

ItalianPlatinum

Board Regular
Joined
Mar 23, 2017
Messages
95
Office Version
2016, 2010
Platform
Windows
No i am looking to modify it away from a dialog box to a mechanism where it will source and open the file based off a criteria the file format meets. to only open a file with "AAA_" & "YYYYMMDD" and ignore the time component at end.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,916
Office Version
2013
Platform
Windows
No i am looking to modify it away from a dialog box to a mechanism where it will source and open the file based off a criteria the file format meets. to only open a file with "AAA_" & "YYYYMMDD" and ignore the time component at end.
Well, vba is limited in how files can be opened by the parameters requiring a path and valid file name. You can use variables to represent the parameters, in whole or in part, but you still need the values of each element to find the file and open it. So in my mind, it is either use the dialog box, the Dir function or the File.Open method.. In all instances, it comes down to File.Open.
 

ItalianPlatinum

Board Regular
Joined
Mar 23, 2017
Messages
95
Office Version
2016, 2010
Platform
Windows
Ok the risk I am seeing is the user opens the wrong file and sort of blows up the macro. was seeing if i could force the file to open but problem is the time stamp in each file name that becomes useless to me. hmmm, So i am sort of out of luck on this then. I was really hoping of a way to say look for these criterias in the file if they meet to open it. if i know the last 4 digits are variables could something like ???? work to kind of ignore it?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,916
Office Version
2013
Platform
Windows
Yep, the time stamp presents a real challenge in trying to narrow the margin of error.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
701
Office Version
2013
Platform
Windows
You might be looking for something like this:
VBA Code:
Sub OpenCopyTXT()
    Dim Fname   As String
    Dim FFname  As String
    Dim bSorry  As Boolean
    Dim sDate   As String
    
    With Application.FileDialog(3)
        .InitialFileName = "f:\Wissen"
        .Filters.Add "Text Files Only", "*.txt"
        If .Show = -1 Then FFname = .SelectedItems(1)
    End With
    
    If Len(FFname) > 0 Then
        Fname = StripPath(FFname)
        If Len(Fname) >= 16 And StrComp(Left(Fname, 4), "aaa_", vbTextCompare) = 0 Then
            sDate = Mid(Fname, 11, 2) & "-" & Mid(Fname, 9, 2) & "-" & Mid(Fname, 5, 4)
            If IsDate(sDate) Then
                Workbooks.OpenText Fname
            Else
                bSorry = True
            End If
        Else
            bSorry = True
        End If
    Else
        MsgBox "Cancel was pressed"
    End If
    If bSorry Then MsgBox "File name has not expected format!", vbExclamation
End Sub

Public Function StripPath(ByRef argFullName As String) As String
    If argFullName <> "" Then
        StripPath = Right(argFullName, Len(argFullName) - InStrRev(argFullName, "\"))
    End If
End Function
 

ItalianPlatinum

Board Regular
Joined
Mar 23, 2017
Messages
95
Office Version
2016, 2010
Platform
Windows
You might be looking for something like this:
VBA Code:
Sub OpenCopyTXT()
    Dim Fname   As String
    Dim FFname  As String
    Dim bSorry  As Boolean
    Dim sDate   As String
   
    With Application.FileDialog(3)
        .InitialFileName = "f:\Wissen"
        .Filters.Add "Text Files Only", "*.txt"
        If .Show = -1 Then FFname = .SelectedItems(1)
    End With
   
    If Len(FFname) > 0 Then
        Fname = StripPath(FFname)
        If Len(Fname) >= 16 And StrComp(Left(Fname, 4), "aaa_", vbTextCompare) = 0 Then
            sDate = Mid(Fname, 11, 2) & "-" & Mid(Fname, 9, 2) & "-" & Mid(Fname, 5, 4)
            If IsDate(sDate) Then
                Workbooks.OpenText Fname
            Else
                bSorry = True
            End If
        Else
            bSorry = True
        End If
    Else
        MsgBox "Cancel was pressed"
    End If
    If bSorry Then MsgBox "File name has not expected format!", vbExclamation
End Sub

Public Function StripPath(ByRef argFullName As String) As String
    If argFullName <> "" Then
        StripPath = Right(argFullName, Len(argFullName) - InStrRev(argFullName, "\"))
    End If
End Function
Do I need to define the date in order to import the file? at the moment it pops a dialog box to select to that folder i select.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
701
Office Version
2013
Platform
Windows
For testing purposes, I changed the source folder path but failed to restore it later. That's why I recommend a minor code change.
Please replace this: .InitialFileName = "f:\Wissen" with this:
VBA Code:
.InitialFileName = Environ("userprofile") & "\Documents\"

Do I need to define the date in order to import the file?
No, you don't. You just need to navigate to the correct folder where your text files are located. Each time a text file is selected, it checks whether the file name format is correct or not, as you stated in your post #1 (ie like AAA_20200529_0513.txt). If the format meets these requirements (ignoring the _0513 part, as requested), the file will be opened, otherwise only a notification will follow.
 

ItalianPlatinum

Board Regular
Joined
Mar 23, 2017
Messages
95
Office Version
2016, 2010
Platform
Windows
I was really looking to discard the dialogbox and just have it open with the criteria that is being met. I was trying to remove the user involvement in the file to select.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,913
Messages
5,465,414
Members
406,426
Latest member
slari

This Week's Hot Topics

Top