View original (French)

Print from a VBA module

Solved
Mistral_13200 Posted messages 649 Status Member -  
 Oz -
Hello,
Hello everyone,
I created an Excel workbook in which I have a VBA module for printing. This workbook is available to several dozen users. It works very well and is appreciated by all. However, I am being asked to improve it by allowing the user to select the printer (in case multiple printers are connected to the computer) or to choose the print quality. To do this, I would like my print macro below to call and display the "Print" dialog box, but I am unable to do so! 

Sub PrintTable() Dim p As Integer 'Application.ScreenUpdating = False ' Disable screen updating. 'Application.EnableEvents = False ' Stop event handling. If Sheets("Bordereau").Range("A28").Value = "" Then ' Check if the first used cell, A28. MsgBox ("The table has not been filled out!" & vbCrLf & " " & vbCrLf & "There is nothing to print."), vbCritical, "Fédération Trucmuche" Exit Sub End If 'Define the entire area Sheets("Tableau").PageSetup.PrintArea = "$A$1:$N$235" ' Part of the sheet used. p = Count_LinesBdx ' Jump to the Count_LinesBdx function p = Count_PagesBdx(p) ' Jump to the Count_PagesBdx function With Sheets("Tableau") .Visible = True .PrintOut from:=1, To:=p '.PrintOut would not display a dialog End With Application.ScreenUpdating = True ' Restore screen updating. Application.EnableEvents = True ' Re-enable event handling. End Sub ' Calculates and returns the number of the last non-empty row. Function Count_LinesBdx() As Integer Dim I As Integer For I = 28 To 235 If Sheets("Tableau").Range("A" & I).Value = "" Then Exit For ' Check the first empty cell in column A. Next I Count_LinesBdx = I - 1 End Function ' Calculates and returns the number of pages. Function Count_PagesBdx(Lines As Integer) As Integer Select Case Lines Case 1 To 58: Count_PagesBdx = 1 Case 59 To 110: Count_PagesBdx = 2 Case 111 To 162: Count_PagesBdx = 3 Case 163 To 214: Count_PagesBdx = 4 Case Is > 214: Count_PagesBdx = 5 End Select End Function

Regardless of this modification, the workbook and printing work very well.
Can you help me?
Thank you in advance.
Mistral

37 answers

  • 1
  • 2
  1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    To choose a printer 
    Application.Dialogs(xlDialogPrint).Show

    and you normally have the "printer properties" button

    this syntax may not work on a network (I am on a single workstation) in which case you would need to use WMI... You say.
    Michel
    0
  2. Mistral_13200 Posted messages 649 Status Member 4
     
    Hello Michel,

    Thank you for your response and for your promptness.

    I did a test on a simple workbook and it works correctly, at least for the printer properties. I still need to test this with several printers.
    However, I can't seem to integrate this line into my module while keeping the calculation of the number of pages to print.
    Do you have any ideas?
    Best regards,
    Mistral
    0
  3. Mistral_13200 Posted messages 649 Status Member 4
     
    Re,

    Actually, I have a lot of difficulty understanding all the arguments (15 in total) of the instruction: 

    Application.Dialogs(xlDialogPrint).Show(, 2, [p], 7, , , , , , , , , , , 1)


    I can successfully validate the page numbers to print (from 2 to [p]) as well as the number of copies (7), but I can't validate the "Page(s)" option, even though I'm setting the 15th argument to 1.

    Do you have any idea or solution to suggest?
    Best regards
    Mistral
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Sorry, I don't see anything at all...
      0
  4. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
     
    Hello,
    Try this code that allows you to choose the printer (click OK to confirm the choice): 
    Application.Dialogs(Excel.xlBuiltInDialog.xlDialogPrinterSetup).Show
    And in your code, you retrieve the name of the active printer that you place as an additional argument for 
    PrintOut .PrintOut from:=1, To:=p .....


    Or with this: 
    Sub choix_imprimante imprimante = Application.Dialogs(xlDialogPrinterSetup).Show Application.ActivePrinter = imprimante End Sub
    Regards.
    The Penguin
    0
  6. Mistral_13200 Posted messages 649 Status Member 4
     
    Hello Le Pingou,
    First of all, thank you for taking an interest in my problem.
    I wanted to test the two proposed solutions but I must admit I'm a bit lost.
    In the first one, I do get the printer choice window that opens, I can indeed choose it and configure the printing with the "Configure" button, but I don't see how to retrieve the printer's name or how to insert it afterwards.
    In the second one, I get a 1004 error: The ActivePrinter method of the Application object has failed.
    Can you help me please?
    Best regards
    Mistral
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Hello,
      The instruction given by Le Pingou: 
      printer = Application.Dialogs(xlDialogPrinterSetup).Show
      returns "true" or "false". Since true and false are not printer names (;-)), I recommend: 
      Sub printer_choice() Dim printer As String, InitialPrinter As String InitialPrinter = Application.ActivePrinter Application.Dialogs(xlDialogPrinterSetup).Show printer = Application.ActivePrinter MsgBox printer Application.ActivePrinter = InitialPrinter End Sub


      Response edited to include InitialPrinter to restore default settings......
      0
    2. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
       
      Hello pijaku,
      Yes, I agree with you, however, since he wants to use the [PrintOut] command, I specified this:
      And in your code, you retrieve the name of the active printer that you place as an additional argument for PrintOut. .PrintOut from:=1, To:=p, .....
      Best regards.
      The Penguin
      0
    3. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Hello Le Pingou,
      Exactly. My reply was just a complement to yours.
      Have a good day.
      See you soon.
      0
  7. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
     
    Hello Mistral_13200,
    Yes, I can help you, it's hard to answer without seeing the code you created..!

    Note: regarding the first one, I didn't say the [Configure] button but: click OK to validate the choice, which is different.

    Regards.
    The Penguin
    0
  8. Mistral_13200 Posted messages 649 Status Member 4
     
    The Pingou,
    Pijaku

    Hello to both of you,

    Thank you for taking the time to solve my issue.

    The complete code for my macro is in my very first message. I would just like to remind you that this code works perfectly as it is. However, some users are asking me to modify it to choose the printer and the print quality.
    I understood that I needed to validate the printer choice with OK, I just wanted to say that one could also intervene on the printer settings if desired.
    It is indeed the integration of the default printer into my macro that I'm struggling with, and that's precisely where I need your help.
    Now, if you think that the 
    PrintOut
    instruction is not appropriate, I have no objections to changing it, but I don't know what to change it to.
    I have tried: 

    Application.Dialogs(xlDialogPrint).Show(, 2, [p], 7, , , , , , , , , , , 1)

    But there I am stuck on how to validate the page option in the "Print" window. I can retrieve the number of pages and the number of copies, which is essential to only print the necessary number of pages.

    In any case, a big thank you to both of you.
    Best regards
    Mistral
    0
  9. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
     
    Hello,
    Yes, but it's not the code with the correction that doesn't work.
    In this case, the lines of code you should have just after the line: 
     p = Nb_PagesBdx(p)
    : 
    Application.Dialogs(Excel.XlBuiltInDialog.xlDialogPrinterSetup).Show Nom = ActivePrinter With Sheets("Tableau") .Visible = True .PrintOut from:=1, To:=p, ActivePrinter:=Nom .Visible = False End With

    Note: in the dialog box, you choose the printer and confirm by clicking OK.
    --
    Regards.
    The Penguin
    0
  10. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello everyone,

    By any chance
    I couldn't find help on your 15 arguments (...?...) and I don't understand this 1.... when you talk about "pages", are you referring to pages within a "sheet" in Excel?

    To find the number of pages per sheet, I found this instruction on another site 
    ExecuteExcel4Macro("GET.DOCUMENT(50)")

    and to print: 
    SheetN.PrintOut From:=1 To:=ExecuteExcel4Macro("GET.DOCUMENT(50)")


    but....
    --
    Michel
    0
  11. Mistral_13200 Posted messages 649 Status Member 4
     
    Hello Michel,

    The instruction:

    Application.Dialogs(xlDialogPrint).Show(, 1, [p], 7, , , , , , , , , , , 1)

    has 15 possible configuration arguments which are:

    range_num, from, to, copies, draft, preview, print_what, color, feed, quality, y_resolution, selection, printer_text, print_to_file, collate

    I can configure without problem the pages I want to print (from 1 to p) and the number of copies to print (7) see the example above, but I can't validate the pages option in the "Pages" section of the Print dialog box; I keep getting "All" permanently. So I end up printing the entire sheet of the workbook, which is 25 pages.

    Best regards
    Mistral
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      Small question: why put p in brackets?

      As for the 1 in argument n)15 collate, it is not necessary because collate:= true by default

      with the extended macro recorder page 1 to 2
      ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1
      0
  13. Mistral_13200 Posted messages 649 Status Member 4
     
    because "p" is a variable, it is the result of the calculation of the number of pages to print.

    To be more precise, in the Range section of the Print window, there are two options: "All" and "Pages". I would like to switch from "All" to "Pages", but I can't do it.
    0
    1. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
       
      Hello Mistral_13200,
      So with my proposal (pos 12) the values of your variables are respected.
      Sorry if this does not suit you.
      Regards.
      The Penguin
      0
    2. Mistral_13200 Posted messages 649 Status Member 4
       
      I'm a bit lost in all these messages, but I responded to your proposal, maybe not in the right order, and I apologize for that.

      I totally agree, the variables are being respected.
      It seems that the last thing left, easier said than done, is to be able to modify the printing settings such as the quality Normal/Draft or Black & White/Color.
      0
  14. Mistral_13200 Posted messages 649 Status Member 4
     
    Re Le Pingou,

    It indeed seems to work; the default printer is recognized. That's already a good thing!
    However, if I change the print quality or if I choose to print in B&W or color using the "Configure" button in the dialog window, nothing happens, and yet I confirm with OK.
    In fact, I would even say that it retains the print settings from the last manual configuration.
    I don't understand anything anymore....
    Best regards
    Mistral
    0
  15. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
     
    Hello,
    Indeed, It is apparently easier said than done to be able to modify the print settings such as Normal/Draft quality or B&W/Color and for it to work, the chosen settings must be passed to the procedure.
    Could you please specify exactly which parameters will be allowed for the user?

    --
    Regards.
    The Penguin
    0
  16. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
     
    Hello Mistral_13200,
    I just ran some tests with the code I proposed, changing the various configuration values, black/white, colors, orientation (Portrait/Landscape), etc., and everything works perfectly with the code as is.

    --
    Best regards.
    The Penguin
    0
  17. Mistral_13200 Posted messages 649 Status Member 4
     
    Hello Pingou,

    I also did some other tests last night on another computer and another printer, and there I was indeed able to change the print quality and the color. Unfortunately, at the club, I don't have any network printers...
    I will look into why I am stuck on B&W on my color printer???

    Once I have fixed my issue, and if you allow me, I will get back to you if I have any other questions.
    Thanks again to the three of you for your help.
    Best regards
    Mistral

    P.S.: I will be absent until 03/21
    0
    1. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
       
      Hello,
      I'm going to see why my color printer is stuck on B&W???
      Probably reinstalling the printer software!
      Have a great holiday.
      Regards.
      The Penguin
      0
  18. Mistral_13200 Posted messages 649 Status Member 4
     
    Re,

    This is exactly what I just did and the problem persists.
    Below is the complete code I am using for my tests and for which I do not think I made any mistakes. 

    Sub ImpressionTableau() Dim p As Integer Application.ScreenUpdating = False ' Disable screen refreshing. Application.EnableEvents = False ' Stop event monitoring. If Sheets("Tableau").Range("A28").Value = "" Then ' Test if the first used cell, A28. MsgBox ("The table has not been filled out!" & vbCrLf & " " & vbCrLf & "There is therefore nothing to print."), vbCritical, "Fédération Trucmuche" Exit Sub End If 'Define the full area Sheets("Tableau").PageSetup.PrintArea = "$A$1:$N$235" ' Part of the sheet used. p = Nb_LigneBdx ' Jump to the function Nb_LigneBdx p = Nb_PagesBdx(p) ' Jump to the function Nb_PagesBdx 'p = 9 If Application.Dialogs(Excel.XlBuiltInDialog.xlDialogPrinterSetup).Show = False Then GoTo Line1 Else Nom = ActivePrinter With Sheets("Tableau") .Visible = True .PrintOut from:=1, To:=p, ActivePrinter:=Nom '.Visible = False 'To be put back in the final version. End With End If Line1: Application.ScreenUpdating = True ' Restore screen refreshing. Application.EnableEvents = True ' Re-enable event monitoring. End Sub ' Calculates and returns the number of the last non-empty row. Function Nb_LigneBdx() As Integer Dim I As Integer For I = 28 To 235 If Sheets("Tableau").Range("B" & I).Value = "" Then Exit For ' Test the first empty cell in column A. Next I Nb_LigneBdx = I - 1 End Function ' Calculates and returns the number of pages. Function Nb_PagesBdx(Lignes As Integer) As Integer Select Case Lignes Case 1 To 58: Nb_PagesBdx = 1 Case 59 To 110: Nb_PagesBdx = 2 Case 111 To 162: Nb_PagesBdx = 3 Case 163 To 214: Nb_PagesBdx = 4 Case Is > 214: Nb_PagesBdx = 5 End Select End Function


    If you see any anomalies, please let me know.
    Clearing my head wouldn't hurt...
    Best regards
    Mistral
    0
  19. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
     
    Hello,
    The part regarding the printer is correct.
    To avoid [GoTo Line1], I would write it this way: 
    If Application.Dialogs(Excel.XlBuiltInDialog.xlDialogPrinterSetup).Show = True Then Nom = ActivePrinter With Sheets("Tableau") .Visible = True .PrintOut from:=1, To:=p, ActivePrinter:=Nom '.Visible = False 'To be restored in the final version. End With End If

    Note: for your printer, make sure that changing the settings is applied directly without going through the VBA procedure.

    --
    Best regards.
    Le Pingou
    0
  20. Mistral_13200 Posted messages 649 Status Member 4
     
    Hello L e Pingou,

    I'm following up because it's still not working. I've reinstalled my printer and it works normally. If I do print tests manually via File/Print, I print well in B&W or color according to my choices. The same goes for print quality Normal/Draft.

    However, when I do tests using the macro, I have two problems (at that moment, the printer is set to color/Normal):
    1st test: I start the print with the button and confirm the choice by clicking OK without changing anything. The print is in B&W even though the printer was in color and remained so! Why?
    2nd test: I start the print and set the printing to B&W, then I confirm by clicking OK. The print is done in B&W (the printer is OK, verified by File/Print) but the second problem appears, namely two dotted lines that appear on the right edge of the cells in columns G and N. Why?
    3rd test: I start the print and set the printing to Color, then I confirm by clicking OK. The print is done in B&W even though the printer is set to color (the printer is OK, verified by File/Print)!!!
    All these tests were done on the workbook whose link is below:

    http://www.cijoint.fr/cjlink.php?file=cj201103/cij3nbfyED.xls

    Can you help me because I really don't understand anything anymore.
    Thank you in advance.
    Best regards,
    Mistral
    0
  21. Le Pingou Posted messages 12283 Registration date   Status Contributor Last intervention   1 480
     
    Hello,
    Everything is working fine on my end.
    Have you checked your macro step by step?
    Also, try disabling the event: `EnableEvents'!

    --
    Best regards.
    The Penguin
    0
  • 1
  • 2