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

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
220
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?

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

Thanks in advance!
~ZM~
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
.PrintOut Copies:=1, Collate:=True, PrToFileName:=Range("B7").Value
 

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
220
Try:
VBA Code:
.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

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
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

Board Regular
Joined
Oct 27, 2009
Messages
220
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.
VBA Code:
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~
:cool:
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Not a problem. Just concatenate the cells using something like this:
VBA Code:
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.
 
Solution

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
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):
VBA Code:
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

Board Regular
Joined
Oct 27, 2009
Messages
220
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):
VBA Code:
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~
:cool:
 

Forum statistics

Threads
1,141,413
Messages
5,706,298
Members
421,440
Latest member
cmphares

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top