Dynamically Reference new date in File Name daily

  • Hello,


    I am fully aware of how ugly this code is going to be as it's a recorded macro and I am by no means a master. I figure my question seems to be a very simple one, but I can't seem to find an answer for it. Basically I am attempting to get my macro to reference a new date in a file name (I/E: Report 02-04-2020 will be Report 02-05-2020 tomorrow). As of right now, the macro will only reference the exact date that is manually entered. (basically I go in every day and change it to the previous day's date). I have tried using *'s/?'s/*.*'s in place of the date in hopes it would work to no avail. I feel like this is a really simple resolution but I can't seem to find it. Here's my code line 11 being the issue I'm trying to resolve:


  • Hello and Welcome to the Forum :)


    Indeed there are several things to be improved in your recorded macro ...


    But focusing specifically on your ' date ' question ... your line 11 does not show any date ...:/

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I know, line 11 shows the method I *attempted* to resolve based on Google Research. I can change it, or assume that it's yesterday's date and I need it ready for today's date.

  • When it comes to using dates within a macro ...


    Code
    1. Sub YesterdayTest()
    2. MsgBox Date - 1
    3. End Sub


    Does it help ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Unfortunately not... I think the issue lies in the fact that it's a "quoted" file name. But I honestly have no clue and that may not mean anything lol. But I noticed I can't use anything that would constitute a formula within it... (I've tried date-1 just to see if it would work) but it just won't function. Maybe this isn't even possible.

  • Well ...


    Cell A1 is holding your starting point ... i.e. the file name with a given date ...

    and

    Cell A7 rebuilds the new file name including the ' yesterday date ' compared to the initial one stored in cell A1 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I would manually have to update it at that point anyway as this report is daily. I'm already manually updating it so I can continue doing that I suppose. Just casting a line to see if I caught a workable resolution. As I said before, maybe it isn't possible. Or if it is possible, beyond my current ability.

  • Sorry ...


    but the formulas within the test file do show you can build your file name with whatever date you do need to insert in the file name ...


    without any manual intervention ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • You could test following instruction ... for your line 11


    Code
    1. Windows(Evaluate("=""Shifts Scheduled ""&TEXT(" & CLng(Date - 1) & ",""mm-dd-yyyy"")&"" - Final.xlsx""")).Activate

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I do believe that will work! I tested it and it worked correctly, but the real test will be when I do it tomorrow for real. The only other issue I have, would be that I do Friday, Sat, and Sun reports on Monday. I can manually do that by changing the (Date - 1) to Date - 3 then 2 then 1 respectfully. That is, unless you have a suggestion that would fix that as well, which would be awesome but as long as this works, I will NOT complain at all lol. Thanks Carim!

  • Glad to hear this is helping you out ...;)


    Thanks for your Thanks ... AND for the Like :)


    If you need to refine the concept of ' yesterday ' ...


    you do need to explain with all the concrete details what does happen on Mondays ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • From your previous message ... it looks like Mondays are busy days ... since 3 reports need to be produced ...


    If that is the case, you could use something along these lines:


    Code
    1. Sub TestMondays()
    2. ' If and Only If on Mondays you have to produce 3 Reports
    3. ' i.e. Reports for Friday, Saturday and Sunday ...
    4. ' Only on Mondays - User will be asked to select between 1,2,3
    5. Dim inb As Integer
    6. inb = 1
    7. If WeekdayName(Weekday(Date)) = "Monday" Then inb = InputBox("Select 1, 2, 3:", "Nb of Days to Deduct")
    8. Windows(Evaluate("=""Shifts Scheduled ""&TEXT(" & CLng(Date - inb) & ",""mm-dd-yyyy"")&"" - Final.xlsx""")).Activate
    9. End Sub


    Hope this is in line with your expectations

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • So that would check if the current day is "Monday" then pop a box that says "select 1/2/3" and 3 would be Friday, 2 would be Sat, 1 would be Sun. If I understand correctly, that would be awesome... My only question is, do I just input that on the first line of the code or does it need to be placed somewhere else?

  • Your understanding is correct ...


    In you original macro ( which again does deserve to be totally rebuilt ...)


    the new lines 7 and 8 have to replace your previous line 11


    and the new lines 5 and 6 need to be inserted at the top of your macro ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited once, last by Carim ().