macro stampa fronte retro automatico



  • macro stampa fronte retro automatico
    di marco_budin data: 04/01/2017 20:22:25

    ciao a tutti.
    ho creato in excel una macro per stampare in automatico 2 pagine. Ora le stampa su 2 fogli separati.
    Vorrei che stampasse questi 2 fogli in automatico, senza chiedere niente in fronte e retro. Essendo una stampante in rete che utilizzano tutti, non posso impostare la stampante, quindi con la macro spero che questo processo di stampare fronte/retro in automatico senza che ti chieda niente funziona.
    Spero che qualcuno possa aiutarmi.
    Ringrazio anticipatamente,
    saluti
    Marco
     
    Public Sub Stampanti_dia()
            Sheets("Lista d1").PrintOut From:=1, To:=2, Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
    End sub
    



  • di Mauro data: 05/01/2017 03:21:15

    Online ho trovato questo... da provare...
     
    Aggiungere un modulo BAS standard al progetto.
    Option Explicit
    
       Public Type PRINTER_DEFAULTS
    
           pDatatype As Long
           pDevmode As Long
           DesiredAccess As Long
       End Type
    
       Public Type PRINTER_INFO_2
           pServerName As Long
           pPrinterName As Long
           pShareName As Long
           pPortName As Long
           pDriverName As Long
           pComment As Long
           pLocation As Long
           pDevmode As Long       ' Pointer to DEVMODE
           pSepFile As Long
           pPrintProcessor As Long
           pDatatype As Long
           pParameters As Long
           pSecurityDescriptor As Long  ' Pointer to SECURITY_DESCRIPTOR
           Attributes As Long
    
    
           Priority As Long
           DefaultPriority As Long
           StartTime As Long
           UntilTime As Long
           Status As Long
           cJobs As Long
           AveragePPM As Long
       End Type
    
       Public Type DEVMODE
           dmDeviceName As String * 32
    
           dmSpecVersion As Integer
           dmDriverVersion As Integer
           dmSize As Integer
           dmDriverExtra As Integer
           dmFields As Long
           dmOrientation As Integer
           dmPaperSize As Integer
           dmPaperLength As Integer
           dmPaperWidth As Integer
           dmScale As Integer
           dmCopies As Integer
           dmDefaultSource As Integer
           dmPrintQuality As Integer
           dmColor As Integer
           dmDuplex As Integer
           dmYResolution As Integer
           dmTTOption As Integer
           dmCollate As Integer
           dmFormName As String * 32
           dmUnusedPadding As Integer
           dmBitsPerPel As Integer
           dmPelsWidth As Long
           dmPelsHeight As Long
           dmDisplayFlags As Long
           dmDisplayFrequency As Long
           dmICMMethod As Long
           dmICMIntent As Long
           dmMediaType As Long
           dmDitherType As Long
           dmReserved1 As Long
           dmReserved2 As Long
       End Type
    
       Public Const DM_DUPLEX = &H1000&
       Public Const DM_IN_BUFFER = 8
    
       Public Const DM_OUT_BUFFER = 2
       Public Const PRINTER_ACCESS_ADMINISTER = &H4
       Public Const PRINTER_ACCESS_USE = &H8
       Public Const STANDARD_RIGHTS_REQUIRED = &HF0000
       Public Const PRINTER_ALL_ACCESS = (STANDARD_RIGHTS_REQUIRED Or _
                 PRINTER_ACCESS_ADMINISTER Or PRINTER_ACCESS_USE)
    
       Public Declare Function ClosePrinter Lib "winspool.drv" _
        (ByVal hPrinter As Long) As Long
       Public Declare Function DocumentProperties Lib "winspool.drv" _
         Alias "DocumentPropertiesA" (ByVal hwnd As Long, _
         ByVal hPrinter As Long, ByVal pDeviceName As String, _
         ByVal pDevModeOutput As Long, ByVal pDevModeInput As Long, _
         ByVal fMode As Long) As Long
       Public Declare Function GetPrinter Lib "winspool.drv" Alias _
         "GetPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, _
         pPrinter As Byte, ByVal cbBuf As Long, pcbNeeded As Long) As Long
       Public Declare Function OpenPrinter Lib "winspool.drv" Alias _
         "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, _
         pDefault As PRINTER_DEFAULTS) As Long
       Public Declare Function SetPrinter Lib "winspool.drv" Alias _
         "SetPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, _
         pPrinter As Byte, ByVal Command As Long) As Long
    
       Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
        (pDest As Any, pSource As Any, ByVal cbLength As Long)
     
       ' ==================================================================
       ' SetPrinterDuplex.
       '
       '  Programmatically set the Duplex flag for the specified default properties
       '  of the printer driver.
       '
       '  Returns: True on success and False on error. An error also
    
       '  displays a message box. This message box is displayed for information
       '  only. You must modify the code to support better error
       '  handling in your production application.
       '
       '  Parameters:
       '    sPrinterName - The name of the printer to be used.
       '
       '    nDuplexSetting - One of the following standard settings:
       '       1 = None
       '       2 = Duplex on long edge (book)
       '       3 = Duplex on short edge (legal)
       '
       ' ==================================================================
       Public Function SetPrinterDuplex(ByVal sPrinterName As String, _
       ByVal nDuplexSetting As Long) As Boolean
    
          Dim hPrinter As Long
          Dim pd As PRINTER_DEFAULTS
          Dim pinfo As PRINTER_INFO_2
          Dim dm As DEVMODE
       
          Dim yDevModeData() As Byte
          Dim yPInfoMemory() As Byte
          Dim nBytesNeeded As Long
          Dim nRet As Long, nJunk As Long
       
          On Error GoTo cleanup
       
          If (nDuplexSetting < 1) Or (nDuplexSetting > 3) Then
             MsgBox "Error: dwDuplexSetting is incorrect."
             Exit Function
          End If
          
          pd.DesiredAccess = PRINTER_ALL_ACCESS
          nRet = OpenPrinter(sPrinterName, hPrinter, pd)
          If (nRet = 0) Or (hPrinter = 0) Then
             If Err.LastDllError = 5 Then
                MsgBox "Access denied -- See the article for more info."
             Else
                MsgBox "Cannot open the printer specified " & _
                  "(make sure the printer name is correct)."
             End If
             Exit Function
          End If
       
          nRet = DocumentProperties(0, hPrinter, sPrinterName, 0, 0, 0)
          If (nRet < 0) Then
             MsgBox "Cannot get the size of the DEVMODE structure."
             GoTo cleanup
          End If
       
          ReDim yDevModeData(nRet + 100) As Byte
          nRet = DocumentProperties(0, hPrinter, sPrinterName, _
                      VarPtr(yDevModeData(0)), 0, DM_OUT_BUFFER)
          If (nRet < 0) Then
             MsgBox "Cannot get the DEVMODE structure."
             GoTo cleanup
          End If
       
          Call CopyMemory(dm, yDevModeData(0), Len(dm))
       
          If Not CBool(dm.dmFields And DM_DUPLEX) Then
            MsgBox "You cannot modify the duplex flag for this printer " & _
                   "because it does not support duplex or the driver " & _
                   "does not support setting it from the Windows API."
             GoTo cleanup
          End If
       
          dm.dmDuplex = nDuplexSetting
          Call CopyMemory(yDevModeData(0), dm, Len(dm))
       
          nRet = DocumentProperties(0, hPrinter, sPrinterName, _
            VarPtr(yDevModeData(0)), VarPtr(yDevModeData(0)), _
            DM_IN_BUFFER Or DM_OUT_BUFFER)
    
          If (nRet < 0) Then
            MsgBox "Unable to set duplex setting to this printer."
            GoTo cleanup
          End If
       
          Call GetPrinter(hPrinter, 2, 0, 0, nBytesNeeded)
          If (nBytesNeeded = 0) Then GoTo cleanup
       
          ReDim yPInfoMemory(nBytesNeeded + 100) As Byte
    
          nRet = GetPrinter(hPrinter, 2, yPInfoMemory(0), nBytesNeeded, nJunk)
          If (nRet = 0) Then
             MsgBox "Unable to get shared printer settings."
             GoTo cleanup
          End If
       
          Call CopyMemory(pinfo, yPInfoMemory(0), Len(pinfo))
          pinfo.pDevmode = VarPtr(yDevModeData(0))
          pinfo.pSecurityDescriptor = 0
          Call CopyMemory(yPInfoMemory(0), pinfo, Len(pinfo))
       
          nRet = SetPrinter(hPrinter, 2, yPInfoMemory(0), 0)
          If (nRet = 0) Then
             MsgBox "Unable to set shared printer settings."
          End If
       
          SetPrinterDuplex = CBool(nRet)
    
    cleanup:
          If (hPrinter <> 0) Then Call ClosePrinter(hPrinter)
    
       End Function
    
    
    Aggiungere un controllo Pulsante di comando a Form1.
    Aggiungere l'esempio di codice riportato di seguito alla finestra del codice per Form1.
    
          
       Option Explicit
       Private Sub Command1_Click()
          Dim oWord As Object
          Dim oDoc As Object
          
          Set oWord = CreateObject("Word.application")
    
          oWord.Visible = True
          
          Set oDoc = oWord.Documents.Add
          oDoc.Range.Select
          
    
          oWord.Selection.TypeText "This is on page 1" & vbCr
          oWord.Selection.InsertBreak 1
          oWord.Selection.TypeText "This is page 2"
          
          SetPrinterDuplex Printer.DeviceName, 2
          
          oDoc.PrintOut Background:=False
          
          SetPrinterDuplex Printer.DeviceName, 1
          
          MsgBox "Print Done", vbMsgBoxSetForeground
          
          oDoc.Saved = True
          oDoc.Close
          Set oDoc = Nothing
       
          oWord.Quit
          Set oWord = Nothing
       End Sub	
    
    					
    Eseguire l'esempio. 
    
    Se la stampante supporta la stampa fronte retro, il documento di prova viene stampato su entrambi i lati della pagina



  • di Mauro data: 05/01/2017 03:23:36

    P.s. si parte dal form1 di un project vuoto in vba



  • di marco_budin data: 05/01/2017 07:25:57

    Ciao Mauro,
    grazie mille della pronta risposta!
    una domanda, questo codice devo inserirlo nel modulo della macro o direttamente nel vba del foglio?
    saluti
    Marco



  • di Mauro data: 05/01/2017 09:26:41

    C'è scritto nella prima riga devi aggiungere un modulo gas al progetto