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

 

[NVRensburg]

Thank you so much, I will need to work on that as I have 700 different company's so will need to make 700 folders each. But I'll come back to you and let you know how it goes. You're a star!

 

[emacjake]

Glad i could help!

you can test this first with 1 company before you make 700 folders, just any new company you use will need a folder first before you use the macro.

you could rearrange the file path however you like, you can just play around with the formula in cell AA3, just remember the folder names have to match the file path your formula creates

 

[aetayar08]

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

Great forum, a lot of interesting information. Currently I am trying to create a "Save to PDF" button that creates the file name from multiple cells however I do not want it to save to a predefined folder. I want the user to be able to pick the folder it saves to. Anyway to do this? My current code is below:

Sub SavePDF()
    Dim strFileName As String
    strFileName = Application.GetSaveAsFilename( _
        FileFilter:="PDF Files (*.pdf),*.pdf", _
        Title:="Save As PDF")
    If strFileName <> "False" Then
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Range("R2").Value _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
    End If
End Sub

TIA

 

[setjimmywet]

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

Hi,
I want to add password also in the pdf generated and this password should use cell value.

Can you pls help.

 

[alex_p]

Hi, Andrew!

I have a similar problem; I need the macro I put together (copied below), in addition to its current properties, to save a given number of pages, based on a cell reference ("AG2"). The excel sheet can only be between 1 to 6 pages long. Thank you for your consideration! Alex

Sub MakePDF()
'
' MakePDF Macro
'


'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Alex\Desktop\CropRecs" & Range("A2").Value & "_" & Range("AM2"), Quality:=xlQualityMinimum, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=1, To:=6, _
OpenAfterPublish:=False
End Sub

 

[Tessoconnor99]

Hi Please can you help?
I am trying to do a VBA script to enter onto a macro to enable file to be saved as pdf with “D3” as filename and saved in "C:/Filesfor University Students/" & Range("D3").Value – I have tried several times with above code but it keepsfailing please oh please could you assist…
many thanks in advance Tess

 

[MichMast]

I am trying to use the above in an Excel Spreadsheet with multiple print pages on the same tab. It works for the 1st page, but not the rest. What am I going wrong?

 

[Lux Aeterna]

Hello, I am using the code below, which works perfectly, but I'd like a small modofication, please. I'd like to take data from two cells for the file name (e.g. A1 and C5).

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=[COLOR=#ff0000][B]Range("A1").Value[/B][/COLOR] _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

thank you in advance.