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

 

[Joe4]

Thanks for confirming that.

Try this code and see if it returns the file name in a Message Box.

Sub LoopThroughAllFilesInFolder()

    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String

    myPath = " C:\Users\Bsantana\OneDrive - TTI, Inc\SPACEX\BACKLOGREPORT\OPEN ORDER REPORTS_SPX\"
    myExtension = "*10-4-2023.csv"

    myFile = Dir(myPath & myExtension)
    Do While myFile <> ""
        If Len(myFile) = 0 Then Exit Do
        MsgBox myPath & myFile
        
        myFile = Dir
    Loop

End Sub

If not, try changing this line:

    myExtension = "*10-4-2023.csv"

to this:

    myExtension = "*.csv"

and run again.

This should return a message box for each CSV found in that directory.
Does it return any?

 

[SantanaKRE8s]

I tried both ways

 

[Joe4]

And what happened?
Are you getting an error on that line, and if so, what exactly does it say?

 

[SantanaKRE8s]

If I put it this way, with the specific date it will open it right away, but since whenever the file is downloaded the title has the current date, which is why Im trying to get it to work with the Current day function.

Sub OpenCSV()

Workbooks.Open "C:\Users\Bsantana\OneDrive - TTI, Inc\SPACEX\BACKLOGREPORT\OPEN ORDER REPORTS_SPX\SpaceX_Open_Order_Report_10-4-2023.csv"

End Sub

 

[SantanaKRE8s]

And what happened?
Are you getting an error on that line, and if so, what exactly does it say?

I get this message

 

[SantanaKRE8s]

Can something be wrong in the current day function? is there another option to write the current day function

& Format(Date, "mm-dd-yyyy") &

 

[Domenic]

Try...

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

Hope this helps!

 

[SantanaKRE8s]

Awesome, it worked. finally worked

 

[SantanaKRE8s]

Try...

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

Hope this helps!

besides this file there is a second file that needs to open also do I do somehting similar in seperate module or how can I added to this same one?

 

[Joe4]

Try...

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

Hope this helps!

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!