Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

Thread: date as filename vba

  1. #1
    Board Regular
    Join Date
    Jan 2016
    Posts
    247
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default date as filename vba

    Im using this line to save a worksheet as a workbook. I want to use a date from the sheet as part of the filename. Vba doesnt like it. Im pretty sure I need to use 'Format..."dd-mm-yy"' around or within 'sh.range("q3")'
    But whatever ive tried is giving an error.
    Any guidance gratefully received. Thanks


    ActiveWorkbook.SaveAs FileName:="/Users/User/Desktop/" & sh.Name & sh.Range("Q3")

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,585
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: date as filename vba

    What's actually in Q3 ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Jan 2016
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: date as filename vba

    Possibly you need to add & ".XLSX" to the end to define your file type

  4. #4
    Board Regular
    Join Date
    Jan 2016
    Posts
    247
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: date as filename vba

    Q3 contains a date, the line works if Q3 is not a date, it doesn't like the date format at the end of the filename.

    also just found out that I need to save as a pdf. Does that make things more complicated.

  5. #5
    Board Regular
    Join Date
    Mar 2017
    Posts
    148
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: date as filename vba

    Hi,

    My guess would be that / in the date format throws the error, as it will cause the macro to search for a folder based on your date.
    Therefore, I would suggest using the substitute function to replace the / with something else, like:

    ActiveWorkbook.SaveAs Filename:="/Users/User/Desktop/" & sh.Name & WorksheetFunction.Substitute(sh.Range("Q3"), "/", "-")

    Replaces / with - and should save your file accordingly.

  6. #6
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: date as filename vba

    Quote Originally Posted by twl2009 View Post
    Q3 contains a date, the line works if Q3 is not a date, it doesn't like the date format at the end of the filename...
    If your default formatting for date includes slashes, that is going to hiccup, as a filename cannot contain slashes. For instance, the default format for me in US English is m/d/yyyy. So if I enter =TODAY() in cell A1 and I type in "?cells(1).value" , it returns 4/24/2017.

    You could try Format(sh.Range("Q3").Value, "mm-dd-yyyy") or similar with hyphens.

    Hope that helps,

    Mark

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,585
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: date as filename vba

    Okay, in that case you need to change the format....without seeing the date....I'd suggest you need to remove the slashes or dots from the date.
    Something like

    Code:
    ActiveWorkbook.SaveAs Filename:="/Users/User/Desktop/" & sh.Name & Format(Range("Q3"), "yyyymmdd") & ".pdf"
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    New Member
    Join Date
    Apr 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: date as filename vba

    Hi twl....
    add the below lines to your code and try...

    Dim filename As String
    filename = Range("B2")
    Path = "INPUT YOUR PATH HERE"
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal

    Cheers!!!

  9. #9
    Board Regular
    Join Date
    Jan 2016
    Posts
    247
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: date as filename vba

    That has sorted the date out, but the pdf is corrupted when saved.

  10. #10
    New Member
    Join Date
    Apr 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: date as filename vba

    Quote Originally Posted by avicric View Post
    Hi twl....
    add the below lines to your code and try...

    Dim filename As String
    filename = Range("B2")
    Path = "INPUT YOUR PATH HERE"
    ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal

    Cheers!!!
    PLEASE IGNORE DID NOT READ THE POST CORRECTLY

    Try this instead

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("A1").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

    Cheers!!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •