Save a file as a pdf to folders dependent on the current date

  • I have the following code to save a worksheet as a pdf when the button is pressed:

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\9. Office12\msn plan share\2. Folders" & _
    *******see below*******
        Worksheets("ATO Days").Range("D2").Value & ".pdf", _
    End Sub

    After 2. Folders\ I need the filename to be in this format but for the date 2 days after the current date:

    2017\04 Apr\09 Apr **and whatever is in Worksheet "ATO Days", cell D2**

    I tried putting the formatted dates into different cells on the sheet "ATO Days" (see attached) and referencing them for the filename, but to no avail.

    I am very new to VBA.

    Thank you for the help.

  • Re: Save a file as a pdf to folders dependent on the current date

    If I'm understanding your problem correctly (i.e. you want the file name to start with the current date + 2 days and then be followed by something in Worksheets("ATO Days").Range("D2").Value).

    Unfortunately I'm in work just now and can't download the file to confirm if this what you are trying to do, the description is a bit sketchy as to why you have two dates in your example (2017\04 Apr\09 Apr).

    First thing I'd say is that including backslashes in your file name is interpreted as child folders by Windows so "2017\04 Apr\09 Apr" isn't the name of a file, it's saying in the 2017 folder, then in the 04 Apr folder, then in the 09 Apr folder.

    Generally when using full dates in a file name people will use periods as the delimiter as windows doesn't take this as an instruction of something else (like the backspace).

    The other common method is to flip the date into a big endian format with no delimiters as this acts as a serial number than can easily be ordered (unlike using the period), big endian meaning for example 20170411 for today's date. Anyway, I digress. to get today's date + 2 days you just need to use:

    (Date + 2)

    "Date" returns the current system date and "+ 2" adds two days to it.

    I suspect after that you want to format it a particular style but I'd need to understand if you are actually putting this in a folder structure of Year > Day & Month as your example suggests or not.

  • Re: Save a file as a pdf to folders dependent on the current date

    I should have used "location" instead of "file name".

    The location I need the file saved to is ...\2017\04 APR\09 APR *the value in cell D2*

    the folder nomenclature is odd but it is already existing and cannot be changed. 04 APR is the month (April being the 4th month), 09 APR is the day (April 9; again, odd nomenclature) and the value in cell D2 is an auto populating 2 letter identifier for the day.

    And that will be the date 2 days ahead of when this is generated.

    Thank you for your help.

  • Re: Save a file as a pdf to folders dependent on the current date

    I'd probably just go with something like:

    replace(cstr(date+2), "/", ".")

    At the end of your file path/location part.

    This will give you the current date that code is being fired, add two days to it, switch it into a string representation of that date and replace the "/" with "." (since windows won't allow "/" to make up part of a filename).

    So something like:

    \2017\04 APR\09 APR & replace(cstr(date+2), "/", ".") & .xlsx

    Although the location part of the file path may well be a variable value rather than a hardcoded text string in your code.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!