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!
 
Thanks and still the complete form is not printed like setting default printer(Adobe PDF) which prints the complete report in one page.

Is there a way?
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
the above code does not use default printer, but pdf printer inside excel, I tried with a code like the following without success
Code:
Private Sub CommandButton3_Click()
     With Application
        sPrinter = .ActivePrinter
        fileOK = .Dialogs(xlDialogPrint).Show
    End With
 
    If fileOK = True Then
       me.PrintForm
'       Application.ActivePrinter = sPrinter
    End If
End Sub
 
Last edited:
Upvote 0
When I use your new code, save as works but the prints database.
Thanks.
I strongly believe it is possible to automate the save as file name of the userform , but don't know how?.
 
Upvote 0
put in a normal module

Code:
Private Const HWND_BROADCAST As Long = &HFFFF&
Private Const WM_WININICHANGE As Long = &H1A
 
Private Declare Function SendNotifyMessage Lib "user32" Alias "SendNotifyMessageA" ( _
  ByVal hwnd As Long, _
  ByVal msg As Long, _
  ByVal wParam As Long, _
  lParam As Any) As Long
 
Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" ( _
  ByVal pszPrinter As String) As Long
 
Public Sub ChangePrinter(NewPrinter As String)
 
  SetDefaultPrinter NewPrinter
  Call SendNotifyMessage(HWND_BROADCAST, _
    WM_WININICHANGE, _
    0, ByVal "windows")
 
End Sub

then in the userform module

Code:
Private Sub CommandButton3_Click()
Dim OldPrinter As String
  Dim 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)
  ChangePrinter NewPrinter
  Me.PrintForm
  ChangePrinter OldPrinter

End Sub

"on " is for english version of Windows, otherwise you have to translate it

<code class="vb spaces"></code>
 
Last edited:
Upvote 0
Hi Mr.Patel,
Thanks for time taken for the code.
I have created new module (not class) and pasted your first half of code
In the print command have pasted the second half of the code.
Now it is prompting for the printer and prints as pdf if selected. File path same as worksheet path. this is ok.
But file name as "Microsoft Visual Basic"
I don't find the code for auto save the file name from the text box value.
any suggestion?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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