• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Ganesh Kanade

New Member
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.?
 
Last edited by a moderator:
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...?
 
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
 
Last edited:
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!
 
Hi, Ganesh Kanade!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
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.
 
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.
 
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
 
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 Ganesh,
If you want saveas filedialog to open everytime you print the sheet, I would suggest you to use activsheet.printout.

With Regards
Rudra
 
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
 
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
 
Hi Rudra,

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


Thanks
GK

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
 
Hi, Ganesh Kanade!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top