Can a Macro write a log of printed pages to a Text file?

Howdy,

We print jobs every day for screenprinting and with the amount of things I print and the amount of people using the printed pages, sometimes things get lost or forgotten.

I can't tell you how many times I get asked a day, "Have you printed this job yet?" and all I can say at this point with my old age and memory is "ummm....hmmm...yea...I think so...but I could be wrong...." LOL

So, I was curious....

Is there a way to use a macro to print to a text file something simple like the job name and the date it was printed after actually printing the job? Or possibly using a macro to print to the text file log and then it would call up the Print menu after writing to the file?

I don't want to actually print out a separate piece of paper every time I print a job. I would just like a simple text file to be written and the job name appended to the top of the file....sort of like this...

S:/network/Graphics/Newest Job Today - July 29, 2011 - 10am

S:/network/Graphics/Newest Job Today - July 28, 2011 - 9am

S:/network/Graphics/Newest Job Today - July 27, 2011 - 1pm

The newest job would always be added at the top of the text file.

Any ideas? is it even possible?

Thanks!

Jason

  • Jason Moore said:
    I would just like a simple text file to be written

    That certainly sounds possible, but you'll need a bit of macro programming to do it.

    VBA has hooks to detect the print events (before or after) and is certainly capable of writing to text files.

    • Hi.

      Put this in any ThisMacroStorage module and make sure you events are enabled.

      Notice Drive letter is D in code. Also I could not get the print event to fire when printed from the print preview window.

      ~John

       

       

      Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

      Private Sub GlobalMacroStorage_DocumentAfterPrint(ByVal Doc As Document)
          WriteToATextFile "printed: " & vbDate & " " & ActiveDocument.FullFileName
      End Sub


      Sub test() 'use for your own testing
          'MsgBox "printed: " & VBA.Date$ & " " & ActiveDocument.FullFileName
           'WriteToATextFile "printed: " & VBA.Date$ & " " & ActiveDocument.FullFileName
      End Sub


      Private Function OpenIt(strFile As String)
          ShellExecute 0, "open", strFile, 0, 0, 1
      End Function

      Private Function WriteToATextFile(strMine As String) As Boolean

      Dim myFile$, strDrive$
      Dim fnum
         On Error GoTo WriteToATextFile_Error

      strDrive = "D"
      myFile = strDrive & ":\" & "temp_printed.txt"
      fnum = FreeFile()
      Open myFile For Append As fnum


      Write #fnum, strMine
      Write #fnum,
      Close #fnum
      If Len(strMine) Then WriteToATextFile = True: OpenIt myFile

         On Error GoTo 0
         Exit Function

      WriteToATextFile_Error:
          If Err = 75 Then
              MsgBox "Path/File access error. Try using another drive for the temporary file."
          Else
              MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure WriteToATextFile of Module GDG"
          End If

      End Function

      • Hi John,

        Sorry, I'm still a Macro noob! LOL

        What do you mean put it in any 'ThisMacroStorage' module? Also, how do I make sure my events are enabled?

        Thanks again!!

        • Hi.

          See if  this  get's you going. Just make sure to paste the code inside the existing ThisMacroStorage storage module of the new gms.

          Events can be toggled by using the macro toolbar. Default is enabled.

          Also make sure to uncheck Delay load VBA in the option of Draw.

          ~John

          • Hi John,

            I have a file saved as temp.cdr then I hit the print button and printed the page.

            Below is a screenshot of what appeared after the page printed.

            What is the "Printed 7" that shows up for? We wouldn't actually need the word "printed" if it's not needed.

            Is there a way to make it so that the newest printed job is always placed at the top of the text file?

            Could it be formated like this? Is this type of formatting possible?

            Below, the D:/artwork would be automatically entered as whatever directory the file was located in...it wouldnt need to be hard coded into the script unless you need it to be for this to work.

            D:/artwork/filename.cdr - Monday July 15, 2011 - 10:05am

            Also, does the text file have to open up after every print job? I would prefer it to not open after every print if that is possible.

            I'll be glad to donate for your time on this as well...your work is very appreciated.

             

            • Hi.

              Can you give this a try. You'll have to add a reference to the Microsoft Scripting Runtime. Do this by going to Tools > References in the VBA editor.

              Use Sub Test for testing.

              ~John

               

              Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

              Private Sub GlobalMacroStorage_DocumentAfterPrint(ByVal Doc As Document)
                  WriteToATextFile ActiveDocument.FullFileName & " - " & VBA.Format(VBA.Date$, "mmm d, yyyy")
              End Sub
              Sub test() 'use for your own testing
                  'MsgBox ActiveDocument.FullFileName & " - " & VBA.Format(VBA.Date$, "mmm d, yyyy")
                   'WriteToATextFile ActiveDocument.FullFileName & " - " & VBA.Format(VBA.Date$, "mmm d, yyyy")
              End Sub
              Private Function OpenIt(strFile As String)
                  ShellExecute 0, "open", strFile, 0, 0, 1
              End Function
              Sub closeIt()
              Dim myFile$, strDrive$
              strDrive = "D"
                  myFile = strDrive & ":\" & "temp_printed.txt"
                  Close myFile
              End Sub

              Private Function WriteToATextFile(strMine As String) As Boolean

              Dim myFile$, strDrive$, strOld$, strGet$
              Dim fnum
                 'On Error GoTo WriteToATextFile_Error

              strDrive = "D"
              myFile = strDrive & ":\" & "temp_printed.txt"
              strGet = getTextFile(strDrive, myFile)
              fnum = FreeFile()
              Open myFile For Output As fnum

              Print #fnum, strMine & vbCrLf & strGet
              'Print #fnum,
              Close #fnum
              If Len(strMine) Then WriteToATextFile = True: OpenIt myFile

                 On Error GoTo 0
                 Exit Function

              WriteToATextFile_Error:
                  If Err = 75 Then
                      MsgBox "Path/File access error. Try using another drive for the temporary file."
                  Else
                      MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure WriteToATextFile of Module GDG"
                  End If

              End Function

              Private Function getTextFile$(strDrive$, myFile$)

              Dim oFSO As New FileSystemObject
              Dim oFS
              Dim strFinal$

                 On Error GoTo getTextFile_Error


              myFile = strDrive & ":\" & "temp_printed.txt"
              Set oFS = oFSO.OpenTextFile(myFile)

              Do Until oFS.AtEndOfStream
              strFinal = strFinal & vbCrLf & oFS.ReadLine
              Loop

              getTextFile = strFinal

                 On Error GoTo 0
                 Exit Function

              getTextFile_Error:

                  getTextFile = ""
              End Function

              • Hi John,

                I'm not sure how to follow your instructions of  "Use Sub Test" for testing....

                I replaced the old script with your new one above, and I'm getting the following error after printing a page. I've attached a screenshot of what appears on my screen after printing. Also, nothing printed to the text file log in case you need to know that as well =)

                Thanks for your expertise and help!