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
 

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
When i try and run this code, i get a runtime error '1004'
 

M4TVD

New Member
Joined
Mar 23, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
I have also tried this code:

VBA Code:
Sub SaveAsPDF()
'Saves active worksheet as pdf using concatenation
'of E5,E6,B6

Dim fName As String
With ActiveSheet
    fName = .Range("E5").Value & .Range("E6").Value & .Range("B6").Value
    .ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            "C:\Users\'InsertFolderStructureHere'\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End Sub

this is not working for me either
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
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"
Try this macro:
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("B6").Value
        .ExportAsFixedFormat Type:=xlTypePDF, filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        MsgBox "Created " & PDFfile
    End With
    
End Sub
 

M4TVD

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

ADVERTISEMENT

I have tried your suggested code but I get this message:


1617545442051.png
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
@M4TVD
change this
VBA Code:
PDFfile = PDFfile & .Range("E5").Value & .Range("E6").Value .Range("B6").Value
to this
Code:
PDFfile = PDFfile & .Range("E5").Value & .Range("E6").Value & .Range("B6").Value
there is a typo about ampersand
 

M4TVD

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

ADVERTISEMENT

Thankyou, i changed this, and got the following:
1617547121090.png

When I Debug:
1617547158577.png
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Sorry, I omitted the ampersand.

Does M12 contain a valid path?

Is the concatenation of E5, E6 and B6 a valid file name?
 

M4TVD

New Member
Joined
Mar 23, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Yes M12 contains a valid path,

I want the file name to be what is written in cells E5, E6 and B6.

no PDF file exists yet.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Is the concatenation of E5, E6 and B6 a valid file name?

This works for me, with test values in the cells. It creates C:\Temp\123.pdf.
VBA Code:
Public Sub Save_Active_Sheet_As_PDF()

    Dim PDFfile As String
    
    With ActiveWorkbook.ActiveSheet
        .Range("M12").Value = "C:\Temp\"
        .Range("E5").Value = "1"
        .Range("E6").Value = "2"
        .Range("B6").Value = "3"
        
        PDFfile = .Range("M12").Value
        If Right(PDFfile, 1) <> "\" Then PDFfile = PDFfile & "\"
        PDFfile = PDFfile & .Range("E5").Value & .Range("E6").Value & .Range("B6").Value
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        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