none
Issue with date as cell value in vba filename RRS feed

  • Question

  • Hi There

    I have the following code which works fine apart from when a date is used as a cell value for the file name.

    Sub PDFActiveSheet()
    Dim ws As Worksheet
    Dim strPath As String
    Dim myFile As Variant
    Dim strFile As String
    On Error GoTo errHandler
    
    Set ws = ActiveSheet
    
    strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
                & "_" _
                & " week ending " & Range("o2").Value _
                & ".pdf"
    strFile = ThisWorkbook.Path & "\" & strFile
    
    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strFile, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Select Folder and FileName to save")
    
    If myFile <> "False" Then
        ws.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    
        MsgBox "PDF file has been created."
    End If
    
    exitHandler:
        Exit Sub
    errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler
    End Sub
    

    Of course it uses / as a divider no matter what date format I use on the spreadsheet.

    Any ideas?

    Lastly

    What do I need to add to attach the PDF to an email in outlook and send it to a designated addess?

    Many Thanks

    Luke

    Friday, May 11, 2018 11:33 AM

Answers

  • I assume that O2 is the cell with the date. Change Range("o2").Value to

    Format(Range("o2").Value, "yyyymmdd")

    or

    Format(Range("o2").Value, "yyyy_mm_dd")

    or similar.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Luke Sykes Friday, May 11, 2018 3:23 PM
    Friday, May 11, 2018 12:09 PM

All replies