Forum Discussion
Help modifying an existing Macro that saves as PDF
I have been using a Macro for sometime that can print (saving as a PDF) a specific page, as a specific file name in whatever folder you are working with the program in. I would like to modify the M...
Same problem. Excel is making me change it. See below. Won't let me put the ":". Only ","
Sub Printchangeorder()
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Change Order "
'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
Range(AO1, BA47).ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
'MsgBox ("New file: " & strPathFile)
End Sub
Sub Printchangeorder()
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Change Order "
'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
Range(AO1, BA47).ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
'MsgBox ("New file: " & strPathFile)
End Sub
- Hey Hans,
One last question. Currently this Macro names this "Change Order" then the file name of the workbook. I want to use this Macro on more then one sheet in this book. How can I get it to use the active sheet name vs. "Change Order". Example sheet one is Change Order 1. Sheet two is Change Order 2... etc. I just realized each time I print/save the PDF, it overwrites the original PDF.Change the part
strPath = ThisWorkbook.Path If strPath = "" Then strPath = Application.DefaultFilePath End If strPath = strPath & "/Change Order " 'Getting the file name dynamically strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1)) 'Creating the new file location and name strFile = strName & ".pdf" strPathFile = strPath & strFileto
strPath = ThisWorkbook.Path If strPath = "" Then strPath = Application.DefaultFilePath End If strPath = strPath & "/" 'Getting the file name dynamically strName = ActiveSheet.Name 'Creating the new file location and name strFile = strName & ".pdf" strPathFile = strPath & strFile- Hans, Again, THANK YOU! I was so close.... Modified what you gave to fit my application, but what you showed me, got me to see what I was doing wrong. I was deleting the strPath completely.
strPath = strPath & "/"
'Getting the file name dynamically
strName = ActiveSheet.Name & "-" & Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
- Hans,
Cannot thank you enough. That was it. Works prefect!!! You sir have a wonderful week!