Userform print pdf with file name and path

sureshtrb

Board Regular
Joined
Mar 24, 2013
Messages
106
I am not an expert in vba and with the forum guide created userform and works well.
I want to save the userform in pdf with textbox file name which i am trying for the past 2 days and unsuccessful.
advice requested.
my code
Code:
Dim OldPrinter As String, NewPrinter As String    OldPrinter = Left$(Application.ActivePrinter, InStrRev(Application.ActivePrinter, "on ") - 2)
    Application.Dialogs(xlDialogPrinterSetup).Show
    NewPrinter = Left$(Application.ActivePrinter, InStrRev(Application.ActivePrinter, "on ") - 2)


    
    Dim sFilename As String
    Dim cname, tagNumber, customer, newHour, newMinute, newSecond, waitTime
    Dim fpath As String
    
    With rvcform
            newHour = Hour(Now())
            newMinute = Minute(Now())
            newSecond = Second(Now()) + 2
            waitTime = TimeSerial(newHour, newMinute, newSecond)
            Application.Wait waitTime
            cname = NO.Value
            tagNumber = TAGN.Value
            customer = CUSTO.Value
            sFilename = customer & "-RVC-" & cname & "-" & tagNumber & ".pdf"
            fpath = "C:\Users\VSD\Desktop\RVC_Printed_Copies\"
     
            Application.ScreenUpdating = True
            SendKeys fpath & sFilename & "{ENTER}", False
            MsgBox fpath & sFilename
            .PrintForm
        End With

Everytime when print, it prompts for "Microsoft Visual Basic" as file name instead of the given file name.
Help please!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Code:
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

Private Sub CommandButton3_Click()
    Dim pdfName As String
'   ------------   Alt+PrtScn
    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.
     
    ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
    Range("A1").Select
    ActiveSheet.Paste
    pdfName = ActiveWorkbook.Path & "\" & Me.Name & " " & Format(Now, "yyyy-mmm-dd") & ".pdf"
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=pdfName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    Application.DisplayAlerts = False
    Worksheets(Worksheets.Count).Delete
   Application.DisplayAlerts = True
'    Unload Me
End Sub
 
Upvote 0
Tnamks pateM5,
Thanks for the effort. Tried your method and its printing the excel database. I need to print the filled userform.
I can able to print filled userform, if I have selected the Adobe as default printer.
 
Upvote 0
I tested this code with your workbook without selecting Adobe printer , it print only the filled userform. I'm using Excel 2010
 
Upvote 0
I regret for my haphazard reply and inconvenience.
I tried on my original format works but printing the form partially. My actual form size is Width 610.5 and Height 838.5.
Once again regret for not providing proper details.
Need your support to adjust the size.
further, will it be possible to print directly using the Adobe printer by saving the file name without the snapshots?
Thanks again

edit:
sometimes creates a new worksheet and the previous copy saves in the clipboeard. Possible to clear the clipboard whine unloading the form?
 
Last edited:
Upvote 0
my solution uses printscreen to capture image, if the userform is larger than screen the code does not run.
can you attach the new file again ?
 
Last edited:
Upvote 0
Code:
    Range("A1").Select
    ActiveSheet.Paste
    [COLOR=#ff0000]ActiveSheet.PageSetup.Orientation = xlLandscape[/COLOR]
    pdfName = ActiveWorkbook.Path & "\" & Me.Name & " " & Format(Now, "yyyy-mmm-dd") & ".pdf"
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=pdfName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    Worksheets(Worksheets.Count).Delete
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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
Back
Top