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]

That error message is telling you exactly what is going on - it cannot find any file by that name in that location.
Note that your name must match EXACTLY. Any extra character or space will cause it not to see/find the file.

If you go to Windows Explorer, can you manually browse to that folder and find a file with that exact name?
If you try to manually open the file from Excel by manually browsing to it, can you find the file under that exact path and file name and open it?

 

[SantanaKRE8s]

Hello Joe4, yes I can get straight to the folder where my file is using the path in the VBA, Today I downloaded a new file it comes again with current date and the VBA is readind the correct fiel name.
File Name- SpaceX_Open_Order_Report_10-4-2023
Path - C:\Users\Bsantana\OneDrive - TTI, Inc\SPACEX\BACKLOGREPORT\OPEN ORDER REPORTS_SPX

I check for spaces but dont see any diference. Everything looks correct. The only thing I notice is that if I put the file name with todays date it will open it but when I put the today function thats when it does not open. I added the today function because everytime we download this file from the website it has the current date on it, so I want to avoind having to change it everyday.

 

[Joe4]

I cannot tell for certain from the image of your File Explorer, but is there a space between "Report_" and "10" in the file name?

Also, can you confirm that from Excel, you can manually browse to the folder and successfully open it (try to do it manually from inside of Excel)?

 

[SantanaKRE8s]

There is no space,
File name
SpaceX_Open_Order_Report_10-4-2023.csv

VBA
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]

Still waiting for confirmation of the other question that I have asked twice now:

If you try to manually open the file from Excel by manually browsing to it, can you find the file under that exact path and file name and open it?

Also, can you confirm that from Excel, you can manually browse to the folder and successfully open it (try to do it manually from inside of Excel)?

 

[SantanaKRE8s]

when I browse to open the file it does not appear Since its set to " All Excel Files ", so I only see one file thats Excel, but if change it to " All Files " then all my CSV files appear

 

[Joe4]

That is not what I am asking.
I am asking, "are you able to successfully OPEN the CSV file manually from inside of Excel".

Just because you are able to see the record does not mean you can open it.
So I want to rule out that possibility first.
If you are not able to open it, then the issue probably has nothing to do with Excel, and is a network issue (maybe the file is open elsewhere, maybe you do not have full rights to the directory, etc).

 

[SantanaKRE8s]

Yes I can open it,

 

[SantanaKRE8s]

This is opens first