VBA to save Userform as PDF

[campysuks]

Hi:

Looking to save a Userform as a PDF when user clicks a command button. Here's what I was able to find on the net but nothing for a User form:

Dim newFile As String, fName As String
 ' Don't use "/" in date, invalid syntax
fName = TextBox1.Value
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
 ' Change directory to suit
ChDir _
"D:\My Documents\Desktop"
UserForm.ExportAsFixedFormat xlTypePDF, Filename:=newFile

I suspect the error is on the last line since I changed "Worksheet" to "UserFrom".

Any help appreciated...

 

[Teeroy]

I haven't tried anything like this but you could possibly set the default printer to be a PDF printer and use Me.PrintForm in the userform code.

 

[campysuks]

Thanks for the response.

I don't want to do it by setting the default printer. I was watching a youtube video that has PrintForm capability to print to file. Visual Basic Print Form Tutorial - YouTube
But it looks like I have to install some software.

Is there a way to do that via VBA?

 

[Teeroy]

Print to file will give you a .prn file which can't be directly converted to PDF (you'd need a PDF distiller). Try the following method for xl2007+ that I had filed away. I've made a few minor adaptions that I can't test (I've only got access to xl2003 at home) but it should be OK. In the UserForm code module;

Option Explicit
 
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Const VK_SNAPSHOT = 44
Const VK_LMENU = 164
Const KEYEVENTF_KEYUP = 2
Const KEYEVENTF_EXTENDEDKEY = 1

'Adapted from Kenneth Hobs
'at http://www.ozgrid.com/forum/showthread.php?t=157677

Private Sub CommandButton1_Click()
'change to your button name
    Dim pdfName As String
    Dim newWS As Worksheet
     
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
     
    DoEvents 'Otherwise, all of screen would be pasted as if PrtScn rather than Alt+PrtScn was used for the copy.
     
    Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
    newWS.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False
    pdfName = ActiveWorkbook.Path & "\" & Me.Name & " " & Format(Now, "yyyy-mmm-dd") & ".pdf"
    newWS.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=pdfName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    newWS.Delete
    Unload Me
End Sub

Let me know how you go.

 

[cigo yee]

thanks Teeroy, but it is not workable . my windows is 64bit.

 

[ARUNCHENNAI]

Print to file will give you a .prn file which can't be directly converted to PDF (you'd need a PDF distiller). Try the following method for xl2007+ that I had filed away. I've made a few minor adaptions that I can't test (I've only got access to xl2003 at home) but it should be OK. In the UserForm code module;

Option Explicit
 
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Const VK_SNAPSHOT = 44
Const VK_LMENU = 164
Const KEYEVENTF_KEYUP = 2
Const KEYEVENTF_EXTENDEDKEY = 1

'Adapted from Kenneth Hobs
'at http://www.ozgrid.com/forum/showthread.php?t=157677

Private Sub CommandButton1_Click()
'change to your button name
    Dim pdfName As String
    Dim newWS As Worksheet
     
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
     
    DoEvents 'Otherwise, all of screen would be pasted as if PrtScn rather than Alt+PrtScn was used for the copy.
     
    Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
    newWS.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False
    pdfName = ActiveWorkbook.Path & "\" & Me.Name & " " & Format(Now, "yyyy-mmm-dd") & ".pdf"
    newWS.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=pdfName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    newWS.Delete
    Unload Me
End Sub

Let me know how you go.

Teeroy, Thank you very much.
Its working for me.

 

[diddi]

i use the default printer method which works well as long as you dont need to print in landscape as there is no printpreview control. i use bullzip as my pdf printer which supports vba and is free.

 

[jbeet]

Can someone tell me how I would update the code so that it would work on 64 bit? This is exactly what I need but haven't figure out how to update. Thanks!

 

[arianfar]

Can someone tell me how I would update the code so that it would work on 64 bit? This is exactly what I need but haven't figure out how to update. Thanks!

this code work for all 32 & 64

Option Explicit
 
 
 #If VBA7 Then
'// 64bit API Declarations
Private Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
#Else
'// 34 bit declarations
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
#End If



'Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Const VK_SNAPSHOT = 44
Const VK_LMENU = 164
Const KEYEVENTF_KEYUP = 2
Const KEYEVENTF_EXTENDEDKEY = 1

'Adapted from Kenneth Hobs
'at http://www.ozgrid.com/forum/showthread.php?t=157677

Private Sub CommandButton1_Click()
'change to your button name
    Dim pdfName As String
    Dim newWS As Worksheet
     
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
     
    DoEvents 'Otherwise, all of screen would be pasted as if PrtScn rather than Alt+PrtScn was used for the copy.
     
    Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
    newWS.PasteSpecial Format:="Bitmap", link:=False, DisplayAsIcon:=False
    pdfName = ActiveWorkbook.Path & "\" & Me.Name & " " & Format(Now, "yyyy-mmm-dd") & ".pdf"
    newWS.ExportAsFixedFormat Type:=xlTypePDF, _
        FileName:=pdfName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    newWS.Delete
    Unload Me
End Sub

 

[Teeroy]

Untested but try replacing

#If VBA7 Then

with

#If Win64 = 1 Then

I've always had some issues using the #VBA7 pre-compiler check.