Print from a VBA module
Solved
Mistral_13200
Posted messages
649
Status
Membre
-
Oz -
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!
Regardless of this modification, the workbook and printing work very well.
Can you help me?
Thank you in advance.
Mistral
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 réponses
- 1
- 2
Suivant
Hello,
To choose a printer
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
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
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
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
Re,
Actually, I have a lot of difficulty understanding all the arguments (15 in total) of the instruction:
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
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
Hello,
Try this code that allows you to choose the printer (click OK to confirm the choice):
Or with this:
The Penguin
Try this code that allows you to choose the printer (click OK to confirm the choice):
Application.Dialogs(Excel.xlBuiltInDialog.xlDialogPrinterSetup).ShowAnd 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 SubRegards.
The Penguin
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
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
Hello,
The instruction given by Le Pingou:
Response edited to include InitialPrinter to restore default settings......
The instruction given by Le Pingou:
printer = Application.Dialogs(xlDialogPrinterSetup).Showreturns "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......
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
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
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
I have tried:
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
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
PrintOutinstruction 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
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:
Note: in the dialog box, you choose the printer and confirm by clicking OK.
--
Regards.
The Penguin
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
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
and to print:
but....
--
Michel
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
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
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
Hello Mistral_13200,
One of the solutions is here: https://forums.commentcamarche.net/forum/affich-21020404-imprimer-depuis-un-module-vba#12
I have tested it and it works.
Is it possible to know if everything is okay on your end?
Regards.
The Penguin
One of the solutions is here: https://forums.commentcamarche.net/forum/affich-21020404-imprimer-depuis-un-module-vba#12
I have tested it and it works.
Is it possible to know if everything is okay on your end?
Regards.
The Penguin
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.
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.
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.
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.
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
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
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
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
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
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
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
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
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.
If you see any anomalies, please let me know.
Clearing my head wouldn't hurt...
Best regards
Mistral
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
Hello,
The part regarding the printer is correct.
To avoid [GoTo Line1], I would write it this way:
Note: for your printer, make sure that changing the settings is applied directly without going through the VBA procedure.
--
Best regards.
Le Pingou
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
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
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
- 1
- 2
Suivant