Auto Save Excel File as PDF

[Granoldad]

I have a button assigned to a macro to auto save the file and this works great.
I need to have the macro save the file as .xlsm and .pdf.
How can I modify this to include the function to save as .xlsm and .pdf?


Sub SaveMyWorkbook()
' First range B5 is company acroynym.
' Second range I4 is one letter.
' Thrid range B3 is name.
' Forth range is WE date.
' Fifth range is file format.
' Should save as example XYZ-X-for-John Doe.-WE-112616.xlsm
' strFolderPath = "C:\1\"
' Sheet1.Range("K3").Value & _
Dim strPath As String
Dim strFolderPath As String
strFolderPath = Sheet1.Range("F2").Value
strPath = strFolderPath & _
Sheet1.Range("A2").Value & "-" & _
Sheet1.Range("B2").Value & "-for-" & _
Sheet1.Range("C2").Value & "-WE-" & _
Sheet1.Range("D2").Value & ".xlsm"
ActiveWorkbook.SaveAs Filename:=strPath
End Sub

[Granoldad]

I tried changing: Sheet1.Range("D2").Value & ".xlsm" to: Sheet1.Range("D2").Value & ".pdf" although did not work. Once the file is created you can not open it as pdf file.

[Kenneth Hobs]

Use the macro recorder, it is your friend. You will see that Excel files are exported or published to pdf format. If you Save the XLSM file with the correct file format, the file extension is not needed. Here again, macro recording shows the syntax. For the "saveas" to pdf, file extensions are not needed either. There are exceptions to this but knowing this should make it easier to code.

Tip: build your drive:\path\Filename string into a variable and then use Debug.Print or MsgBox() to show the resultant string value. The former method puts a run's Debug.Print's into VBE's Immediate Window. Select the View menu to show it if needed.

[Granoldad]

Yes I know how to manually save a file as pdf and record it.
My problem is this will be a template and each person will have a different path, name, etc... so I put these values in the worksheet and need to use them to save just as I did with .xlsm


Dim strPath As String
Dim strFolderPath As String
strFolderPath = Sheet1.Range("F2").Value
strPath = strFolderPath & _
Sheet1.Range("A2").Value & "-" & _
Sheet1.Range("B2").Value & "-for-" & _
Sheet1.Range("C2").Value & "-WE-" & _
Sheet1.Range("D2").Value & ".xlsm"

[Kenneth Hobs]

Let me see if I can simplify what I said. DO NOT USE A FILEXTENSION.


strFolderPath = Sheet1.Range("F2").Value
strPath = strFolderPath & _
Sheet1.Range("A2").Value & "-" & _
Sheet1.Range("B2").Value & "-for-" & _
Sheet1.Range("C2").Value & "-WE-" & _
Sheet1.Range("D2").Value
This is used for SaveAs and the ExportAsFixedFormat.


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath

[Granoldad]

This is what I found that works!

Sub SaveMyPDF()
'
' SaveMyPDF Macro
'
On Error GoTo ende
' First range B5 is acroynym.
' Second range I4 is one letter.
' Thrid range B3 is name.
' Forth range is WE date.
' Fifth range is file format.
' Should save as example ***-X-for-John Doe.-WE-112616.pdf
' strFolderPath = "C:\1. Aquitas\Time and Expenses\"
' Sheet1.Range("K3").Value & _

Dim strPath As String
Dim strFolderPath As String
strFolderPath = Sheet1.Range("F2").Value
strPath = strFolderPath & _
Sheet1.Range("A2").Value & "-" & _
Sheet1.Range("B2").Value & "-for-" & _
Sheet1.Range("C2").Value & "-WE-" & _
Sheet1.Range("D2").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPath

ende:
Set app = Nothing
Set itm = Nothing

End Sub