VBA Open .txt file with a string

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
733
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2010
  5. 2007
Platform
  1. 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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Yep, the time stamp presents a real challenge in trying to narrow the margin of error.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top