How to save a spreadsheet that was converted to a pdf to a file location

[Pookiemeister]

The goal I am trying to achieve is to convert a spreadsheet to a pdf, save the pdf to a specified folder, and email that pdf all with a click of a button. However I am stuck on the second part, saving the new converted pdf to a specified file location. Here's what I have so far, everything works great until the last line of code.

ActiveWorkbook.SaveAs filename:=sPath

I get Runtime error '1004': Application defined or object-defined error.

Private Sub btnSave_Email_Click()
    Dim sPath As String
    Dim sFilename As String
    
    sPath = "C:\Users\UserName\Desktop\Saved PDF TEST\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
    ActiveWorkbook.SaveAs filename:=sPath
    
End Sub

Thank You

 

[Fluff]

What do you want to call the file?

 

[Trevor G]

You haven't assigned a file name.

Sub btnSave_Email_Click()
    Dim sPath As String
    Dim sFilename As String
    
    sPath = "C:\Users\UserName\Desktop\Saved PDF TEST\myPDFFile.pdf" 'Assign the file name
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath
    
End Sub

 

[Pookiemeister]

I'm sorry. I can't believe I missed that. I must of gotten ahead of myself. The excel filename will stay the same and converted PDF name will be the same as the excel filename. So I added this line of code to define the filename and inserted it after the "sPath = ...". Here's the updated code.

Private Sub btnSave_Email_Click()
    Dim sPath As String
    Dim sFilename As String
    
    sPath = "C:\Users\Jacob B Cutler\Desktop\Saved PDF TEST\"
    sFilename = ThisWorkbook.Name
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
    ActiveWorkbook.SaveAs filename:=sPath
    
End Sub

sFilename = ThisWorkbook.Name

 

[Fluff]

In that case use

Private Sub btnSave_Email_Click()
    Dim sPath As String
    Dim sFilename As String
    
    sPath = "C:\Users\Jacob B Cutler\Desktop\Saved PDF TEST\"
    sFilename = ThisWorkbook.Name
    ActiveSheet.ExportAsFixedFormat xlTypePDF, sPath & sFilename

End Sub

 

[Pookiemeister]

When the line of code below is ran the pdf is automatically saved as the same filename. I just need to figure out how to save that pdf to that location.

 

[Fluff]

Are you talking about my code?
If so it will save it in the specified location.

 

[Pookiemeister]

Fluff, When I put your code in, I receive a Compile Error Syntax error. Don't I need to add a SaveAs somewhere in there?

Thank You

 

[Pookiemeister]

Nevermind. I left the word "Type:=" in the code. I also noticed that Excel Macro is also add to the file location. I forgot to mention that the Excel File macro is a Template and does not need to be added to the file folder only the PDF. Is this possible to do?

 

[Fluff]

Please try the code I posted as-is. Without any changes or amendments.