Saving PDF with macro to user defined directory

[fantabulator]

Hello all,

I have found a macro to use msoFileDialogFolderPicker to have the user choose a file path. I can make the file path appear in a cell. I want the file path to stay constant unless changed by the user and I don't want the user to have to choose a file path each time they save a report. What I need is to know how to modify the following code to accept cell path as part of the file path name. I can't seem to get the syntax right.

Dim fName As String
With ActiveSheet
fName = .Range("J3").Value & .Range("J28").Value & .Range("K28").Value

End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\My Documents\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

I would like the "C:\My Documents" portion to come from the information inputted by the user and stored in cell L 28 (cell 28,9)

 

[Smallman]

Hi fantabulator

Do a test on the pdf code below. It should work but if now provide a file please. So we can see what is in j3 j28 and K28.

Option Explicit
Sub CreatePDF()
  Dim str As String
  Dim fName as String

  str = "C:\My Documents\"
  fName = [j3] & [J28] & [K28]
  ThisWorkbook.ExportAsFixedFormat xlTypePDF, str & fName & ".pdf"
End Sub

You can find an article on this here:

SavetoPDF

Take care

Smallman

 

[fantabulator]

The code I had above saved to PDF as well. What I need to figure out is how to make the str ="c:\My Documents\" user driven. I am unsure of what I need to change to make that str = L28 where the user inputted folder information is.

BTW j3, j28 and k28 are student name, _report, and #(for number of reports)

 

[Smallman]

This bit:

What I need to figure out is how to make the str ="c:\My Documents\" user driven.

You said you wanted it static not user driven. The opposite of what you just said.

 

[fantabulator]

Sorry for any confusion. What I meant was that I currently have a separate a macro to have the user input the desired directory where all the pdf reports will be saved...I currently have that directory data saved in l28. Once it is provided, I wish to make the user provided directory static unless they use the initial macro again.

I don't want to adjust the macro to change directories for every new class that might use this. I also don't want to request a directory for each saved report. A teacher may print off 10 or more reports for three classes. I want to automate this as much as possible to save time.

Thanks for your time and input.