open file using pattern matching VBA

Question

I have an issue with opening a file using pattern matching. The purpose is opening a file which has a patern like this : extraction _ 20180630 _ Data - Updated.Xlsx. This part of the file name : 201806 is set by the users via an input box function using variables as text. The goal is to open this file knowing that the dates can change depending on the target chosen by the users (year and month) .

Here is my code :

Sub OpenFile 
    Dim Directory As Text
    Dim File As Text 
    Dim MainPath As Text
    Dim Y As Text
    Dim M As Text 

    Directory = "C:\Desktop\Folder\"

    Y = InputBox  ("Please Choose your target Year such as 2017")
    M = InputBox ("Please Choose your target Month such as 06 for June")

    ' File name format = "extraction _ 20180630 _ Data - Updated _V5.2.xlslx"
    File = "extraction _" &  Y  & M 
    MainPath = Directory  & File 
    Filename = Dir (MainPath & "*_ Data - Updated _*")

    On Error  Resume Next

    ' after several Checking, the MainPath is Correct. the issue is whithin the second part 
    Workbooks.Open MainPath & Filename

    If Err.Number = 1004
        MsgBox " The file was not found"
        Err.Clear
        Application.DisplayAlerts = False
        Application.Quit 
     End If
End Sub

As You can imagine, this code generates an

error 1004

and I checked the main path which is correct. The issue is with the second part of Filename as Follows *_ Data - Updated _*)

How can I solve this?

If you add `MsgBox MainPath & "|" & Filename` to your code, what Path and FileName does it *actually* try to open? This might help you find which line the error is on — Chronocidal, Sep 10 '18 at 10:01
Using spaces ( ` `) in filenames is almost always a bad idea. — Luuklag, Sep 10 '18 at 10:21
I guess the issue is that the second part it's not taken into account by what it starts with " * ". usually, I use this terms '*' with someting before you know. — DataScientist, Sep 10 '18 at 10:22
@Luuklag. I know that, but it's not my choice. I have to handle it with this format with space :( — DataScientist, Sep 10 '18 at 10:24
Possible duplicate of [Opening a File whose Name Contains a Space](https://stackoverflow.com/questions/46593437/opening-a-file-whose-name-contains-a-space) — Luuklag, Sep 10 '18 at 10:27
@Chronocida. MsgBox Display what follows : C:\Desktop\Folder\extraction _ 201806| — DataScientist, Sep 10 '18 at 10:29
Maybe, there is a vba function which generates the last day of each month automatically in order to set a path with the full file name. — DataScientist, Sep 10 '18 at 10:32
@DataScientist Yup: `DateSerial(Year(Now()),Month(Now())+1,0)` will give the last day of the current month, or `DateSerial(cLng(Y),cLng(M)+1,0)` to get the last day based on your code. The fact that there is nothing after the "|" on the MessageBox means that `Filename` was not found by the `Dir` function. — Chronocidal, Sep 10 '18 at 10:39
Once you get the `FileName` working correctly, you should also be opening the file `Directory & Filename`, not `Mainpath & Filename` - otherwise you try to open `C:\Desktop\Folder\extraction _ 201806extraction _ 20180630 _ Data - Updated.Xlsx` instead of `C:\Desktop\Folder\extraction _ 20180630 _ Data - Updated.Xlsx` — Chronocidal, Sep 10 '18 at 10:46

Chronocidal · Accepted Answer · 2018-09-10T22:33:27.433

1

Based on discussion in comments above, try the following:

' File name format = "extraction _ 20180630 _ Data - Updated _V5.2.xlsx"
File = "extraction _" & Format(DateSerial(CInt(y), CInt(M) + 1, 0), "yyyymmdd") & "_ Data - Updated*"
Filename = Dir(Directory & File)

On Error Resume Next

' after several Checking, the MainPath is Correct. the issue is whithin the second part
If Len(Filename) > 1 Then
    Workbooks.Open Directory & Filename
Else
    MsgBox "Unable to find file for pattern """ & File & """ in location " & Directory, vbCritical
End If

Here is a version that will also roll back up to 3 months looking for the latest file:

' File name format = "extraction _ 20180630 _ Data - Updated _V5.2.xlsx"
Dim lRollBack As Integer
For lRollBack = 0 To 3
    File = "extraction _" & Format(DateSerial(CInt(y), CInt(M) + 1 - lRollBack, 0), "yyyymmdd") & "_ Data - Updated*"
    Filename = Dir(Directory & File)
    If Len(Filename) > 1 Then Exit For 'Stop after we find a match
Next lRollBack

On Error Resume Next

' after several Checking, the MainPath is Correct. the issue is whithin the second part
If lRollBack <= 3 Then
    If lRollBack > 0 Then
        MsgBox "We had to roll back by " & CStr(lRollBack) & " month(s)", vbInformation
    End If
    Workbooks.Open Directory & Filename
Else
    MsgBox "Unable to find file for pattern """ & File & """ in location " & Directory, vbCritical
End If

edited Sep 10 '18 at 22:33

answered Sep 10 '18 at 10:49

Chronocidal

6,827
1
12
26

Hi, Thanks for your quick reply. Just to understand why there is + 1 in what follow : CInt(M) + 1 ? – DataScientist Sep 10 '18 at 11:53
1

@DataScientist Because the *last* day of **this** month (e.g. `DateSerial(2018,09,30)`) is the same as the *zeroth* day of **next** month (e.g. `DateSerial(2018,10,0)`) - much like how `DateSerial(2018,13,1)` is the same as `DateSerial(2019,1,1)` – Chronocidal Sep 10 '18 at 12:09
@ Chronocidal : Last Question : Sometimes. I need to open the file of the previous month when the current is not available. I was Thinking of using M = M - 1 if the first is not available, but the Variable M is text. Do you know if is there a way to handle it with Format(DateSerial(CInt(y), CInt(M) + 1, 0), "yyyymmdd") directly ? Thanks Again – DataScientist Sep 10 '18 at 13:29
1

@DataScientist I have added a loop that rolls back up to 3 months – Chronocidal Sep 10 '18 at 13:45
@ Chronocidal that awesome!! big Thanks. Realy usefull. I hope that handle the first month as January to go back to the previous year. thnks a lot sir – DataScientist Sep 10 '18 at 18:24

open file using pattern matching VBA

1 Answers1