SaveAsPDF to Folder

[rlindfors]

New Excel user.
I used the code below (found on internet) to create a button that would save the active worksheet as a pdf to the same folder from which the excel file was opened. It worked for a time and then started saving the pdf to a different folder. I cannot determine the reason why it changed or how to fix it...

Sub SaveAsPDF()

Dim File_Name As String
Dim Destination As String
Application.DisplayAlerts = False
Destination = "/Users/skarekroe/Documents/_Documents/_Southern/_MIATC/_Courseware/_Courseware Developement/WorkPages/"
File_Name = Range("AY1").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
filename, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


End Sub

 

[Ruddles]

You're setting up the name of the destination folder in Destination but then you're not using it. Try:-

File_Name = Destination & Range("AY1").Value & ".pdf"

or:-

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
Destination & filename, Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

But only one of these!

 

[Ruddles]

Incidentally, instead of using Destination in the .ExportAsFixedFormat statement, you could use ThisWorkbook.Path (since Excel knows where the workbook was opened from).

Hope this helps.

 

[rlindfors]

Thanks for the quick reply.

I tried each of the first two methods but had the same result.

The file path it's actually using is: /Users/skarekroe/Library/Containers/Microsoft Excel/Data/Documents. There are similarities but...

Using a similar macro called SaveAs, the file is successfully saved to the source folder as a spreadsheet named after the specified range. There is, I think, something in the portion of the code telling it to save as a pdf that is the culprit (?).

Can you give me a little more direction in the use of ThisWorkbook.Path that you mentioned above? Maybe that'll be the trick.

 

[rlindfors]

This is the successful SaveAs code:

Sub SaveAs()

Dim File_Name As String
Dim Destination As String
Application.DisplayAlerts = False
Destination = "/Users/skarekroe/Documents/_Documents/_Southern/_MIATC/_Courseware/_Courseware Developement/WorkPages/"
File_Name = Range("AY1").Value & ".xlsm"
ActiveWorkbook.SaveAs Destination & File_Name
Application.DisplayAlerts = True


End Sub