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

 

[aatmik_m24]

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

For which value should I Assign?
A1 or A54 ??


I have tried this one also..

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", _
fileFilter:=" (*.pdf), *.pdf")

If fileSaveName = False Then
Exit Sub
End If



ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlTypePDF

Application.DisplayAlerts = True
End Sub

 

[aatmik_m24]

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

please tell me which one would work or is better ?

 

[Andrew Poulsom]

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

It would be:

ThisFile = Range("A1").Value

But you can't use SaveAs to save as PDF. You have to use ExportAsFixedFormat.

 

[allExcell]

Example:

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

Hi Andrew, I've faced a similar problem and not sure how to resolve this. Below given is my thread from this forum:
http://www.mrexcel.com/forum/excel-...ssue-my-code-save-files-name-reference-2.html

Using my code I'm copying data into a Template file (.xlsx) and then trying to save that with a name taken from a reference cell from the original file. Here if I try to give .Range("A1") it picks value from any sheets, but it has to pick from Sheet1 only.

If you can look into my thread and help me out it would be great. As since last couple of days I'm stuck with this part of the code, else everything is working fine.

Please help.

 

[mholt2110]

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

Hi I have a similar problem and have tried these macros above but to no avail.

the code that I have recorded is

"Sub PrintToPDF1()
'
' PrintToPDF1 Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\martin\Desktop\MSite\Invoices & Timesheets\TEST.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub"

I would like to replace the file name "TEST" with that of the Cell Value (J9) but have had errors when using the above fix. Im wondering if the problem is that Cell (J9) is populated by using a custom number format consisting of ["M Site Ltd. - Invoice WE" dd-mm-yy] and the formula in the cell (J9) is =F13 which holds a live fomula for the invoicing date.

If someone could point me in the right direction It would be very much appreciated.

Kind regards
Martin

 

[mholt2110]

I have since tested other ways to populate Cell (J9) and it works but is there a way to insert the date in 02-03-14 format after a quoted reference? in this case I want all of my files to be saved as "M Site Ltd. - Invoice WE" and then the last day of the week that I am Invoicing for, but in the above format. all im getting is the microsoft code for that date ie 41700.

Thanks in advance.

Martin

 

[Andrew Poulsom]

Maybe try:

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\martin\Desktop\MSite\Invoices & Timesheets\" & Range("J9").Text & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

 

[markgilbo]

Had a very similar challenge to those above. I needed to produce 350 different confirmation forms, save them as PDFs and of course mail them to different addresses. All the info was contained in the same sheet. With the help of the macros above and a little adjustment, I pulled the whole thing off in an evening. You have my deepest heartfelt thanks. ...Mark

 

[mholt2110]

Thanks Andrew Ive used your code as below this has simplified the spreadsheet greatly, I would like to say thankyou for taking the time to help me.

Sub PrintToPDF()
'
' PrintToPDF Macro
'

'
ChDir "C:\Users\martin\Desktop\MSite\Invoices & Timesheets"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\martin\Desktop\MSite\Invoices & Timesheets\" & Range("L9").Text & Range("N9").Text & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

Kind Regards
Martin Holt

 

[emacjake]

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

Hi i am having the same problem as the original post in this thred,
I'm not very familiar with the vba language, but i have recorded a macro to save a purchase order as a pdf that does work,
But when trying to change the file name for each new purchase order i had added Range("F3").Value, like you suggested but it continues to give me an error

here's my current code

Sub SAVEPDF()
'
' SAVEPDF Macro
'
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\PANTHER\Department Share\Hotel\Hotel Stores\Hotel Inventory Live\Purchasing\Purchasing Orders History\Food 2014\Purchase Order Book Food NEW1.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub


any help would be greatly appreciated!!!