0

I have wrote a code to print excel file to .PDF file with the page setups parameters.And also it eliminates the need of having a prompt dialog box also.

But I need to know if I need to name the .PDF file as same as the excel file name with below code but not the same destination path.As an example:= if excel file name is "Quality Report 1411185623689" This file is generated by a system therefore its name is changed everyday. How do I solve this?

 Sub Save_As_PDF()
With ActiveSheet.PageSetup
     .Orientation=xlLandscape
     .Zoom=16

End With
ActiveSheet.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 FileName:="C\:Desktop\Reports\Same as excel file name", _
 Quality:=xlQualityStandard, _
 IncludeDocProperties:=False, _
 IgnorePrintAreas:=False, _
 OpenAfterPublish:=True

Exit Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
Nilusha M.
  • 57
  • 1
  • 13
  • 1
    You can name the PDF whatever you want to. What name do you want to give to the PDF? Will it vary each time? Is it a derivative of the generated name? – alowflyingpig Nov 14 '18 at 03:58
  • 1
    This is a duplicate. See this [SO question](https://stackoverflow.com/questions/38847014/save-pdf-file-automatically-in-new-folder) – GMalc Nov 14 '18 at 04:06
  • @alowflyingpig ,I would really appreciate answering my question where I got stuck.Yes,my system generated excel file name will vary everyday such as "Quality Report 141256522356" like this some digits included in its name.so those digits will vary everyday when this file is generated by the system.I need to print the pdf with the same name of excel file. – Nilusha M. Nov 14 '18 at 05:23

1 Answers1

2

Untested, but assuming you want to name the PDF the same as the Excel file (ignoring file extension), but in a different folder (say some folder/directory called "C\:Desktop\Reports\" for example):

Option explicit

Sub SaveAsPDF()

Dim folderPath as string
folderPath = "C\:Desktop\Reports\" ' Change to whatever folder, but make sure it ends with a \

If len(dir$(folderPath, vbDirectory)) = 0 then
Msgbox("'" & folderPath & "' is not a valid/existing directory. Abandoning export. Code will stop running now.")
Exit sub
End if

Dim Filename as string
Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1) & ".pdf"

With ActiveSheet.PageSetup
     .Orientation=xlLandscape
     .Zoom=16
End With
ActiveSheet.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 FileName:=folderPath & filename, _
 Quality:=xlQualityStandard, _
 IncludeDocProperties:=False, _
 IgnorePrintAreas:=False, _
 OpenAfterPublish:=True

Exit Sub
chillin
  • 4,391
  • 1
  • 8
  • 8
  • 1
    Sorry, there was an extra `.` in my answer, causing invalid syntax. Try again if you need to. – chillin Nov 14 '18 at 05:29
  • In your code this part is displayed me in red color and saying Syntax Error Filename = left$(Thisworkbook.name, instrrev(Thisworkbook.name, ".", -1, vbbinarycompare) -1). & ".pdf" How do I solve this? – Nilusha M. Nov 14 '18 at 05:32
  • 1
    Get rid of the `.` before the `&` -- or just copy-paste the code in my answer (I have fixed it there) and try again. – chillin Nov 14 '18 at 05:35