Save defined range as pdf where I choose the subfolder to place the file

[sspatriots]

I thought I was done for today and then came across this issue with another macro I'm trying to make work. I want to print a defined range on my currently Active Worksheet to a ".pdf" file so that the range fits all on one page. I need it to take me to a defined file path that has many folders beneath it that I need to choose from to save it under.

For example:

H:\PO Block History\ is the parent folder that has about 10 subfolders beneath it with the name of a year.

H:\PO Block History\2013
H:\PO Block History\2014
H:\PO Block History\2015
H:\PO Block History\2016
H:\PO Block History\2017
H:\PO Block History\2018
H:\PO Block History\2019
H:\PO Block History\2020
H:\PO Block History\2021
H:\PO Block History\2022

When the code gets to the point where I need to select one of these folders to put it in I need to be able to choose where it goes. I need the ".pdf" file name to come from the values in cells K6, L6 & D20 (with a space between L6 & D20).

So the file name may look like "2022-999 123 Bachelor Quarters"
Where,
K6 = 2022
L6 = -999
D20 = 123 Bachelor Quarters

The defined range is B1:L60


Below is what I have found to start with...


Sub SaveRangeAsPDF()

'Create and assign variables
Dim saveLocation As String
Dim rng As Range
Dim ws As Worksheet

Set ws = ActiveSheet
saveLocation = "H:\PO Block History\"
Set rng = ActiveSheet.Range("B1:L60")

'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

End Sub

 

[sspatriots]

Forgot to mention in the FWIW section - if you Set an object variable it is commonly considered sage advice to destroy it and reclaim memory before the procedure terminates. That is, right before your exit sub in this procedure:
Set wb = Nothing

Same for any other Set variable. Common to have

YourLineLableHere:
On Error Resume Next
Set wb = Nothing
Exit Here

when it's possible that the timing of the error caused the object variable to be empty. Sometimes you can raise another error by trying to Set to Nothing an object variable which never got instantiated.

Wow, thanks for the explanation and ideas. Especially that last post. It actually makes sense to me and that isn't easy to do. LOL. Going to give this a try tomorrow morning when I get in the office.

 

[sspatriots]

I tried to insert the Msgbox part into my code, but I must be doing something wrong because it doesn't like the line that says "result = Msgbox "File exist. Click yes to overwrite; No to cancel""

'user can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")





If Not Dir(strPath) = "" Then 'means this file was found in the specified folder
   result = Msgbox "File exists. Click yes to overwrite; No to cancel"
    If result = vbNo Then Exit Sub 'If yes chosen, this line is ignored and saving process should run
End If





'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

 

[Micron]

Whoops! I forgot that when MsgBox function elements must be (wrapped) when a value is to be returned! Try

result = Msgbox ("File exists. Click yes to overwrite; No to cancel")

Any chance you could reduce the copious number of empty lines and line continuation charactes in your code? I have a small laptop screen

Don't you find this easier to follow (and code window doesn't even need scrolling- at least not for me)?

 'user can enter name and select folder for file
myFile = Application.GetSaveAsFilename  (InitialFileName:=strPathFile,  FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")

If Not Dir(strPath) = "" Then 'means this file was found in the specified folder
   result = Msgbox "File exists. Click yes to overwrite; No to cancel"
   If result = vbNo Then Exit Sub 'If yes chosen, this line is ignored and saving process should run
End If

'export to PDF if a folder was selected
If myFile <> "False" Then
   wsA.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
   IgnorePrintAreas:=False, OpenAfterPublish:=False
   'confirmation message with file info
   MsgBox "PDF file has been created: " & vbCrLf & myFile
End If

 

[sspatriots]

Definitely looks shorter. I fixed that line to put the parenthesis around the quoted part. When I run the code I don't see a "Yes" or "No" button. I'm just getting an "OK" button to choose.

 

[sspatriots]

I just inserted the following on that result line: , vbYesNo + vbQuestion before the last parenthesis and it seems to be working. It works great for the second code that I put in post #18. The pop-up won't come up in the first code in that post. I think I missed something somewhere when I was adding everything in there to that first one.

 

[sspatriots]

Figured out what was making the first code in #18 not to work. I took the word "Not" out of If Dir(strPath). I think I'm good now. Thanks so much for all your help on this.

 

[Micron]

You'd think that by now I could write a simple msgbox function. I forgot the options. Doesn't help to try to answer questions while doing other things.
Glad you figured it out. One thing I like about this thread is how much effort you put into it. So many people seem to want a fish handed to them. You know how to fish!