Open csv file that has current date in the name VBA code

[SantanaKRE8s]

Hello can someone help, I am trying to automatically open a csv file that has current date on the name so I can transfer certain columns into my workbook. Below is the VBA code I have. I get error that the file could have been moved or deleted. I placed VBA code in Module 1

Sub OpenCSV()

Workbooks.Open "C:\Users\Bsantana\OneDrive - TTI, Inc\SPACEX\BACKLOGREPORT\OPEN ORDER REPORTS_SPX\SpaceX_Open_Order_Report_" & Format(Date, "mm-dd-yyyy") & ".csv"

End Sub

 

[SantanaKRE8s]

Try...

Format(Date, "mm-d-yyyy")

Hope this helps!

when the date no longer has a 0 opn the day but it becomes 11 or 22, will it still open?

 

[Domenic]

Actually, just realized that we probably have to do the same thing for the month. For example, if the date for your January file is formatted as "1-31-2023", then it should be...

format(Date, "m-d-yyyy")

 

[Domenic]

when the date no longer has a 0 opn the day but it becomes 11 or 22, will it still open?

Yes, it will return the day without a leading zero. So a single digit will be returned for a single digit day and a double digit will be returned for a double digit day. The last one I posted will return both the month and day without the leading zero. So same thing here.

 

[Domenic]

Arg! You would not believe how many times I looked at that over and over again, and missed that leading zero on the day!
Thanks for the assist and third set of eyes!

Easy to overlook, I've done it myself as well...

 

[SantanaKRE8s]

Thank you both, its working, question, Im doing the same for an XLSX file and Im back to same error. Ive tried the same fix but not working

Sub vba_open_workbook()

Workbooks.Open "C:\Users\Bsantana\OneDrive - TTI, Inc\SPACEX\BACKLOGREPORT\LIST REPORTS_ TTI\WAS_CAS_" & Format(Date, "mm-d-yyyy") & ".xlsx"

End Sub

 

[SantanaKRE8s]

Its working found the problem, again thank you both