using VBA code to open a file with a partial name

[Rasberry]

My problem is trying to find the correct syntax to open a file where I know the filepath, but the file name is partial and part of that partial changes based on the date. So the file names look like this:

I want to open the excel file that has "new" in it, and corresponds to the date in my string currMonth (e.g. 20231114)

 

[DanteAmor]

Try this:

Sub openbooktest()
  Dim sPath       As String
  Dim sFile       As String
  Dim sName       As String
  Dim currMonth   As String
 
  currMonth = "20231114"
  sPath = ThisWorkbook.Path & "\"               'fit to your path
  sName = "*New_" & currMonth & ".xls*"
  sFile = Dir(sPath & sName)
  If sFile = "" Then
    MsgBox "Workbooks dont exists"
  Else
    Workbooks.Open sPath & sFile
  End If
End Sub

 

[Rasberry]

I have several hours, and something isn't quite right. The following command works to open the file named: "Late-Money-11240-869690-12072023-New.xls"
However, the name is going to change based on the date so I am trying to use a string currMonth for part of the wildcard/partial name, and no matter what syntax I use with the "*", if I try to substitute a string like currMonth, it can't find the file.

Dim currMonth As String
currMonth = "12072023"
Workbooks.Open Dir("I:\Securities\Asset Classes in the Portfolio\Bond Holdings\SBA RMOF\A - Daily email files\*12072023-New.xls")
Windows("Test open excel file.xlsm").Activate

 

[Rasberry]

I solved it!

Dim currMonth As String
Dim myFile As String
Dim extension As String
Windows("Test open excel file.xlsm").Activate
Sheets("Sheet1").Select
currMonth = Range("D2")
myFile = "Late-Money-11240-869690-"
extension = "-New.xls"
Workbooks.Open Dir("I:\Securities\Asset Classes in the Portfolio\Bond Holdings\SBA RMOF\A - Daily email files\" & myFile & currMonth & extension)

The date can change in cell D2, and it will open the file.

 

[Rasberry]

Never mind. It stopped working. Now it tells me it can't find the file. There must be something weird going on with this.

 

[DanteAmor]

I have several hours, and something isn't quite right. The following command works to open the file named: "Late-Money-11240-869690-12072023-New.xls"
However, the name is going to change based on the date so I am trying to use a string currMonth for part of the wildcard/partial name, and no matter what syntax I use with the "*", if I try to substitute a string like currMonth, it can't find the file.

Did you try what I put in post #2.
I forgot to mention that you should adjust the variable names to what you have.
But the macro works if you only put the date.


According to your example, your files have this structure:

"some data"-New_currMonth".xls"

I don't know exactly what you have in cell D2.
I also don't know what folder you have the file in, but adjust it in the code.

But before exchanging the data to cell D2, try my macro.

Sub openbooktest()
  Dim sPath       As String
  Dim sFile       As String
  Dim sName       As String
  Dim currMonth   As String
  
  currMonth = "20231114"
  sPath = "I:\Securities\Asset Classes in the Portfolio\Bond Holdings\SBA RMOF\A - Daily email files\"
  sName = "*New_" & currMonth & ".xls"
  sFile = Dir(sPath & sName)
  If sFile = "" Then
    MsgBox "Workbooks dont exists"
  Else
    Workbooks.Open sPath & sFile
  End If
End Sub

 

[Rasberry]

Did you try what I put in post #2.
I forgot to mention that you should adjust the variable names to what you have.
But the macro works if you only put the date.


According to your example, your files have this structure:

View attachment 104522

"some data"-New_currMonth".xls"

I don't know exactly what you have in cell D2.
I also don't know what folder you have the file in, but adjust it in the code.

But before exchanging the data to cell D2, try my macro.

Sub openbooktest()
  Dim sPath       As String
  Dim sFile       As String
  Dim sName       As String
  Dim currMonth   As String
 
  currMonth = "20231114"
  sPath = "I:\Securities\Asset Classes in the Portfolio\Bond Holdings\SBA RMOF\A - Daily email files\"
  sName = "*New_" & currMonth & ".xls"
  sFile = Dir(sPath & sName)
  If sFile = "" Then
    MsgBox "Workbooks dont exists"
  Else
    Workbooks.Open sPath & sFile
  End If
End Sub

I think the problem might be that it is a 97 excel workbook and it keeps telling me it doesn't find it. So I am going to try to attack this another way (by saving each file as a xlsx file). The code works when it finds a xlsx file. However, to do that, I am going to have to re-write the code which locates the attachment from email and saves the xls 97 file from the email.

 

[DanteAmor]

I think the problem might be that it is a 97 excel workbook and it keeps telling me it doesn't find it

I tested with a 97 file with xls extension and it works for me.

Did you test with my macro or did you modify the macro again?

 

[Rasberry]

Ok, so I scrapped my code and did just yours, and yes it worked! I finally was able to open the file consistently, and then, I made currMonth dynamic. When I change the date in the spreadsheet and it looks to that cell, it will recognize whatever date in that cell is, and use that in the sName. Thank you so much!