• 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

salmonchild

Member
Excel warriors,

A cheeky request for a bit of scripting- could anyone please provide me with VBA code to save the current active sheet as a .pdf in a defined network drive, and for the name of the file to be a combination of the text in cells A1 A2 and A3?

I have managed through recording a macro and then tinkering with it to achieve the first two but naming the .pdf I just cannot work out.

If you could help you would be a superstar!



salmon
 

Luke M

Excel Ninja
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
 

salmonchild

Member
Cheers Luke, if I know the range of cells that from the page, e.g. A1:K30 is there another line I can put in to make that the 'active sheet' for the save.

It is always awesome to see how clean the VBA can be if you know what you are doing- I usually search for the different components or record bits and try to stitch them together. Mostly it works but it looks like Frankenstein's monster!
 

Luke M

Excel Ninja
Not sure I understand what you mean...do you now want to use the active sheet for the pdf, or you want the cells that generate the name to come from a different spot? if the latter:
Code:
Sub SaveAsPDF()
 
 
Dim fName As String
With Worksheets("My Sheet")
    fName = .Range("A1").Value & .Range("A2").Value & .Range("A3").Value
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\My Documents\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
End Sub
 

salmonchild

Member
This has been working brilliantly for over a year - props to Luke for the code.

My requirements have changed a little now and instead of a .pdf of the active sheet I would like it to save as a workbook in .xlsx format- is there a tweak to this code that can change the file type? I did some searching and THIS details making a copy of the sheet and saving as a new workbook but I can't reconcile the two to keep the file name generation from concatenating A1, A2 and A3.

Help gratefully received.
 

Deepak

Excel Ninja
This might be...

Code:
Option Explicit

Sub SaveAsWB()
Dim fName As String
With Worksheets("My Sheet")
    fName = .Range("A1").Value & .Range("A2").Value & .Range("A3").Value

.Copy
    ActiveWorkbook.SaveAs "C:\My Documents\" & fName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close

End With

End Sub
 

Luc

New Member
Hi Luke,

Thanks for your advice. I have tried to implement, but unsuccessfully. Trying to save my worksheet to PDF using cell F5 value as filename. I keep getting the "Compile error: Syntax error" message. I am working with Excel 2013, and using the following code:

Code:
Sub Save_PDF()
'Saves active worksheet as pdf using rep name

Dim fName As String
With ActiveSheet
    fName = .Range("F5").Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:/Users/LANGELU3/Desktop/M1_2016_Payout_Sheets/" & fName,_
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End Sub
Can you please point me to where the error is?
Thanks, Luc
 

blairski10

New Member
What if I wanted to run this Save to PDF on a loop? I have a dashboard that needs to populate and then save to a PDF for about 700 clients. Using a key cell (Let's say it's A1 on a different sheet than the dashboard), how would I incorporate this code into what Luke has already come up with?
 

tim220225

New Member
Luke I know this is an old post that I came across. I used your code for my project and it worked great. How can I amend the code to check for a duplicate file in the save folder and prompt me to overwrite the file or to change the name? Should the file not exist the code just needs to continue running.

Thanks in advance.

Tim


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
 

vletm

Excel Ninja
tim220225
As a new member, You should reread Forum Rules
Start a new post every time you ask a question, even if the theme is similar.
 
Top