Given a pre-selected print area, the following macro by Ryan Wells prints to pdf and then points the user to name&save the pdf file into the XLSTART directory on local machine.

Please help me identify the line in the code where I can insert a path statement such that when the "save as" dialog box opens, the desired path (located in OneDrive) is present.


    Sub PrintSelectionToPDF()
'SUBROUTINE: PrintSelectionToPDF
'DEVELOPER: Ryan Wells
'DESCRIPTION: Print your currently selected range to a PDF

Dim ThisRng As Range
Dim strfile As String
Dim myfile As Variant

If Selection.Count = 1 Then
Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)
Set ThisRng = Selection
End If

'Prompt for save location
strfile = "Selection" & "_" _
& Format(Now(), "yyyymmdd_hhmmss") _
& ".pdf"

strfile = ThisWorkbook.Path & "\" & strfile
myfile = Application.GetSaveAsFilename _
(InitialFileName:=strfile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save as PDF")
If myfile <> "False" Then 'save as PDF
ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True


MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"

End If

End Sub

'Once you select the range and click OK, it will show a dialog box where you can select where you want to save the PDF.
'It will automatically pick the date and time from your system's clock and add it to the file name.

The folder is put as part of the InitialFileName parameter, like this:

strfile = "C:\mypath\Selection_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf"

Note that this in effect overrides and nullifies the following line which should be deleted:

strfile = ThisWorkbook.Path & "\" & strfile
|improve this answer|||||
  • Correct, but in the snippet provided, the path is prepended using ThisWortkbook.Path in the line strfile = ThisWorkbook.Path & "\" & strfile. Mention this only so the OP doesn't overlook that and wind up with 2 paths tacked together. – Yorik Jan 28 at 17:58
  • @Yorik: Right. I added a note that this line now becomes redundant. – harrymc Jan 28 at 18:05
  • Your code suggestions are perfect. One prob on my end re: path statement. There's something in my path statement that prevents the macro from navigating to the final sub-folder named "Quant PDF Dump" The full path statement = "C:\... 2019-2020\2019-20 2nd Semester\Quant PDF Dump" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf" _ & Format(Now(), "yyyymmdd_hhmmss") _ & ".pdf" I tried modifying the path syntax without success. Please advise. Thanks! – WyoWindStorm Jan 29 at 15:42
  • What is the error? Could you MsgBox the full path and add it here? – harrymc Jan 29 at 15:49
  • Thanks for responding, harrymc. Not really an error. Using the path statement, the macro drills down as far as "\2019-20 2nd Semester" folder to provide the desired save location. But I want to path to go to the final sub-folder named "\Quant PDF Dump". That folder is spec'd in the full path statement. Do I need a syntax change or something added to "\Quant PDF Dump" folder id to get to that folder in the same window? – WyoWindStorm Jan 29 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.