Print to PDF - need file name pre-loaded from cell

[zombiemaster]

Hoping for some easy help. I have a button on a worksheet that when you click it, it takes the data on whatever row you're cursor is on and fills in a "work item" and prints it to PDF that you decide where to save it to on your workstation.

Currently, the PDF is un-named until you name it in the PDF print dialog. I've been asked if it can be pre-filled with the name that is in cell B7. I know it's possible but online searches weren't much help. Here's what I've got right now - can someone help fill in the blanks of how to do this?

    With Sheets("WORK ITEM TEMPLATE")
       .Visible = True
       .Calculate
       .PrintOut Copies:=1, Collate:=True
       .Visible = False
    End With

Thanks in advance!
~ZM~

 

[shknbk2]

Try:

.PrintOut Copies:=1, Collate:=True, PrToFileName:=Range("B7").Value

 

[zombiemaster]

I'm going to try that today - thank you!!

 

[zombiemaster]

Try:

.PrintOut Copies:=1, Collate:=True, PrToFileName:=Range("B7").Value

Thanks for the response shknbk2, but adding that code stopped the print dialog box from appearing entirely...not sure why? I took the code back out and it's working again (without pre-filling the file name, of course).

Is there another method that might work instead?

Thanks,
~ZM~

 

[shknbk2]

Yeah, including PrToFileName saves directly to file without showing the print dialog box show up.

I can't find how to populate the print dialog box while still having it show up. However, you could use the SaveAs dialog box to approximate the same thing.

Sub PrintOutAsPDF()
    Dim sFileName As String
    
    With Sheets("WORK ITEM TEMPLATE")
       .Visible = True
       .Calculate
        sFileName = Range("B7").Value
        sFileName = Application.GetSaveAsFilename(InitialFileName:=sFileName, Filefilter:="PDF (*.pdf), *.pdf", Title:="Save Print Output As")
        If sFileName <> "False" Then
            .PrintOut Copies:=1, Collate:=True, PrToFileName:=sFileName
        End If
       .Visible = False
    End With
End Sub

 

[zombiemaster]

Yeah, including PrToFileName saves directly to file without showing the print dialog box show up.

I can't find how to populate the print dialog box while still having it show up. However, you could use the SaveAs dialog box to approximate the same thing.

Sub PrintOutAsPDF()
    Dim sFileName As String
   
    With Sheets("WORK ITEM TEMPLATE")
       .Visible = True
       .Calculate
        sFileName = Range("B7").Value
        sFileName = Application.GetSaveAsFilename(InitialFileName:=sFileName, Filefilter:="PDF (*.pdf), *.pdf", Title:="Save Print Output As")
        If sFileName <> "False" Then
            .PrintOut Copies:=1, Collate:=True, PrToFileName:=sFileName
        End If
       .Visible = False
    End With
End Sub

I just did a quick test and I think you just nailed it! Thank you!!

Now, to anticipate a follow-up request that I am pretty sure management is going to ask for... Is there a way to have the file name be a combination of two cells? For example I have B7 which is the Name, and I also have B19 which is the Office...is there a way to combine them so it shows up in the file name as B7 & B19 "John Doe Tampa"?

If that's not possible, at least what you've given me seems to work like a charm and I appreciate the help!

~ZM~

 

[shknbk2]

How could you possibly think that management would have more questions?

Not a problem. Just concatenate the cells using something like this:

Range("B7").Value & " " & Range("B19").Value

The ampersand & concatenates string values (make sure there is a space before and after). Also think about the end result. If I didn't add the extra space in the middle (the " " part), it might end up with John DoeTampa.

 

[shknbk2]

Also note that the Title of the SaveAs dialog in my example ("Save Print Output As") was meant to mimic the dialog that shows up if you didn't include the output file. However, you can change this value to whatever you want. Maybe use is as partial instructions: "Save PDF to Shared Drive" or something like that.

Also, you can also pre-set the destination folder if you include a path before the B7 value. If there is a particular location that the files would be saved in, at least as a starting location, you could add that. You would do something like this (making sure to include the ending backslash):

sFileName = "C:\storage path\" & Range("B7").Value & " " & Range("B19").Value

Just go to the initial folder and copy the address at the top of the window.

 

[zombiemaster]

Also note that the Title of the SaveAs dialog in my example ("Save Print Output As") was meant to mimic the dialog that shows up if you didn't include the output file. However, you can change this value to whatever you want. Maybe use is as partial instructions: "Save PDF to Shared Drive" or something like that.

Also, you can also pre-set the destination folder if you include a path before the B7 value. If there is a particular location that the files would be saved in, at least as a starting location, you could add that. You would do something like this (making sure to include the ending backslash):

sFileName = "C:\storage path\" & Range("B7").Value & " " & Range("B19").Value

Just go to the initial folder and copy the address at the top of the window.

That's exactly what I needed - thanks so much for the time and effort - it is much appreciated! You ROCK!

~ZM~

 

[shknbk2]

You're welcome. Glad to help.