VBA macro to print to .pdf

[gmittar]

Hi all,

I looked up a macro on this site to print an excel sheet to pdf, but I'm running into some issues when using it. Now, the post is 8 years old, so I suspect some of the commands have changed since it was posted. The error that I'm getting is a compile error "Assignment to constant not permitted" on the "vbYesNo" on the last messagebox line.

Thanks for your help!

Sub Make_PDF()
' Create and save .pdf
Dim pdfName As String, FolderName As String, FullName As String
' change to cell with file name that you want
pdfName = Range("h3").Text
' change to cell with folder name that you want
FolderName = Range("H4").Text
'change directory to where you want it saved
If Not DirExists("C:\Users\mittar.khalsa\Desktop\test" & FolderName) Then MkDir "C:\Users\mittar.khalsa\Desktop\test" & FolderName
'change directory to where you want it saved
FullName = "C:\Users\mittar.khalsa\Desktop\test" & FolderName & "" & pdfName & ".pdf"
If MsgBox("Please confirm that name and location is correct: " & FullName & ". - " & " Is it correct?", vbYesNo + vbQuestion, "Confirm File Name and Location") = vbNo Then Exit Sub
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName _
, Quality:=xlQualityStandard, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
vbYesNo = MsgBox("Would you like to open the folder where the invoice was saved?" _
, vbYesNo + vbQuestion, "Open Folder?")
Select Case YesNo
Case vbYes
'change directory to where you want it saved
myval = Shell("explorer C:\Users\mittar.khalsa\Desktop\test" & FolderName, 1)
Case vbNo
End Select
End Sub

 

[mrhstn]

Hi, your variable appears to be called YesNo and not vbYesNo

Dim YesNo As VbMsgBoxResult


YesNo = MsgBox("Question to ask?", vbYesNo)
If YesNo = vbYes Then
    'Do YES things
Else
    ' Do NO things
End If

 

[gmittar]

I'm sorry, I'm not following. I'm very new to VBA, what do you suggest that I change?

Thank you.

 

[mrhstn]

The vbYesNo you highlighted in red, change that to YesNo.

 

[mrhstn]

This will also cause a problem

FullName = "C:\Users\mittar.khalsa\Desktop\test" & FolderName & "" & pdfName & ".pdf"

There is no path separator between the folder name and file name, it should be like...

FullName = "C:\Users\mittar.khalsa\Desktop\test\" & FolderName & "\" & pdfName & ".pdf"

So there is a number of instances that you need to change.

 

[gmittar]

Thanks again mrhstn, I made the changes and I'm still getting the same error, with YesNo highlighted in the debugger.

I'm wondering if that messagebox statement should be written differently, as it seems to be written oddly as compared to the messagebox statement earlier in the macro. I scrapped that part of the macro and the rest works just fine, but I can see the value in opening the folder that the pdf was saved to.

Thoughts?

 

[mrhstn]

Try this

Sub Make_PDF()
' Create and save .pdf
Dim pdfName As String, FolderName As String, FullName As String
Dim DeskPath As String
' Added variables
Dim FinalPath As String
Dim DoExport As VbMsgBoxResult
Dim DoOpenDir As VbMsgBoxResult
Dim myval


DeskPath = Environ("USERPROFILE") & "\Desktop"
' change to cell with file name that you want
pdfName = Range("H3").Value
' change to cell with folder name that you want
FolderName = Range("H4").Value
FinalPath = DeskPath & "\test\" & FolderName


'change directory to where you want it saved
If Len(Dir(FinalPath)) = 0 Then
    MkDir FinalPath
End If


'change directory to where you want it saved
FullName = FinalPath & "\" & pdfName & ".pdf"


DoExport = MsgBox("Please confirm that name and location is correct: " & FullName & ". - " & " Is it correct?", vbYesNo + vbQuestion, "Confirm File Name and Location")


If DoExport = vbNo Then
    Exit Sub
Else
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName _
    , Quality:=xlQualityStandard, IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False
    
    DoOpenDir = MsgBox("Would you like to open the folder where the invoice was saved?", vbYesNo + vbQuestion, "Open Folder?")
    If DoOpenDir = vbYes Then
        'change directory to where you want it saved
         myval = Shell("explorer " & FinalPath, 1)
    Else
        'do nothing (or whatever the result of hitting No would be)
    End If
End If
End Sub