VBA to save page as .pdf, name to come from cell values

Ganesh Kanade · Apr 3, 2014

Try this:

Code:

Sub SaveAsPDF()
'Saves active worksheet as pdf using concatenation
'of A1,A2,A3

Dim fName As String
With ActiveSheet
    fName = .Range("A1").Value & .Range("A2").Value & .Range("A3").Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\My Documents\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End Sub

This code worked for me too, many thanks for that, but I need space between values of the cells and the pdf file should open after creating it. Pls help.

And also is it possible to select the file path each time instead of fixed file path.?

Luke M · Apr 3, 2014

Can you elaborate on what you mean by "space between values"? To open the file after creating, change this:
OpenAfterPublish:=False
to this:
OpenAfterPublish:=True

How do you want to choose the filepath? Type it into a cell and have the code read it, or...?

Ganesh Kanade · Apr 5, 2014

Thanks for reply.

I am using two cell values to create the file name, suppose if cell A1 contains "XXXXX" and cell A2 contains "YYYYY" by using above code the file name is becoming XXXXYYYY but I need in XXXX YYYYY or XXXX - YYYYY format. Is it posssbile?

For file path I may use different folders whenever create a pdf file, so it should ask me where to save the file. For example whenever we use save as function a pop-up window will open, asking where to save it & we choose the required path/folder & save it. I hope you got my point.

Thanks
GK

SirJB7 · Apr 5, 2014

Hi, Ganesh Kanade!

For your 1st issue (output filename) try replacing this:

Code:

fName = .Range("A1").Value & .Range("A2").Value & .Range("A3").Value

by this:

Code:

fName = .Range("A1").Value & " " & .Range("A2").Value & .Range("A3").Value

or this:

Code:

fName = .Range("A1").Value & " - " & .Range("A2").Value & .Range("A3").Value

depending on the format that you'd finally choose.

Regards!

Ganesh Kanade · Apr 6, 2014

Hi SirJB7,

That's wonderfull, Thanks for the suggestion it worked perfectly.

Thanks
GK

SirJB7 · Apr 7, 2014

Hi, Ganesh Kanade!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

Ganesh Kanade · Apr 7, 2014

Hi SirJB7,

Thanks for the support, pls help me on the second issue i.e. file path. I want save the pdf file at differentt locations whenever i run the above code. Pls advise how to accomplish it.

And there is one more problem raised, how do I save pdf file with
security settings? I want create pdf file as readable or only printable.
Is it possible.

SirJB7 · Apr 8, 2014

Hi, Ganesh Kanade!

Check this:
http://msdn.microsoft.com/en-us/library/office/ff836441(v=office.15).aspx
Nothing about magic, just F1 key over the export word.

Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.
The same from the VBA editor, you can place the cursor (keyboard, so click, don't hover with mouse) on any VBA reserved word and press F1 to access the same type of help.

About your last question, the answer is implicit in the posted link.

Regards!

PS: If you don't happen to find it, if you don't find any parameter that let you specify PDF file type security features, well, it's because you can't do that from Excel.

Ganesh Kanade · Apr 9, 2014

Hi SirJB7,

I found the below code for what I am looking for but it is quite different, I am unable put it at right place in your code.

The below code take the file name from the different cells and open the save as dialog box along with file name in the File Name field. But it allows to save the file only in .xls format, I want the file to be saved in .pdf format.
I tried changing .xls to .pdf in the below code but it resulted in currupted file.

Code:

Sub SaveFile()
    Dim NameFile As Variant
        With Worksheets("SO1")
        NameFile = .Range("M3") & "_" & .Range("C11") & "_" & .Range("B22") & ".xls"
        End With
        NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Desktop\tickets\" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")
        If NameFile = False Then
        MsgBox "File not saved"
        Else
        ThisWorkbook.SaveAs Filename:=NameFile
        End If
End Sub

Can you help me to combine these codes?

Many Thanks in Advance,

GK

SirJB7 · Apr 9, 2014

Hi, Ganesh Kanade!

No, I can't help you with the combination of the 2 codes as each code performs a very different task, both incompatible between them.

The 1st code exports the active sheet (and only that one) to a file in .PDF format, while no action is taken with the Excel workbook. The 2nd code saves the workbook (i.e., all the worksheets and co.) as a new Excel file taking the new file name from many cells.

That's to say, the last code does nothing regarding your original requirement.

Regards!

rumshar · Apr 11, 2014

Hi Ganesh,
If you want saveas filedialog to open everytime you print the sheet, I would suggest you to use activsheet.printout.

With Regards
Rudra

Ganesh Kanade · Apr 12, 2014

Hi Rudra,

Code:

Sub SaveAsPDF()
'Saves active worksheet as pdf using concatenation
'of A1,A2,A3

Dim fName As String
With ActiveSheet
    fName = .Range("A1").Value & .Range("A2").Value & .Range("A3").Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\My Documents\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End Sub

Thanks for the suggestion. Can you pls advise where do I put this in the above code. Sorry I am very novice to VB.
It 'd be of great help.

Thanks in advance.
GK

rumshar · Apr 14, 2014

Hi GK,
Please go through this link.
http://msdn.microsoft.com/en-us/library/office/ff838253(v=office.15).aspx.
to Print Activesheet

Code:

Sub PrintActiveSheet()
ActiveSheet.PrintOut
End Sub

To Print All the Sheets of Workbook

Code:

Sub PrintAllSheets()
Activeworkbook.printout
End Sub

To Print Selected Range Only

Code:

Sub PrintSelectedCells()
Selection.printout
End Sub

With Regards
Rudra

Ganesh Kanade · Apr 16, 2014

rumshar said:
Hi GK,
Please go through this link.
http://msdn.microsoft.com/en-us/library/office/ff838253(v=office.15).aspx.
to Print Activesheet

Code:

Sub PrintActiveSheet() ActiveSheet.PrintOut End Sub

To Print All the Sheets of Workbook

Code:

Sub PrintAllSheets() Activeworkbook.printout End Sub

To Print Selected Range Only

Code:

Sub PrintSelectedCells() Selection.printout End Sub

With Regards
Rudra

Hi Rudra,

Thanks for the suggestion but this is not what I was looking for.

Thanks
GK

Ganesh Kanade · Apr 23, 2014

Ganesh Kanade said:
Hi Rudra,

Thanks for the suggestion but this is not what I was looking for.

Thanks
GK

SirJB7 said:
Hi, Ganesh Kanade!

No, I can't help you with the combination of the 2 codes as each code performs a very different task, both incompatible between them.

The 1st code exports the active sheet (and only that one) to a file in .PDF format, while no action is taken with the Excel workbook. The 2nd code saves the workbook (i.e., all the worksheets and co.) as a new Excel file taking the new file name from many cells.

That's to say, the last code does nothing regarding your original requirement.

Regards!

Hi SirJB7,

I finally found the below code for my requirement..

Code:

Dim FName As Variant
    FName = Application.GetSaveAsFilename( _
        InitialFileName:=Range("E3").Value & " - " & Range("B9").Value, _
        FileFilter:="PDF files, *.pdf", _
        Title:="Export to pdf")
    If FName <> False Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
    End If

Thanks
GK

SirJB7 · Apr 23, 2014

Hi, Ganesh Kanade!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

Ganesh Kanade · May 5, 2014

SirJB7 said:
Hi, Ganesh Kanade!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

Hi SirJB7,

Thanks.

Need little help to make above code little more valuable.
The above code creates the pdf files without any warnings such as "File already exists. Do you want to replace it?".
Can you pls help me to get this warning message.

I tried "Application.DisplayAlerts" but it is not working. Or may be i dont know where exactly place this "Application.DisplayAlerts" in the code.

Pls help.

Thanks and Regards
GK

Deepak · May 5, 2014

Hope this help!

Code:

Dim Q As Variant
Dim FName As Variant

FName = Application.GetSaveAsFilename( _
        InitialFileName:=Range("E3").Value & " - " & Range("B9").Value, _
        FileFilter:="PDF files, *.pdf", _
        Title:="Export to pdf")

If FName = False Then Exit Sub

    If Dir(FName) <> "" Then
        Q = MsgBox("File already exists.  Overwrite?", vbQuestion + vbYesNo, "Overwrite?")
            If Q = vbNo Then Exit Sub
    End If
   
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True

Ganesh Kanade · May 6, 2014

Deepak said:

Hope this help!

Code:

Dim Q As Variant
Dim FName As Variant
 
FName = Application.GetSaveAsFilename( _
        InitialFileName:=Range("E3").Value & " - " & Range("B9").Value, _
        FileFilter:="PDF files, *.pdf", _
        Title:="Export to pdf")
 
If FName = False Then Exit Sub
 
    If Dir(FName) <> "" Then
        Q = MsgBox("File already exists.  Overwrite?", vbQuestion + vbYesNo, "Overwrite?")
            If Q = vbNo Then Exit Sub
    End If
  
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True

Hi Deepak,

That's awesome. The code is working like a charm & I am almost done with it, need little more help.

When I click "No" on the msgbox it directly exiting the save as dialog box, instead of exiting, is it possible to return to the SaveAsDialog box keeping the file name as it is, so that user can do the minor change in the file name and then save the file?

Thanks a ton in advance.

GK

Deepak · May 6, 2014

Ganesh Kanade said:
Hi Deepak,

....

..... to the SaveAsDialog box keeping the file name as it is,......and then save the file?

.....

GK

This might help in regard what you are looking for!!!

Code:

Dim Q As Variant
Dim FName As Variant
L1:
FName = Application.GetSaveAsFilename( _
        InitialFileName:=Range("E3").Value & " - " & Range("B9").Value, _
        FileFilter:="PDF files, *.pdf", _
        Title:="Export to pdf")

If FName = False Then Exit Sub

    If Dir(FName) <> "" Then
        Q = MsgBox("File already exists.  Overwrite?", vbQuestion + vbYesNo, "Overwrite?")
            If Q = vbNo Then GoTo L1
    End If
   
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True

Ganesh Kanade · May 6, 2014

Deepak said:

This might help in regard what you are looking for!!!

Code:

Dim Q As Variant
Dim FName As Variant
L1:
FName = Application.GetSaveAsFilename( _
        InitialFileName:=Range("E3").Value & " - " & Range("B9").Value, _
        FileFilter:="PDF files, *.pdf", _
        Title:="Export to pdf")
 
If FName = False Then Exit Sub
 
    If Dir(FName) <> "" Then
        Q = MsgBox("File already exists.  Overwrite?", vbQuestion + vbYesNo, "Overwrite?")
            If Q = vbNo Then GoTo L1
    End If
  
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True

Hi Deepak,

That's unbelievable...!!! Giga thanks for the suggestion...

That completes my current task.

Thanks
GK

Deepak · May 6, 2014

Glad it helped.

We are happy to help you.

Ganesh Kanade · May 7, 2014

Deepak said:
Glad it helped.

We are happy to help you.

Hi Deepak,

Added little twist to the code with vbYesNoCancel.. Happy to share it.

Just wanted to know what is this L1: function called ?

Code:

Dim FName As Variant
    Dim Q As Variant
      
L1:
        FName = Application.GetSaveAsFilename( _
        InitialFileName:=Range("H3").Value & " - " & Range("B9").Value, _
        FileFilter:="PDF files, *.pdf", Title:="Export to pdf")
      
            If FName = False Then Exit Sub
            If Dir(FName) <> "" Then
          
            Q = MsgBox(Range("H3").Value & " - " & Range("B9").Value & " -This file already exists." & vbNewLine & _
            vbNewLine & "Do you want to replace it?", vbYesNoCancel + vbExclamation + vbDefaultButton1, "Confirm Save As")
          
            If Q = vbCancel Then Exit Sub
            If Q = vbNo Then GoTo L1
          
            End If
          
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True

Thanks
GK

Deepak · May 7, 2014

Ganesh Kanade said:
Hi Deepak,

Added little twist to the code with vbYesNoCancel.. Happy to share it.

Just wanted to know what is this L1: function called ?
.........

This called level/line's name.

http://www.vbaexpress.com/forum/showthread.php?39352-Call-Sub-GoTo-Point

https://groups.google.com/forum/#!topic/microsoft.public.excel.programming/piHyOB3xot0.

Ganesh Kanade · May 8, 2014

Deepak said:
This called level/line's name.

http://www.vbaexpress.com/forum/showthread.php?39352-Call-Sub-GoTo-Point

https://groups.google.com/forum/#!topic/microsoft.public.excel.programming/piHyOB3xot0.

Thanks once again Deepak.

Regards
GK

VBA to save page as .pdf, name to come from cell values

New Member

Excel Ninja

New Member

Excel Rōnin

New Member

Excel Rōnin

New Member

Excel Rōnin

New Member

Excel Rōnin

Member

New Member

Member

New Member

New Member

Excel Rōnin

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member