Check the number of copies that have been printed

Asked

Viewed 229 times

4

Hello, I have a table that when printed generates a record in a txt telling the user who printed, on which machine, on which printer, which document was and when. But I would also like to register the number of copies, but I have no idea how.

Code that picks up information from the computer:

Declare Function GetComputerName& Lib "kernel32" Alias "GetComputerNameA" (ByVal lbbuffer As String, nSize As Long)
Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long

Function deMAQUINA() As String
Dim Buffer As String * 256
Dim BuffLen As Long
Dim lngX As Long
Dim strCompName As String
BuffLen = 255
If GetComputerName(Buffer, BuffLen) Then deMAQUINA = Left(Buffer, BuffLen)
End Function

Function deUSUARIO() As String
Dim Buffer As String * 256
Dim BuffLen As Long
BuffLen = 256
If GetUserName(Buffer, BuffLen) Then deUSUARIO = Left(Buffer, BuffLen - 1)
End Function

Code that registers:

 Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Dim vUsuario As String, vMaquina As String
    vUsuario = deUSUARIO()
    vMaquina = deMAQUINA()

    Open "\\Caminho\do\arquivo.txt" For Append As #2

        Print #2, "O usuário: " & vUsuario & " | pela Máquina: " & vMaquina & " | pela impressora: " & ActivePrinter & " | Imprimiu o documento " & ActiveWorkbook.Name & "| no dia: " & Now

    Close #2

    Open ActiveWorkbook.Path & "\OBSOLETO\" & Left(ActiveWorkbook.Name, 8) & ".txt" For Append As #3

        Print #3, "O usuário: " & vUsuario & " | pela Máquina: " & vMaquina & " | pela impressora: " & ActivePrinter & " | Imprimiu o documento " & ActiveWorkbook.Name & "| no dia: " & Now

    Close #3

End Sub

If anyone knows, please, could you help?

excel vba

  • I could not put a spreadsheet there, with a table per user to go adding a counter to each print, or registered in a row each print and generate another spreadsheet with report?:

  • Imagine if the user print 3 copies and save with it. He just circumvented the record because it will appear that it was a printed, but he has 3.

  • What if you block the printing and create a routine that the user can only print by clicking a button? Then you have control of how many copies. What do you think?

  • In that case I would know how to help you do I have a routine ready that does that. =)

  • I would like to, but users would be angry since they are used to using "Ctrl+P" so I try to keep the record as if nothing had been changed in the spreadsheet. The ideal is something that I take how many were actually printed :)

  • Ctrl+P would continue to work, only it would print automatic! rs But I understand. If you discover put there. Abs

  • Thanks anyway @Evert, I will post yes.

Show 2 more comments

1 answer

1

Private Sub Workbook_BeforePrint(Cancel As Boolean) 
If ActiveSheet.CodeName = "Sheet1" Then 
    With Sheet1.Range("A1") 
        Select Case .Value 
        Case Is > 0 
            .Range("A1").Value = .Range("A1") + 1 

        Case Else ' cell is empty
            .Range("A1").Value = 1 

        End Select 
    End With 
End If 
End Sub

You can use a counter and increment a cell with each new print. check if you can implement your!

  • But in this case it marks only that printed, I already have, I need to know how many were copied, as if the user printed 3 copies at once.

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.