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

 

[senvelthil]

Perfectly working....
How to save 2 sheets in single PDF.

 

[Andrew Poulsom]

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!!!

How did you add Range("F3").Value, what error did you get and what does F3 contain?

 

[emacjake]

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

I figured it out!!
I literally copied Range("F3").Value, staight from this page. after Filename:=
I Had to delete this line "\\PANTHER\Department Share\Hotel\Hotel Stores\Hotel Inventory Live\Purchasing\Purchasing Orders History\Food 2014\Purchase Order Book Food NEW1.pdf" _
and then in F3, i entered
="G:\Hotel\Hotel Stores\Hotel Inventory Live\Purchasing\Purchasing Orders History\Food\"&TEXT($P$29,"yyyy")&"\"&MONTH($Q$29)&TEXT($Q$29," mmm")&"\"&$B$17&TEXT($B$21," dd-mm-yy")&" PO."&$D$8
which gives me

G:\Hotel\Hotel Stores\Hotel Inventory Live\Purchasing\Purchasing Orders History\Food\2014\11 Nov\HCWholesale 10-11-14 PO.321044

<colgroup><col style="width: 124pt; mso-width-source: userset; mso-width-alt: 6034;" width="165"> <tbody>
</tbody>

Which works briliantly! now the file same is dynamic in that it saves with the suppler, date, purchase order number, read straight from the purchase order itself, and also automatically saves it into files broken down into the year and then month.

 

[NVRensburg]

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

I figured it out!!
I literally copied Range("F3").Value, staight from this page. after Filename:=
I Had to delete this line "\\PANTHER\Department Share\Hotel\Hotel Stores\Hotel Inventory Live\Purchasing\Purchasing Orders History\Food 2014\Purchase Order Book Food NEW1.pdf" _
and then in F3, i entered
="G:\Hotel\Hotel Stores\Hotel Inventory Live\Purchasing\Purchasing Orders History\Food\"&TEXT($P$29,"yyyy")&"\"&MONTH($Q$29)&TEXT($Q$29," mmm")&"\"&$B$17&TEXT($B$21," dd-mm-yy")&" PO."&$D$8
which gives me

G:\Hotel\Hotel Stores\Hotel Inventory Live\Purchasing\Purchasing Orders History\Food\2014\11 Nov\HCWholesale 10-11-14 PO.321044

<tbody>
</tbody>

Which works briliantly! now the file same is dynamic in that it saves with the suppler, date, purchase order number, read straight from the purchase order itself, and also automatically saves it into files broken down into the year and then month.

Please can someone help me with the Syntax error I'm getting. I've used your macro above which consists of:

Sub SAVEPDF()
'
' SAVEPDF Macro
'
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= C:\Users\USER\Documents\Remittances in PDF\"&TEXT($B$5)&"\"&TEXT($J$17)&"\"&TEXT($I$17)&"\"&TEXT($I$55)
:=False, OpenAfterPublish:=False
End Sub

whereby B5 is the company name, J17 is the year, I17 is the month and I55 is the date on which it is paid, which i want as the filename. What have I done wrong?

 

[emacjake]

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

Please can someone help me with the Syntax error I'm getting. I've used your macro above which consists of:

Sub SAVEPDF()
'
' SAVEPDF Macro
'
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= C:\Users\USER\Documents\Remittances in PDF\"&TEXT($B$5)&"\"&TEXT($J$17)&"\"&TEXT($I$17)&"\"&TEXT($I$55)
:=False, OpenAfterPublish:=False
End Sub

whereby B5 is the company name, J17 is the year, I17 is the month and I55 is the date on which it is paid, which i want as the filename. What have I done wrong?

HI

i'll do my best to help,

Firstly i removed the file path formula from the macro itself and just used the Cell reference Range("AA3").Value in the macro after Filename:=, this way you can have the formula of to the side out of view and then just had the formula written on the worksheet,
you also need quotations around your formula ""
having the formula written on the worksheet will also show you the file path as you complete all these cells.
also using the Text formula for dates you need to specify the date format, as i did above for example TEXT($J$17,"yyyy"),
also these need to exactly resemble the names of the folders being saved into, these all need to be set up in advance or the macro will give you this error everytime

I hope this makes some sense

 

[NVRensburg]

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

Thanks so much for taking the time to reply! Ok I've created the folders up until the Remittance in PDF, then I assumed it created it's own folders based on the cell formats when running the macro after that? Would you have a chance to edit my macro and fix what I've done wrong as I'm not that clued up when it comes to VBA?

Sub SAVEPDF()
'
' SAVEPDF Macro
'
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= C:\Users\USER\Documents\Remittances in PDF\"&TEXT($B$5)&"\"&TEXT($J$17)&"\"&TEXT($I$17)&"\"&TEXT($I$55)
:=False, OpenAfterPublish:=False
End Sub

 

[emacjake]

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

Thanks so much for taking the time to reply! Ok I've created the folders up until the Remittance in PDF, then I assumed it created it's own folders based on the cell formats when running the macro after that? Would you have a chance to edit my macro and fix what I've done wrong as I'm not that clued up when it comes to VBA?

Sub SAVEPDF()
'
' SAVEPDF Macro
'
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= C:\Users\USER\Documents\Remittances in PDF\"&TEXT($B$5)&"\"&TEXT($J$17)&"\"&TEXT($I$17)&"\"&TEXT($I$55)
:=False, OpenAfterPublish:=False
End Sub

Do you want everything saved into the folder remittances in PDF or do you want more folders for each year and month?

 

[NVRensburg]

It would be nice if if could save in the remittance in PDF under company name folder first, then month. So eg. All the Unilever would be in the remittance I PDF/Unilever/2016/May or even just the company name as per what's in the cell

 

[emacjake]

Try the below macro, and on your work sheet in cell 'AA3' enter

="C:\Users\USER\Documents\Remittances in PDF\"&TEXT($B$5,)&"\"&TEXT($J$17,"YYYY")&"\"&TEXT($I$17,)&"\"&TEXT($I$55,"dd-mm-yy")


Which should give you the file path, C:\Users\USER\Documents\Remittances in PDF\Coca Cola\2016\November\01-06-16

(You may need to play around with the date formatting to give you the right file path name for the year and month)

so inside the 'remittances in PDF' folder you will need a folder for each company and inside those you will need a folder for each year and inside those you will need a folder for each month.
(these need to be made in advance for every company or the macro will not work)

make sure the names of the folders are spelled exactly the same as your file path.

The files will just be saved with the date paid as the name




Sub SAVEPDF()
'
' SAVEPDF Macro
'
'

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


End Sub

 

[emacjake]

Also i forgot to add if there is more than one for each company on the same date, the macro will break as it can't save the same file name twice,

so you may need to add another parameter to the file name,

you could use


Sub SAVEPDF()
'
' SAVEPDF Macro
'
Range("AA4").Value = Range("AA4").Value + 1

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


End Sub



Start by entering the number 1 in cell AA4 and this will increase by one every time you run the macro

And then on the end of your file path add &" "&AA4