Macro to Save as PDF - Using file name in cell

[becca74]

I am not a familiar with VBA language. I recorded a macro to save my file as PDF and assigned the macro to a button. Easy. Now I would like to change my macro to pause when the file is saving to allow the user to enter a file name. If that is not possible, I'd like to reference a cell to use as the file name. I have gone through other posts and tried changing my macro, but always get an error.
I am using Excel 2010. Any help would be appreciated. Following is my code for saving to pdf.

Sub SavePDF()
'
' SavePDF Macro
'

'
ChDir "D:\New folder\Documents\PMIC\Quote Converter\MA HO"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"D:\New folder\Documents\PMIC\Quote Converter\MA HO\MA HO Sharp Missed Quotes output.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub

 

[Andrew Poulsom]

If A1 on the active sheet contains the file name try:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("A1").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

 

[becca74]

Andrew,
Thank you so much. That works perfectly.

 

[ENAPo]

If A1 on the active sheet contains the file name try:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("A1").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

What would I need to add to designate a location to save?

Thanks in advance!

 

[Andrew Poulsom]

Example:

Filename:="C:/Test/" & Range("A1").Value

 

[ENAPo]

Example:

Filename:="C:/Test/" & Range("A1").Value

Perfect!! Thanks!!

 

[aatmik_m24]

Save Excel Macro Template as PDF with File Name as Cell Values and to special directory

Please help me with this as I've tried all my senses but couldn't make it finally I have to consult someone.
Urgent

MY REQUIREMENTS -
Save the following Invoicing Excel Template as a proper PDF Invoice
Save the PDF File to Directory - G:\Electronyx Bazaar\Invoice\
Select Cells Ranging from A1 to A54
Use File Name as Follows - Range("A9").Value & " Invoice# " & Range("F3").Value & " Order ID#" & Range("F9").Value & " Mobile No." & Range("C15").Value & " " & Range("F13").Value & "-" & Range("F11").Value

PLEASE DO NOT ALTER THE FILE NAME AS IT WILL HARD FOR ME TO CODE THIS SMALL LINE AGAIN.

My Existing Code (using ActiveX Button)

Public Sub SaveAsA1()​

ThisFile = Range("A1:A54").Value​

ActiveSheet.SaveAs Filename:=ThisFile​

End Sub​

​

Private Sub CommandButton1_Click()​

​

Application.DisplayAlerts = False​

template_file = ActiveWorkbook.FullName​

ActiveSheet.Range("A1:A54").Select​

​

fileSaveName = Application.GetSaveAsFilename( _​

InitialFileName:="G:\Electronyx Bazaar\Invoice\" + Range("A9").Value & " Invoice# " & Range("F3").Value & " Order ID#" & Range("F9").Value & " Mobile No." & Range("C15").Value & " " & Range("F13").Value & "-" & Range("F11").Value & ".pdf"​

If fileSaveName = False Then​

Exit Sub​

End If​

​

​

​

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("A9").Value & " Invoice# " & Range("F3").Value & " Order ID#" & Range("F9").Value & " Mobile No." & Range("C15").Value & " " & Range("F13").Value & "-" & Range("F11").Value & ".pdf"​

​

Application.DisplayAlerts = True​

End Sub
​

*IMPORTANT- PLEASE REPLY BACK THE CORRECT CODE IN SOME DIFFERENT COLOR SO THAT I CAN DIFFERENTIATE EASILY

THANKS in ADVANCE!!!

 

[Andrew Poulsom]

Re: Save Excel Macro Template as PDF with File Name as Cell Values and to special directory

In what way isn't your code working?

 

[aatmik_m24]

Re: Save Excel Macro Template as PDF with File Name as Cell Values and to special directory

In what way isn't your code working?

Firstly , it does not save the PDF File with the desired name. Infact , it saves the file with the name of the Workbook.
Secondly , the code has error whenever I click on the button it gives SYNTAX Error.

and highlights the line " ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlTypePDF, " in red.

 

[Andrew Poulsom]

Re: Save Excel Macro Template as PDF with File Name as Cell Values and to special directory

I don't see that statement in the code that you posted. However, you need to assign the Value of a single cell to ThisFile rather than the Value of a multicell range like Range("A1:A54").