How to save a worksheet as a pdf specified by the sheet name and a cell and todays date-CodePudding

I have a worksheet that I need to save with the active sheet name, a cell and the date.

Sub PrintToPDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:="P:\Estimating Misc\EXPERIMENTS\"   ActiveSheet.Name   " & "RANGE("F18:J18") & FORMAT("DATE,DD.MM.YYYY")".PDF", _
                                IGNOREPRINTAREAS:=False, _
                                OPENAFTERPUBLISH:=True

End Sub

I am super new to VBA so this is a collection of what I've found online. What am I missing?

CodePudding user response：

Use & to concatenate, not .
Refer to a merged cell's value by its topmost, leftmost cell, so F18 only.

Sub PrintToPDF()
    With ActiveSheet
        Dim FileName As String
        FileName = "P:\Estimating Misc\EXPERIMENTS\" _ 
            & .Name & " " & .Range("F18").Value _ 
            & Format$(Date, "dd.mm.yyyy") & ".pdf"

        .ExportAsFixedFormat Type:=xlTypePDF, _
                             FileName:=FileName, _
                             IgnorePrintAreas:=False, _
                             OpenAfterPublish:=True                 
    End With
End Sub

CodePudding user response：

When you calculate the filename, your pieces are off. The quote characters are at the wrong places. Everything within a pair of quotes is kept untouched by VBA.

If you want to read the content of one cell, you need to specify that exact cell. A range of cells (like Range("F18:J18")) will fail, even if the cells are merged. Just specify the first cell (Range("F18:J18").

And you should always use & for string concatenation.

Best is to create the filename into a variable - if it is not correct, you can use the debugger or the immediate window (Ctrl G) to check what is going on.

Dim pdfFilename As String
pdfFilename = "P:\Estimating Misc\EXPERIMENTS\" _
            & ActiveSheet.Name _
            & Range("F18") _
            & Format(Date, "DD.MM.YYYY") _
            & ".PDF"
' Check in the immediate window the content of the variable pdfFilename.
Debug.Print pdfFilename

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                            Filename:=pdfFilename, _
                            IGNOREPRINTAREAS:=False, _
                            OPENAFTERPUBLISH:=True