VBA save as PDF based on cell values for path and file name

M4TVD

New Member
Joined
Mar 23, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
I am trying to save a PDF of the sheet that i am working on (only the sheet selected and not the whole workbook)
the file path is written in cell "M12"
the file name that I want to create for the sheet is "E5" then "E6" then "B6"

I have this code but it is not working, think i have typos somewhere in it:


VBA Code:
Public Sub Save_Sheets_As_PDF()

    Dim PDFfile As String
    Dim currentSheet As Worksheet
    
    With ActiveWorkbook
        PDFfile = .ActiveSheet.Range("m12").Value
        If Right(PDFfile, 1) <> "\" Then PDFfile = PDFfile & "\"
        PDFfile = PDFfile & .ActiveSheet.Range("e5" & "E6" & "b6").Value
        Set currentSheet = .ActiveSheet
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        currentSheet.Select
        MsgBox "Created " & PDFfile
    End With
    
End Sub
 

M4TVD

New Member
Joined
Mar 23, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Thankyou!

I had to change the code slightly, The cell "B6" that i was trying to ref for the file name was a =now() to give the current time, I have found that i can not use this as it is not compatible with files names! so I have changed it to a different cell "M13 that contains the date outputted as a number only in the form of YYMMDD.

VBA Code:
Public Sub Save_Active_Sheet_As_PDF()

    Dim PDFfile As String
    
     With ActiveWorkbook.ActiveSheet
     
        PDFfile = .Range("M12").Value
        If Right(PDFfile, 1) <> "\" Then PDFfile = PDFfile & "\"
        PDFfile = PDFfile & .Range("E5").Value & .Range("E6").Value & .Range("M13").Value
        .ExportAsFixedFormat Type:=xlTypePDF, filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        MsgBox "Created " & PDFfile & ".pdf"
    End With
    
End Sub


this works great! thankyou.

I want to add to this now it is working. If I already have a file of the name that will be created by the values in Cells "E5", "E6", and "M13", it throws an error, how can i avoid this? auto add a version number to the end of the file name? if the file name already exists?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

M4TVD

New Member
Joined
Mar 23, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
I have tidied up the Code to give me a slightly more organised (and easier to read in the folder!) and fixed the issue i found with the already existing file name by adding the contents of cell "M13" into the file name structure.
M13 has the following formula in it to give me the time in hhmm format
M13 = =(HOUR(NOW())&MINUTE(NOW()))

VBA Code:
Public Sub Save_Active_Sheet_As_PDF()

    Dim PDFfile As String
    
     With ActiveWorkbook.ActiveSheet
     
        PDFfile = .Range("M12").Value
        If Right(PDFfile, 1) <> "\" Then PDFfile = PDFfile & "\"
        PDFfile = PDFfile & .Range("E5").Value & "_" & .Range("E6").Value & "_" & .Range("M13").Value & "_" & .Range("M14").Value
        .ExportAsFixedFormat Type:=xlTypePDF, filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        MsgBox "Created " & PDFfile & ".pdf"
    End With
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top