Save excel as PDF in specific location

Question

I have the following line of code which is supposed to take an excel spreadsheet, and save it as a PDF file.

It was working correctly and saving the PDF files in the same directory that the file was opened in, but suddenly it does not seem to be saving the PDF files in the right place.

Sometimes it saves it and sometimes it does not. Is there a way I can update my code to include a specific location or the location that the file was opened in?

Sheets("Mortgage Charts").Activate
Range("L2").Select
With Sheets("Mortgage Charts")
    .ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="Mortgage Scorecard " & Format(.Range("L2"), "mm-dd-yyyy"), _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
End With

I would also suggest trying to avoid `Activate` and `Select` if possible (just to form good coding habits). I've always found [this post useful](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) in how to be explicit in different situations. — Dan, Mar 18 '16 at 16:19

score 1 · Answer 1 · edited Mar 18 '16 at 15:30

1

I would start by changing the OpenAfterPublish:= from false to true. Then look at the document properties when its open to find out where it is saving.

I would imagine if there are no errors, it is saving to an unknown folder. If you find the file is indeed saving, I would add the directory name to the Filename:= argument. You can also set the filepath to a variable with

x = Application.ActiveWorkbook.Path

it will return the path of your workbook as a string.

edited Mar 18 '16 at 15:30

Poul Kruijt

69,713
12
145
149

answered Mar 18 '16 at 15:22

John Paukovits

11
1

Can i suggest editing `Applicaiton.Activeworkbook.Path` in case anyone decides to copy and paste ;) – Tim Wilkinson Mar 18 '16 at 15:25

Tim Wilkinson · Answer 2 · 2016-03-18T15:46:46.453

Using what John suggested in your code.

Dim thisPath As String, docName As String

thisPath = Application.ActiveWorkbook.Path
docName = thisPath & "\Mortgage Scorecard " & Format(Range("L2"), "mm-dd-yyyy")

Sheets("Mortgage Charts").Activate
Range("L2").Select
With Sheets("Mortgage Charts")
    .ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=docName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
End With

EDIT

If you want to open the PDF after saving, add this after End With:

openDoc = docName & ".pdf"
ActiveWorkbook.FollowHyperlink openDoc

Save excel as PDF in specific location

2 Answers2