Naming function of export to pdf failing

[morrisps]

I have hunted around and am losing my mind trying to figure this out.
So I have the following code:

Private Sub CommandButton3_Click() 'exporting to pdf
   Dim NamingPDF As String
   Dim MyFile As Variant
   Dim projectnum As String
   Dim Address As String
   Dim doneby As String
   Dim toDate As String
      
    Sheet3.Select
    projectnum = Range("A3").Value
    Address = Range("B3").Value
    doneby = Range("C3").Value
    toDate = Range("E3").Value


        If (projectnum = "[#]") Or (Address = "[address]") Or (doneby = "[name]") Or (toDate = "[mmmm dd, yyyy]") Then
             MsgBox ("Please fill in required fields first: Project Number, Address, Estimate done by, Date found in Row 3. Thank You")
         Else
            Sheet3.Select
            Sheet1.Select (False)
            NamingPDF = "Project Num" & projectnum & ", Cost Estimate" & " - " & toDate & ".pdf"
            ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="mystuff", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=False, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
                
            MsgBox "Your PDF has been successfully exported."
         End If


    Sheet3.Select
End Sub

It works fine when I just stick the string "My Stuff" in for filename, but when I try to use NamingPDF (the created variable) it fails. I need the name that is drawn from the specified ranges.

The one other thing I want is it to open the location where the file is saved to so the user can see...

Thanks for your help

 

[JoeMo]

What exactly happens when "it fails"? Do you get an error message or .....? If you get an error message, what is it and which line is highlighted? What are the values of the variables "projectnum" and "toDate" when "it fails"?

 

[morrisps]

I get this error:

Run-time error '1004':

Document not saved. The document may be open, or an error may have been encountered when saving.

It highlights the entire block:

ActiveSheet.ExportAsFixedFormat _                Type:=xlTypePDF, _
                Filename:=NamingPDF, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=False, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True

The values shouldn't matter as it is just a string name right?
projectnum is just the project number so a integer
toDate is the date in a standard format

Like I said before if I assign the name as just a string example:

Filename:= "Testing My File", _

Then it saves perfectly.

 

[JoeMo]

I get this error:



It highlights the entire block:

ActiveSheet.ExportAsFixedFormat _                Type:=xlTypePDF, _
                Filename:=NamingPDF, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=False, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True

The values shouldn't matter as it is just a string name right?
projectnum is just the project number so a integer
toDate is the date in a standard format

Like I said before if I assign the name as just a string example:

Filename:= "Testing My File", _

Then it saves perfectly.

Wrong - the values may matter, especially if they contain characters that are forbidden for use in file names. I suspect your date in standard format is of the form mm/dd/yyyy or something similar. Note that "/" is a forbidden character for use in filenames. Change the format of the variable "toDate" to "mm.dd.yyyy" and you should be fine.

 

[morrisps]

The date is August 28, 2017... no /. You are right it is a problem with the date format. Because all the other fields work without it. But I need the date and cannot find a format for which it works.

I tried saving it with the name I want just using the normal save as and manually wrote in "Project Num 1234 Cost Estimate, August 28, 2017" and it saved fine. So all those characters should be allowed. But for the life of me I cannot get the toDate portion to work in vba.

 

[JoeMo]

Looking at your code, most likely the problem is in the syntax in this bit:

Or (doneby = "[name]") Or (toDate = "[mmmm dd, yyyy]")

Neither name nor mmmm dd, yyyy is defined and the way you are using the square brackets is suspect.

 

[morrisps]

Notice how my code prevents it from running if those values are in the cells. Those have to be user filled fields before the code runs. Also doneby is just verifying that it is filled in it is not used by the code.