Print in a Macro

Posted on by

If you need to print in a macro then there are some things that are helpful to know like how to simply print using the default printer, how to let the user select a printer and how to manage the printer port numbers.

Record a Macro

The first thing I do if I want to find out the VBA commands and syntax for a particular action is to record a macro.  Here is the code that I got when I did this (replacing my actual printer name with “PrinterName”):

Application.ActivePrinter = "PrinterName on Ne04:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,FALSE,,,,,,2,""PrinterName on Ne04:"",,TRUE,,FALSE)"

There are a couple of things to keep in mind when looking at code from the macro recorder:

  • The code that is generated may not be the most efficient and there may be other ways of achieving the same thing. In this case, there is an alternative to using the “ExecuteExcel4Macro” line to print.
  • There may be lots of different parameters or values specified in the code, but in a lot of cases you can omit all but the ones that you are changing from the default. Try using the macro recorder when you go into Page Setup.  This generates over 40 lines of code, even if you don’t change anything!

The key thing I want you to see is how the printer is referred to:

  • Depending on your computer setup, the PrinterName may simply be the brand and model number of the printer, e.g. if you are at home, or it may include the server name and the printer name on your network, if you are in an environment that has network printers.
  • After the printer name there is some text added on to the end, ” on Nenn”, where nn is a number between 00 and 99.  The number is sometimes referred to as the printer port number or, simply, as the printer number.  This is what tends to cause a lot of problems because the number can be different from computer to computer for the same printer! It can also change if you add or remove printers on your computer.

How to Print in a Macro Using Your Default Printer

If you want to simply print in a macro using the default printer (i.e. not changing it) then the simplest way is to use this line of code:

ActiveSheet.PrintOut

There are various options available with the PrintOut command.  To find out more about these, highlight the word PrintOut in the VBA Editor and press F1 for help.  Select the Sheets.PrintOut Method in the resulting list.

How to Get the User to Select a Printer

If you want the user, i.e. the person running the macro, to select a printer while the macro is running, then use this:

If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
 ActiveSheet.PrintOut
 End If

The Application.Dialogs(xlDialogPrinterSetup).Show code displays (shows) the PrinterSetup dialog box.  This popup lists all of the printers set up on the computer, with the active printer highlighted.  The user can change the printer or keep it the same. If they click OK then this dialog box returns a True value and the active sheet is printed.  If they click Cancel then this dialog box returns a False value and hence the active sheet is not printed.

How to Find Out the Printer Port Number

I tried another help method of finding a solution to this – the internet!

After searching through several forums and blogs on this subject, I realised that this was quite a common issue.  The site that I found most helpful  on this issue of printer port numbers was that of a Norwegian consultant who I have no affiliations with. Other sites also had a similar solution.  Here are the steps involved (in words rather than code):

  • Create a function (rather than a subroutine) which has the printer name (see above) as the input parameter or argument to the function.
  • Define two string variables and an integer count variable.
  • Set up error trapping.  The easiest one is to use: On Error Resume Next.
  • Assign the current active printer to the first string variable so that you can reset it back when you exit the function.
  • Set up a loop going from 00 to 99 using the count variable.
  • Assign the second string variable to have a value of the full printer name with a value of the printer port equal to the count variable.  Use Format(Counter,”00″) to format the counter variable with a leading zero for values less than 10.
  • Assign the value of Application.ActivePrinter equal to the second string variable. If the network port number is correct then assigning this value will work.  If it is not the correct value then it will go into error and the error trapping will kick in.  In this case it will simply ignore this statement and proceed on to the next statement.
  • Now check if this assignment worked by comparing the value of Application.ActivePrinter with the second string variable in an If … Then statement.  If the value is correct then set the value of the function equal to this second string variable and set the counter to 100 to exit the loop.
  • Reset the active printer to its original value using the first string variable.

Help Methods

Here are some help methods that I commonly use when writing macros, which are demonstrated in this post:

  • Recording a macro in Excel to find out how things are coded.
  • Highlighting a command in the Visual Basic Editor and pressing F1 (help).
  • Searching on the internet.

 

I hope that helps show you how to print in a macro.