Using a macro to print to PDF & changing the file name

FashionGal

New Member
Joined
Mar 24, 2019
Messages
5
Hello All,
I am new to macros and so I need a bit of help. I am trying to save an excel file to PDF using a macro and I want to change the file name to be based off of a particular cell. I have gotten the macro to save the file, however it asks me to name the file. Is there a way to make it automatically save to the folder I listed in the macro and use a particular cell value to automatically name the file. I also do not want the PDF to automatically open once it has been created. Any help would be greatly appreciated.

Here is the macro I currently have:

Sub PTPDF()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Dim pdfName As String, FolderName As String, FullName As String
pdfName = Sheets("Award Sheet").Range("CXID").Text
FullName = "I:\Student Awarding\2019-20\APPEALS + pdfName & .pdf"
ActiveSheet.Shapes.Range(Array("Button 2744")).Select
Selection.OnAction = "PTPDF"
Range("V17").Select
Application.WindowState = xlNormal
End Sub
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,576
.
Start with this macro ...

Code:
Option Explicit


Sub SavePDF()
Dim Path, FileName1 As String
Path = "C:\Users\My\Desktop\" '<-- edit path as required.
FileName1 = Sheet1.Range("C1").Value '<-- edit cell target as required.
ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
End Sub
 

FashionGal

New Member
Joined
Mar 24, 2019
Messages
5
.
Start with this macro ...

Code:
Option Explicit


Sub SavePDF()
Dim Path, FileName1 As String
Path = "C:\Users\My\Desktop\" '<-- edit path as required.
FileName1 = Sheet1.Range("C1").Value '<-- edit cell target as required.
ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
End Sub


Hello Logit,
I tried that macro, and it saved the file however it did not save it in the correct folder and it did not save it with the correct file name. When it saved the file, the file was named "APPEALS + pdfName & .pdf" and it was not saved in the correct file location. The file location should be "I:\Student Awarding\2019-20\APPEALS" and the file name should be the information listed from cell T3. Any further thoughts?
 

FashionGal

New Member
Joined
Mar 24, 2019
Messages
5
Here is the code I was able to create based off of the previous suggestion. I was able to get the file to be automatically saved in the correct folder, however it does not name the file correctly, as it names it "pdfName". I want the file to be named from the data that is located in cell "T3" in a general format. Any help or advice is greatly appreciated.


Sub PTPDF()


Dim pdfName As String, FolderName As String, FullName As String
pdfName = Sheets("Award Sheet").Range("T3").Text
Path = "I:\Student Awarding\2019-20\APPEALS\ pdfName"
ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & FileName1 & ".pdf", OpenAfterPublish:=False
End Sub
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,576

ADVERTISEMENT

.
Code:
Sub PTPDF2()


Dim pdfName As String, FullName As String, Path As String


pdfName = Sheets("Award Sheet").Range("T3").Value
Path = "I:\Student Awarding\2019-20\APPEALS\"
FullName = Path & pdfName & ".pdf"


Application.ScreenUpdating = False


ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName, OpenAfterPublish:=False


Application.ScreenUpdating = True


End Sub
 

FashionGal

New Member
Joined
Mar 24, 2019
Messages
5
It worked when I ran it on one excel sheet that was located on my desktop, however when I went to add the macro to a similar excel sheet that was located in a different location on my computer it doesn't work.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,576
.
Be certain you are verifying the specific names listed in the macro :

Code:
Sub PTPDF2()


Dim pdfName As String, FullName As String, Path As String


pdfName = Sheets([B][COLOR=#ff0000]"Award Sheet"[/COLOR][/B]).Range([B][COLOR=#ff0000]"T3"[/COLOR][/B]).Value
Path = [B][COLOR=#ff0000]"I:\Student Awarding\2019-20\APPEALS\"[/COLOR][/B]
FullName = Path & pdfName & ".pdf"


Application.ScreenUpdating = False


ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName, OpenAfterPublish:=False


Application.ScreenUpdating = True


End Sub

The same names (in RED BOLD) must exist on the other computers.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,791
Messages
5,524,900
Members
409,610
Latest member
db321

This Week's Hot Topics

Top