0

In a previous post, SE'er harrymc suggested this line to my code so that the user is allowed to name the file but the directory save path is fixed.

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

I incorporated the above code but the save directory still defaults to \This PC\Documents.

I have spent several hours reading various SE and other VBA forums but have not found a solution. Your expertise is much appreciated.

    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)
Else
Set ThisRng = Selection
End If

'Prompt for save location
strfile = "C:\Users\DJB Laptop\OneDrive .....\_Qualtrics\_2019-2020\2019-20 2nd Semester\Quant PDF Dump\\Selection_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf"
'"C:\Users\DJB Laptop\OneDrive - Albany County School District One\_Qualtrics\_2019-2020\2019-20 2nd Semester\Quant PDF Dump\" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf" _

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

Else

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

End If

End Sub
2

I don't want to admit it, but I gotta admit it... I'm becoming a dinosaur.

Back in my day, we we had to code in three feet of snow, up hill, both ways! But kids these days, they have it easy with all these new fangled wizkaboodles in the code that do things I don't understand. I can't even understand a file path anymore. For the life of me, I do not see how this will work:

"C:\Users\DJB Laptop\OneDrive .....\_Qualtrics\_2019-2020\2019-20 2nd Semester\Quant PDF Dump\\Selection_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf"

How do you use the ellipsis ..... without replacing them with an expanded path?

What do the two backslashes \\ do?

If you can teach me these things then I might be able to help you.


Getting comments about the ellipses but not the double backslashes so it seems my point was lost somewhere among the meandering rambling of an old man. To be explicitly clear:

The problem is definitely the double backslashes

Windows generally ignores a double backslashes, for example using them in the cmd.exe window had no effect; but I have tested and confirmed \\ is not ignored in VBA with this code:

Application.GetSaveAsFilename "C:\Test\\Fail.xlsx

Opens the window to the documents of the current user.

Application.GetSaveAsFilename "C:\Test\Success.xlsx

Properly follows the path.

An interesting takeaway, is that you can use this as a shortcut to the user documents folder

|improve this answer|||||
  • I think the OP omitted that part of the file path for privacy purposes? Or at least I'd hope so, really should have indicated that in some manner for clarity though. – Arctiic Feb 5 at 1:51
  • @Arctiic 1 Yeah, that'd make sense.... Still gonna have a problem here though [...] PDF Dump\\Selection [...] – ProfoundlyOblivious Feb 5 at 3:07
  • Arctiic is correct about the ellipsis indicating omitted portion of path for privacy. I apologize for not indicating the purpose of the ellipsis. – WyoWindStorm Feb 5 at 18:42
  • @WyoWindDtorm No worries on that - but note the double backslashes. This should be ignored by windows but may be causing a problem. Nice name by the way. – ProfoundlyOblivious Feb 5 at 18:51
  • As an also-old person, I recall in vb6 and windows that double slashes are ignored, but just tried it via Windows Explorer (win10 64 bit) and it failed. Used admin CMD prompt, works. – Yorik Feb 5 at 19:35

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.