VBA - Opening File with Yesterday's Date

[Goureyham]

I hope everyone is doing ok with COVID and keeping busy.
I was trying to make a macro in order to open files based on yetserday's date.
For instance, everyday, there is file that is created.
I just wanted to open the file based on yesterday's date.
I looked extensively on-line, youtube, and more for information, but I just couldn't solve the problem. Below is what I am trying to do:

Sub OpenWorkbook()
'
' Code to open Excel File based on Shorts Report with yesterday's date

ChDir "W:\Purchasing & Inventory\Daily Shorts Report"
Workbooks.Open Filename:= _
"W:\Purchasing & Inventory\Daily Shorts Report\Daily Shorts Report "& Format(NOW-1(),"YYYYMMDD")&".xlsx")

Would someone know how to provide me some advice?
Thank you

[Paul_Hossler]

Try this

Remove MsgBox's or comment out and uncomment the .Open line

Option Explicit


' Code to open Excel File based on Shorts Report with yesterday's date
Sub OpenWorkbook()
    Dim sYesterday As String, sFileName As String


    sYesterday = Format(Now - 1, "YYYYMMDD")
    MsgBox sYesterday
    
    sFileName = "W:\Purchasing & Inventory\Daily Shorts Report\Daily Shorts Report " & sYesterday & ".xlsx"
    
    MsgBox sFileName
    
'    Workbooks.Open sFileName
    
    
End Sub