Excel VBA打印宏添加.SendKeys及实现双面打印、左上角装订功能求助

Hey there! Let's tackle that printing problem you're having. The SendKeys "%fpr" approach is notoriously flaky because it depends on Excel's UI being in exactly the right state—so we'll ditch that and use two more reliable methods instead: either letting users configure settings via Excel's built-in print dialog, or hardcoding those preferences directly in VBA.

Why Your SendKeys Failed #

SendKeys is a brittle tool: it relies on Excel's menu shortcuts staying consistent across versions, and requires the Excel window to have focus. If anything is off (like a background dialog stealing focus), it won't work. Let's use better alternatives.

Method 1: Let Users Choose Print Settings via the Standard Dialog #

This is the most user-friendly approach—users get the familiar print dialog where they can select a printer, set duplex, choose binding, and adjust any other preferences. We'll call this dialog once before starting the batch print, so all copies use the same settings.

Here's the modified code:

Private Sub CommandButton1_Click()
    Dim xCount As Variant
    Dim xJAC As Variant
    Dim xID As Variant
    Dim xScreen As Boolean
    Dim printConfirmed As Boolean
    
    On Error Resume Next
    
LInput:
    ' Get user inputs
    xJAC = Application.InputBox("Please enter the job number (Leave Out JAC):", "")
    xCount = Application.InputBox("Please enter the number of copies you want to print:", "")
    xID = Application.InputBox("Please enter the Starting ID of the product:", "")
    
    ' Exit if user cancels any input box
    If TypeName(xJAC) = "Boolean" Or TypeName(xCount) = "Boolean" Or TypeName(xID) = "Boolean" Then
        Exit Sub
    End If
    
    ' Validate inputs
    If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Or _
       (xID = "") Or (Not IsNumeric(xID)) Or (xID < 1) Then
        MsgBox "Invalid entry—please try again.", vbInformation, "Input Error"
        GoTo LInput
    End If
    
    ' Show print dialog to let user configure settings
    printConfirmed = Application.Dialogs(xlDialogPrint).Show
    
    ' Exit if user cancels the print dialog
    If Not printConfirmed Then
        MsgBox "Print cancelled by user.", vbInformation
        Exit Sub
    End If
    
    ' Disable screen updates for speed
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    
    ' Batch print each product ID
    For c = 0 To xCount - 1
        ActiveSheet.Range("E2").Value = "JAC" & xJAC
        ActiveSheet.Range("G2").Value = xID + c
        ' Use the settings the user selected in the dialog
        ActiveSheet.PrintOut Copies:=1
    Next
    
    ' Clean up
    ActiveSheet.Range("E2").ClearContents
    ActiveSheet.Range("G2").ClearContents
    Application.ScreenUpdating = xScreen
End Sub

Note on Copy Counts: #

If you want the user to set the number of copies only via the input box (and ignore the dialog's copy count), keep the loop as-is. If you'd prefer to let the dialog handle total copies, remove the xCount input box and rely on the dialog's copy setting instead.

Method 2: Hardcode Print Preferences in VBA #

If you want to automate duplex and binding settings without asking the user to adjust them manually, you can set these directly using Excel's PageSetup object. This is great for consistent, repeatable print jobs.

Here's the code with built-in duplex and left/top binding:

Private Sub CommandButton1_Click()
    Dim xCount As Variant
    Dim xJAC As Variant
    Dim xID As Variant
    Dim xScreen As Boolean
    
    On Error Resume Next
    
LInput:
    ' Get user inputs
    xJAC = Application.InputBox("Please enter the job number (Leave Out JAC):", "")
    xCount = Application.InputBox("Please enter the number of copies you want to print:", "")
    xID = Application.InputBox("Please enter the Starting ID of the product:", "")
    
    ' Exit if user cancels any input box
    If TypeName(xJAC) = "Boolean" Or TypeName(xCount) = "Boolean" Or TypeName(xID) = "Boolean" Then
        Exit Sub
    End If
    
    ' Validate inputs
    If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Or _
       (xID = "") Or (Not IsNumeric(xID)) Or (xID < 1) Then
        MsgBox "Invalid entry—please try again.", vbInformation, "Input Error"
        GoTo LInput
    End If
    
    ' Disable screen updates for speed
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    
    ' Set print preferences directly
    With ActiveSheet.PageSetup
        ' Double-sided printing (long edge flip for portrait documents)
        .Duplex = xlDuplexFlipLongEdge
        ' For landscape, use short edge flip: .Duplex = xlDuplexFlipShortEdge
        
        ' Binding position (left binding for portrait, aligns with top-left stapling)
        .Binding = xlBindingLeft
        ' For top binding (common for landscape), use: .Binding = xlBindingTop
        
        ' Optional: Adjust margins to make room for binding
        .LeftMargin = Application.InchesToPoints(0.85) ' Slightly wider left margin
        .TopMargin = Application.InchesToPoints(0.75)
    End With
    
    ' Optional: Let user select a printer first
    ' Application.Dialogs(xlDialogPrinterSetup).Show
    
    ' Batch print each product ID
    For c = 0 To xCount - 1
        ActiveSheet.Range("E2").Value = "JAC" & xJAC
        ActiveSheet.Range("G2").Value = xID + c
        ActiveSheet.PrintOut Copies:=1
    Next
    
    ' Clean up
    ActiveSheet.Range("E2").ClearContents
    ActiveSheet.Range("G2").ClearContents
    
    ' Optional: Reset PageSetup to default after printing
    ' ActiveSheet.PageSetup.Reset
    
    Application.ScreenUpdating = xScreen
End Sub

Key Properties Explained: #

• Duplex: Controls double-sided printing. Use xlDuplexFlipLongEdge for portrait (flips along the long edge, like a book) or xlDuplexFlipShortEdge for landscape.
• Binding: Sets the binding position. xlBindingLeft works for top-left stapling in portrait, while xlBindingTop is better for landscape.
• Margins: Adjust these to ensure your content doesn't get covered by staples.

内容的提问来源于stack exchange，提问作者Pavlo Kobzar