VBA open file with name that changes daily

[johnmcpa]

I use a program called Fishbowl for inventory management. Each day, the program generates a report of all orders fulfilled that day as a .csv file. I want to create a macro that will automatically open this file daily, reformat it, save it as an Excel file and email a .pdf of the reformatted file to my boss.

I can work out the formatting, and emailing. Where I am having trouble is right up front. The program saves the .csv file to a folder with the name Shipments today YYYY-MM-DD HH-MM
The report is generated each day at 18:00 so that part of the name remains the same, but the date changes daily.

If I ran the macro at 18:10 each day, is there a way to code it so it can open the right file?

Thanks,

John

 

[dermie_72]

you could dim yr, mth and dy as string and have:

yr = Format(Date, "yyyy")
mth = Format(Date, "mm")
dy = Format(Date, "dd")

added to puck up today's date.
Then when it comes time to load the file you can have the file name showing as

"Shipments today " & yr & "-" & mth & "-" & dy &" 18-00"

that will pick up the today's date, and load that file. If the file is created at 6:00pm and you're looking at the file the next day, will it have yesterday's date on it?

 

[johnmcpa]

Yes. It will have yesterday's date. The file that Fishbowl creates is a .csv generated by iReports and all of the values in the file are static. There are no formulas in it at all.

 

[dermie_72]

If the day is yesterdays's date, then use

dy = Format(Date - 1, "dd")

if the date on Monday is Friday's date, then you could have a variable in there that checks the day name before the minus.

if format(date,"dddd").value = "Monday" then
    a = 3
    else a = 1
end if
dy = format(date - a,"dd")

Then link the date variables into the file name as mentioned in post #2.