0

I got a template with button to convert excel to PDF and save it to specific location. Also, it will move my excel file to new location and kill the old file. I am open to a better approach to do so or any suggestion to improve this. 

Private Sub PDF_Click()
Dim strFilename     As String
Dim rngRange        As Range
Set rngRange = Worksheets("MRO").Range("C6")
strFilename = rngRange.Value & Format(Now(), " mm-dd-yyyy hh_mm AM/PM")
Sheets(Array("MRO")).Select
Sheets("MRO").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
"\\*change this to your path*\" & strFilename & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

Path = "\\*change this to your path*\"
Dim thisWb As Workbook
Set thisWb = ActiveWorkbook
MyOldName = ActiveWorkbook.FullName
MyNewName = ActiveWorkbook.Name

ActiveWorkbook.SaveAs fileName:=Path & "\" & MyNewName
Kill MyOldName
ActiveWorkbook.Close
End Sub
Community
  • 1
  • 1
Ryan Nguyen
  • 25
  • 1
  • 9
  • https://stackoverflow.com/a/31694603/4539709 – 0m3r Apr 12 '18 at 16:07
  • It does not move the xl file, it re-saves it...also you may not require the selects. `Sheets("MRO").ExportAsFixedFormat Type:=xlTypePDF, fileName:=...`..see here for ways to move a file [Move file to another folder](http://www.xlorate.com/excel-questions.html#Copy%20Files%20to%20a%20different%20folder) You also would not require a sheet array for just one sheet. – Davesexcel Apr 12 '18 at 16:09
  • https://stackoverflow.com/a/10717999/4539709 – 0m3r Apr 12 '18 at 16:09
  • Ask a question and answer it yourself, but in a proper way: https://stackoverflow.blog/2011/07/01/its-ok-to-ask-and-answer-your-own-questions/ – M-- Apr 12 '18 at 20:17

0 Answers0