Tweak create PDF code

[Access Beginner]

Hi All,

The below code creates a PDF from a named range in cell A55 on the "Menu" sheet and saves this to a folder based in a value in cell A52 on the "Menu" sheet.

What I'd like to achieve, is that if the value of cell A115 on the "Menu" sheet is "Zone" then output the PDF file to a path in cell A116 on the "Menu" sheet.

Sub PDFPrinttest()
    Dim ReportPath As Variant
    Dim ReportName As Variant
    Dim ToPrint As Variant
  
   
    Set ReportPath = Sheets("Menu").Range("A32")
    Set ReportName = Sheets("Menu").Range("A52")
    Set ToPrint = Sheets("Menu").Range("A55")
    
    Worksheets("IOverview5").Activate
    
    With ActiveSheet
    .PageSetup.PrintArea = ToPrint
    .ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ReportPath & ReportName & ".PDF", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    End With
    

End Sub

 

[fhqwgads]

i think when you save as pdf it goes to the current working directory which by default is documents or something

to change it add ChDir followed by a path like "S:\Finance\F19\03 SEP 2018" to your code

if cell A116 has a path in it then ChDir Range("A116").value should work hopefully

Sub PDFPrinttest()
    Dim ReportPath As Variant
    Dim ReportName As Variant
    Dim ToPrint As Variant
  
   
    Set ReportPath = Sheets("Menu").Range("A32")
    Set ReportName = Sheets("Menu").Range("A52")
    Set ToPrint = Sheets("Menu").Range("A55")

ChDir Range("A116").value
    
    Worksheets("IOverview5").Activate
    
    With ActiveSheet
    .PageSetup.PrintArea = ToPrint
    .ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ReportPath & ReportName & ".PDF", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    End With
    

End Sub

 

[Access Beginner]

Thanks for your response. It's almost what I was after. If there isn't a value in cell A116, the I'd like the path to be cell A32. I could create a drop down and have Zone and No Zone, so cell A116 would either have "Zone" or "No Zone". If A116 equals "Zone" then path is A116 and if it's "No Zone", then the path is A32

i think when you save as pdf it goes to the current working directory which by default is documents or something

to change it add ChDir followed by a path like "S:\Finance\F19\03 SEP 2018" to your code

if cell A116 has a path in it then ChDir Range("A116").value should work hopefully

Sub PDFPrinttest()
    Dim ReportPath As Variant
    Dim ReportName As Variant
    Dim ToPrint As Variant
  
   
    Set ReportPath = Sheets("Menu").Range("A32")
    Set ReportName = Sheets("Menu").Range("A52")
    Set ToPrint = Sheets("Menu").Range("A55")

ChDir Range("A116").value
    
    Worksheets("IOverview5").Activate
    
    With ActiveSheet
    .PageSetup.PrintArea = ToPrint
    .ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ReportPath & ReportName & ".PDF", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    End With
    

End Sub