Macro to Save as PDF - Using file name in cell

becca74

New Member
Joined
Jun 22, 2012
Messages
6
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If A1 on the active sheet contains the file name try:

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

ENAPo

New Member
Joined
May 16, 2013
Messages
23
If A1 on the active sheet contains the file name try:

Rich (BB code):
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Example:

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

aatmik_m24

New Member
Joined
Aug 2, 2013
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Aug 2, 2013
Messages
6
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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").
 

Watch MrExcel Video

Forum statistics

Threads
1,122,952
Messages
5,599,033
Members
414,275
Latest member
Pungie

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