none
Workbook.BeforePrint RRS feed

  • Question

  •  

    Hi
    I'm trying to save a copy of current active workbook using application events. I need to export a pdf file of active workbook and a copy of activeworkbook to a folder. So I decided to use the application_WorkbookBeforePrint event to capture the printing and perform my tasks

    This function worked perfectly when I call excel File->Print menu. My problem appeared when I call printout from a macro or a button. I found many people with same problem at forums I didn't found a solution.

    I mean if I call printout using ButtonPrint funcion app_WorkbookBeforePrint is triggered but wb.savecopyas does nothing
    If I use file->print it works perfectly. 


    it's like functions related with wb, application etc doesn't work at workbookbeforeprint event. I tried activeworkbook.save, activeworkbook.close etc and none of those works. Not error messages appear when instruction is executed, but at the end does nothing

    Any Idea or sugestion will be welcome. Attached below you will found part of my code as example


    Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
        Cancel = True

        Application.EnableEvents = False
        Set toSaveAfterPrint = Wb
        Wb.ExportAsFixedFormat xlTypePDF, "c:\temp\testpdf.pdf"
        Wb.SaveCopyAs "c:\temp\testcopyworkbook"
        Application.EnableEvents = True
    End Sub

    Sub ButtonPrint()
        ActiveWorkbook.PrintOut 1, 1, , , , , 1, , True
    End Sub

    Tuesday, November 22, 2016 9:34 AM

Answers

  • Hi TJLJ,

    when I test the above mentioned code then I got the same result like yours.

    Workbook_beforeprint event is in ThisWorkbook module.

    and I create one other module to print the file.

    when I call the sub then it will raise the event and also exporting as pdf file but not saving the copy of file.

    so here I can reproduce the issue.

    Temporary solution is to put all the code to the normal sub like mentioned below.

    Sub ButtonPrint()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Application.EnableEvents = False
         Set toSaveAfterPrint = wb
         wb.ExportAsFixedFormat xlTypePDF, "C:\Users\v-padee\Desktop\testpdf.pdf"
         wb.SaveCopyAs "C:\Users\v-padee\Desktop\testpdf.xlsx"
         Application.EnableEvents = True
         ActiveWorkbook.PrintOut 1, 1, , , , , 1, , True
     End Sub

    so it will save and export the file as pdf before print and temporary avoid to use before_print event to save copy of file.

    please give your feedback to Excel User voice to look in to this issue and if there is any problem then they can try to solve.

    Excel User Voice

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 23, 2016 2:14 AM
    Moderator

All replies

  • Hi,

    I think you should put your code under ThisWorkbook in VBA project explorer. it will look something like this:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
     Cancel = True
    
        Application.EnableEvents = False
        Set toSaveAfterPrint = wb
        wb.ExportAsFixedFormat xlTypePDF, "c:\temp\testpdf.pdf"
        wb.SaveCopyAs "c:\temp\testcopyworkbook"
        Application.EnableEvents = True
    End Sub


    Vish Mishra

    Tuesday, November 22, 2016 9:34 PM
  • Hi TJLJ,

    when I test the above mentioned code then I got the same result like yours.

    Workbook_beforeprint event is in ThisWorkbook module.

    and I create one other module to print the file.

    when I call the sub then it will raise the event and also exporting as pdf file but not saving the copy of file.

    so here I can reproduce the issue.

    Temporary solution is to put all the code to the normal sub like mentioned below.

    Sub ButtonPrint()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Application.EnableEvents = False
         Set toSaveAfterPrint = wb
         wb.ExportAsFixedFormat xlTypePDF, "C:\Users\v-padee\Desktop\testpdf.pdf"
         wb.SaveCopyAs "C:\Users\v-padee\Desktop\testpdf.xlsx"
         Application.EnableEvents = True
         ActiveWorkbook.PrintOut 1, 1, , , , , 1, , True
     End Sub

    so it will save and export the file as pdf before print and temporary avoid to use before_print event to save copy of file.

    please give your feedback to Excel User voice to look in to this issue and if there is any problem then they can try to solve.

    Excel User Voice

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 23, 2016 2:14 AM
    Moderator
  • Hi Deepack 

    Thanks for your answer. I found the same solution after trying differents approachs. It is like the application events starting like BeforePrint, BeforeSave, etc doesn't works as the other events and doesn't allow us to execute some functions. Don't know if it is an intended decision from microsoft or not. Anyway I dind't found other solution to my problem so I will put my code in a standard sub and will avoid use of beforeprint for this particular behaviour.

    Thanks a lot for your help

    Tomas


    Wednesday, November 23, 2016 10:16 AM
  • Hi, In my particular case I'm developing a solution for all openned workbooks and I can't use this workbook to solve my problem. As applicaion.Workbook_BeforePrint does't work for me.

    I will develop an standard right button menu for print documents calling to my own sub to resolve this issue and I will avoid the use of beforeprint for my particular case. Following Deepack sugesstion

    Thanks a lot for your help

    Tomas



    • Edited by TJLJ Wednesday, November 23, 2016 10:20 AM
    Wednesday, November 23, 2016 10:19 AM