CODEWITHSUNDEEP
excelvbadate
Mike Rophone
Mike Rophone

Reputation: 39

Changing Date format when saving as PDF

I'm confronted with the following issue:
I would like to save a Sheet to PDF using VBA. The file name should include the values of some Cells, including one cell that holds a date. This date has been changed to the format "dd-mm-yyyy" (by means of code or in the Number Format dropdown in the Home tab) since Filenames cannot hold "/" but when I look at the formula bar the date is still "dd/mm/yyyy".

When running the code the filename returns the date in that format... How can I change the date format so that I can include it correctly in the filename?


Dim wsA As Worksheet
Dim wbA As Workbook
Dim strDate As Range
Dim strName As Range
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveWorkbook.Sheets("Devis")
Set strName = ActiveWorkbook.Sheets("Calc").Range("Designation")
Set strDate = ActiveWorkbook.Sheets("Calc").Range("arrival")

strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"
strDate = Replace(strDate, "/", "-")

strFile = strName & "_" & strDate & ".pdf"
strPathFile = strPath & strFile

myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler


End Sub

You will see I tried to replace the "/" with "-" in the code but this does not seem to help... Looking forward to your suggestions!

Upvotes: 1

Views: 1001

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

When you use strDate = Replace(strDate, "/", "-") you are calling the value in cell. Even if you see a date on it, dates on Excel are, actually, numbers.

So Replace(strDate, "/", "-") does not work, because there is nothing to replace.

Using Format allows to change how a value is displayed, and you can save that new formated value into a string variable.

That explains why strFile = strName & "_" & Format(strDate,"dd-mm-yyyy") & ".pdf" works.

Upvotes: 1

Related Questions